Relation can not contain other values than in array

Question

I'm struggling with something and I hope you can help me out a bit.

Let's say I have two tables: class and student. One class has many students and a student has only one class.

I am allowed to manage certain students, spread out over different classes. Now I would like to retrieve all classes that I am allowed to see. That is based on, I may see a class when all students are in my visibility (which will be given through an array). So instead of IN I would need something like ALL IN, but that obviously doesn't exist.

Does any one can point me into the right direction, how to achieve this with Doctrine v1.2.4 or with a plain SQL?

Answer 1

I've been struggling with some queries and came to this solution, that will retrieve all classes where all students are within my visibility:

SELECT
    c.*
FROM
    class c
INNER JOIN student s ON c.id = s.class_id
WHERE
    s.id NOT IN (
        SELECT
            id
        FROM
            student
        WHERE
            s.id NOT IN (1, 2, 5, 6, 8) /* All visible student id's */
)
GROUP BY
    c.id