My schema looks like this:
User has a foreign key to Admin (admin_id)
Admin is associated with League through admin_leagues table. So admin_leagues has a foreign_key to admin and a foreign key to league.
A Gadget has a foreign key to a User.
A Gadget has a foreign key to a GadgetStatus.
I would like to be able to sort Users by:
1 - The Users which Admin is associated with a League which ID=5
2 - The Users where the total amount of associated Gadgets which GadgetStatus.id = 4 sum of its quantity column is smaller than 100
3 - The rest of the users.
In order to solve this problem I have started writing what would be the query to just satisfy one (scoping it with admin, because I don't know how to do two joins), and I have come up with this:
SELECT Admin.* FROM Admin
LEFT OUTER JOIN admin_leagues ON admin.id = admin_leagues.admin_id
ORDER BY
CASE WHEN admin_leagues.league_id = 5
THEN 1
ELSE 0
END
This seems to work. But I am not sure how to associate this ordering with the second ordering condition.
Aucun commentaire:
Enregistrer un commentaire