mardi 14 juin 2016

Order by different attributes in different tables in MySQL

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