CODEWITHSUNDEEP
sqlsymfonydoctrine-orm
Jake N
Jake N

Reputation: 10583

Doctrine - Get items based on the count of a many to many

I have the following tables, User, Project and Images.

A User has a one to many relationship with Projects and Images. Each Project and Image is owned by a User.

A Project has a many to many relationship with Images. So each Project can have many Images, and an Image can appear within many Projects.

I want to write a DQL query to get all Images, for a specific User that are not included in any Projects. This I can write in SQL.

Is writing in SQL the best way to go?

Or should I be using DQL to do this?

I have tried writing the DQL but its hard work!

Edit

From within my Image Repo I am now doing this

$qb = $this->createQueryBuilder("i");

$qb->select("i")
    ->from('MyBundleName:User','u')
    ->innerJoin('u.images', 'user_images')
    ->where('IDENTITY(u.id) = :user_id')
    ->andWhere('NOT EXISTS (
        SELECT p
        FROM MyBundleName:Project p
        WHERE user_images MEMBER of p.images
    )')
->setParameter('user_id', $user_id);

I have replace the / syntax with : for my classes as they failed when using /

I am still getting this error though;

[Semantical Error] line 0, col 131 near 'id) = :user_id': Error: Invalid PathExpression. Must be a SingleValuedAssociationField.

The function createQueryBuilder requires an alias, I am passing it "i" - is that right?

I then give it "i" again when calling select?

If I remove the innerJoin then it works, but the results are wrong. It returns all images even if they do exist within an project.

Upvotes: 1

Views: 203

Answers (2)

FuzzyTree
FuzzyTree

Reputation: 32392

I don't know what your entities look like but given the relations something like this should work. The key is combining NOT EXISTS and MEMBER OF (you want to make sure that for all returned images no project exists that the image is a member of).

$qb->select("i")
    ->from('MyBundle\Entity\User','u')
    ->innerJoin('u.images','i')
    ->where('IDENTITY(u) = :user_id')
    ->andWhere('NOT EXISTS (
        SELECT p
        FROM MyBundle\Entity\Project p 
        WHERE i MEMBER of p.images
      )')
    ->setParameter('user_id', $user_id);

Upvotes: 1

woot
woot

Reputation: 7606

I can't say how difficult it would be in DQL, but I can tell you that in SQL it sounds pretty simple: 

SELECT I.*
FROM IMAGES I
INNER JOIN USERS U ON (I.USER_ID = U.USER_ID)
WHERE NOT EXISTS (
    SELECT *
    FROM   PROJECTS P, PROJECT_IMAGES PI
    WHERE  P.USER_ID = U.USER_ID
    AND    PI.PROJECT_ID = P.PROJECT_ID
    AND    I.IMAGE_ID = PI.IMAGE_ID
)

Images owned by a user which does not exist in any project that the user owns.

Upvotes: 1

Related Questions