Gorm retrieve all unique by field data prioritized by other fields

let's say we have the following values in the database:

 id label  value   client  place
  1  test  value1  a       home
  2  test  value2  a
  3  test  value3
  4  test1 value4  b
  5  test1 value5
  6  test2 value6

I am trying to make a GET request, that will use the parameters(client & place) and will retrieve basically all the rows(a single time based on the label) with the following priority rule:

if exists in the db the value that has also a client and place, take that
if not, take the one if it has the client set,
finally take the general one

So for the example above it should return:

  id label  value  client  place
  1  test  value1  a       home
  4  test1 value4  b
  6  test2 value6

I'm wondering if I can achieve this by using gorm syntax, I am trying to integrate with this:

  SELECT * FROM `mytable`ORDER BY label, client DESC, place DESC

Solution

It's tricky. I definitely do not have the fully correct answer for you as I'm using Oracle.

Oracle supports a different syntax for the JOIN (WHERE o."label" = i."label"), so I expect my solution will not work using MySQL.

This is the best I could come up with, it's working for me in Oracle. Hopefully it helps to point you in the right direction:

SELECT m.* 
FROM MY_TABLE m
WHERE m."id" IN (
    SELECT o."id"
    FROM (SELECT DISTINCT i."id", i."label", i."client", i."place"
        FROM MY_TABLE i
        ORDER BY i."label", i."client" DESC NULLS LAST, i."place" DESC NULLS LAST) o
    WHERE m."label" = o."label" AND ROWNUM = 1
);

The logic behind the approach is to

order the table (i)
select the first "id" for each distinct label (o)
select all data by those ids (m)