How to get unique records based on two fields Ruby-CodePudding

I need to filter Home records based on uniq inspection_id

Home.all.select(:id,:inspection_id)

  [#<Home:0x007fc5cd065758 id: 2192, inspection_id: 21967>,
 #<Home:0x007fc5cd065618 id: 2191, inspection_id: 21968>,
 #<Home:0x007fc5cd0654d8 id: 2190, inspection_id: 21967>,
 #<Home:0x007fc5cd065398 id: 2189, inspection_id: 21968>,
 #<Home:0x007fc5cd065258 id: 2188, inspection_id: 21963>,
 #<Home:0x007fc5cd065118 id: 2187, inspection_id: 21967>]

Need the result like this

[#<Home:0x007fc5cd065758 id: 2192, inspection_id: 21967>,
     #<Home:0x007fc5cd065618 id: 2191, inspection_id: 21968>,
     #<Home:0x007fc5cd065118 id: 2188, inspection_id: 21963>]

Home with ids 2191,2191,2188 , I need in result set

I tried this , but not works.

Home.all.select(:id,:inspection_id).distinct

Gives this error

Expression #1 of ORDER BY clause is not in SELECT list, references column 'myDB.homes.created_at' which is not in SELECT list; this is incompatible with DISTINCT

Any solution please

CodePudding user response：

First of all, I don't think this is a valid SQL statement. DISTINCT will work on all the columns defined in SELECT clause and will return all records because ID will be different for all. The better option to use is GROUP.

Home.all.select(:id, :inspection_id).group(:inspection_id) This will group all records by inspection_ids and if you call to_a on above query - you'll get only valid records. You can also iterate on above records.

Other option is that if you are not expecting many rows - just use Home.all.pluck(:id, :inspection_id) and do the unique logic on the rails side - but not a good solution if you have thousands and millions of rows - which will load all the records into memory.

CodePudding user response：

You didn't specify your RDBMS, but in PostgreSQL you can use DISTINCT ON

Home.select('DISTINCT ON (inspection_id) *')

CodePudding user response：

Udit's code looks good but it needs some changes. Try the below code:

Home.all.order(: inspection_id, :id).select('DISTINCT ON (inspection_id) id, inspection_id')