Active Record (MYSQL) - Select distinct ids from multiple columns

Question

Ver 14.14 Distrib 5.1.73

activerecord (4.1.14)

I have a trade model that belongs to a lender and borrower. I want to find all uniq counterparties to an institution's trades in one SQL query. The query below works, but only because I flatten & unique-ify the array after the SQL query:

Trade.where("borrower_id = :id OR lender_id = :id", id: institution.id).uniq.pluck(:lender_id, :borrower_id).flatten.uniq

(I know this includes the institution itself, so we normalize after with [1,2,3,4] - [1])

But what I'd like to do is use a Group By clause or something so that my SQL query handles the flatten.uniq part.

The below does not work because it returns a nested array of unique combinations of lender_id and borrower_id:

Trade.where("borrower_id = :id OR lender_id = :id", id: institution.id).group(:lender_id, :borrower_id).uniq.pluck(:lender_id, :borrower_id)

=> [[1,2], [1,3], [2,3]]

I want a flat array of JUST unique ids: [1,2,3]

Any ideas? Thanks!

Strawberry · Accepted Answer · 2016-11-03 00:10:08Z

0

I don't understand what you're trying to, or why you'd want to include a GROUP BY clause in the absence of any aggregating functions.

FWIW, a valid query might look like this...

SELECT DISTINCT t.lender_id
              , t.borrower_id
           FROM trades t
          WHERE 28 IN(t.borrower_id,t.lender_id);

answered Nov 3, 2016 at 0:10

Strawberry

34k14 gold badges43 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

daino3 Over a year ago

thanks for replying. I was using a GROUP BY because of similar SO posts (stackoverflow.com/questions/12188027/…).... I'm open to whatever works! That being said, your query results in a nested array return set that includes unique combinations of lender_id and borrower_id (e.g. [ [1, 2], [1, 3] ])... I need just the unique ids as a flat array (e.g [ 1,2,3 ]).

Strawberry Over a year ago

i would do that kind of processing in the app.

daino3 Over a year ago

LOL, that's what I'm doing (flatten.uniq), but I want to learn how I can do this via SQL / ActiveRecord (hence the question). Seems like a pretty relevant / frequent use case.

Strawberry Over a year ago

Well in that case, lose the DISTINCT and instead include a GROUP_CONCAT(DISTINCT...) function (and that GROUP BY clause again) - but I think your existing method (or something like it) is better.

Collectives™ on Stack Overflow

Active Record (MYSQL) - Select distinct ids from multiple columns

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related