0

How can I leave one record (last/newest) for each resource_owner_id and scopes combination and remove other records?

For example, for resource_owner_id=3 and scopes=driver it should be only record with id=1357.resource_owner_id=5 and scopes=driver - with id=1034

I know that I can use .ids, after that get all records by id and scope (scopes are enum - 'driver' and 'passenger'), after that use .pop and remove all remaining records.

Maybe there is a more elegant solution?

enter image description here

ViT-Vetal-
  • 2,431
  • 3
  • 19
  • 35

1 Answers1

0

SQL Fiddle

ActiveRecord version:

Model.select("DISTINCT ON(resource_owner_id, scopes) *")
     .order("resource_owner_id, scopes, id")

Read more about DISTINCT ON here.

Marcin Kołodziej
  • 5,253
  • 1
  • 10
  • 17
  • Thanks! But I need leave in table this records and remove all other records. Should I just add this line`Model.where.not(id:@models.map(&:id)).destroy_all`? – ViT-Vetal- Nov 28 '18 at 10:06
  • Ah, I thought that by remove you mean remove them from the returned records. Yes, that would work, depending on what callbacks you have, you might consider `delete_all` as it would be much faster. – Marcin Kołodziej Nov 28 '18 at 10:18