0

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!

daino3
  • 4,386
  • 37
  • 48

1 Answers1

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);
Strawberry
  • 33,750
  • 13
  • 40
  • 57
  • thanks for replying. I was using a GROUP BY because of similar SO posts (http://stackoverflow.com/questions/12188027/mysql-select-distinct-multiple-columns?rq=1).... 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 ]). – daino3 Nov 03 '16 at 16:32
  • i would do that kind of processing in the app. – Strawberry Nov 03 '16 at 16:35
  • 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. – daino3 Nov 03 '16 at 16:43
  • 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. – Strawberry Nov 03 '16 at 16:47