0

I have this table:

User

Name Role
Mason Engineer
Jackson Engineer
Mason Supervisor
Jackson Supervisor
Graham Engineer
Graham Engineer

There can be exact duplicates (same Name/Role combination). Ignore comments about primary key.

I am writing a query that will give the distinct values from 'Name' column, with the corresponding 'Role'. To select the corresponding 'Role', if there is a 'Supervisor' role for a name, that record is returned. Otherwise, a record with the 'Engineer' role should be returned if it exists.

For the above table, the expected result is:

Name Role
Mason Supervisor
Jackson Supervisor
Graham Engineer

I tried ordering 'Role' in descending order, so that I can group by Name,Role and pick the first item - it will be a 'Supervisor' role if present, else 'Engineer' role - which matches my expecation.

I also tried doing User.select('DISTINCT ON (name) \*).order(Role: :desc) - I am not seeing this clause in the SQL query that gets executed.

Also, I tried another approach to get all valid Name, Role combinations and then process it offline iterating the result set and using if-else to decide which row to display.

However, I am interested in anything that is efficient and does not over do this handling.

I am new to Ruby and therefore reaching out.

rogerdpack
  • 62,887
  • 36
  • 269
  • 388
Mason
  • 1
  • 1
  • 1
    What are you seeing executed? https://stackoverflow.com/questions/6844752/how-do-i-select-unique-records-by-column-with-activerecord-and-postgresql?rq=1 looks interesting – rogerdpack Nov 22 '22 at 00:24
  • @rogerdpack - I am seeing every other part of the ruby query in the corresponding SQL one, and it is the DISTINCT portion that is not seen. – Mason Nov 22 '22 at 01:28
  • Do you want us to ignore the obvious problem that your app breaks completely as soon as two people with the same name are hired for a role? – max Nov 22 '22 at 12:18
  • Yes, as per the disclaimer in the question. – Mason Nov 22 '22 at 12:20
  • Hmm that's odd, rails bug? See also https://stackoverflow.com/questions/9658881/rails-select-unique-values-from-a-column – rogerdpack Nov 22 '22 at 20:30

1 Answers1

1

If I wanted to do this in pure SQL, I would have to use GROUP BY.

SELECT Name, MAX(Role) FROM User GROUP BY Name

So one method would be to execute this SQL statement against the base connection.

ActiveRecord::Base.connection.execute("SELECT Name, MAX(Role) FROM User GROUP BY Name")

That would provide exactly the data you need, though it wouldn't be returned as ActiveRecord models. If you need those models then I would use find_by_sql and do an inner join to provide the records.

User.find_by_sql("SELECT User.* FROM User INNER JOIN (SELECT Name AS n, MAX(Role) AS r FROM User GROUP BY Name) U2 WHERE Name = U2.n AND Role = U2.r")

Unfortunately that would provide both records for Graham.

Jeff Bigler
  • 11
  • 2