mysql query object not categorized for user

Question

i have a User with attributes User.id

i have an object with Object.id, Object.name

i have a category with Category.id, Category.name, Category.user_id

i have an association table with category_id, object_id

user HABTM objects

objects HABTM users

categories belong to 1 user

how can i write a query to return a list of all objects User has that User has not assigned a category to? Keep in mind that another User may have assigned that same object to a category of their own.

Answer 1

SELECT o.*
FROM Objects o
JOIN (
    UsersObjects uo JOIN Users u ON (uo.user_id = u.id)
  ) ON (o.id = uo.object_id)
LEFT OUTER JOIN (
    ObjectsCategories oc JOIN Categories c ON (oc.category_id = c.id)
  ) ON (o.id = oc.object_id AND c.user_id = u.id)
WHERE u.id = :userid AND oc.object_id IS NULL;

NB: I have not tested this, and it's up to you to adapt this syntax to whatever ORM you might be using.