Need Doctrine Many to Many QueryBuilder Query to Return NOT In Common Rows

Question

I have two tables 1) Prices and 2) Users

Owner is a column in the Prices table.

Both tables have a many to many relationship between price.users and users.price.

The query below returns all Prices shared by owner1 and user1.

Question: How do I fix this query where it only returns all owner1 prices that are not synced with user1.

If I use ->andWhere('u.id = :user1Id') then I get only records for user1.

If I use ->andWhere('u.id != :user1Id') then I get all owner record including user1 records.

Again I want all owner records except those that are synced with user1. I've tried the following so far:

1) $queryUsersPrices
                        ->innerJoin('p.owner', 'o')
                        ->leftJoin('p.users', 'u')
                        ->andWhere('o.id = :ownerId')
                        /*I need to Remove records for u.id from results*/
                        ->andWhere('u.id = :user1Id')
                        ->setParameter('owner1Id', $owner->getId())
                        ->setParameter('user1Id', $user->getId());

                    $userPrices = $queryUsersPrices->getQuery()->getResult();

2) $userPrices =   $repository->createQueryBuilder($alias);
                $userPrices
                    ->select("u.prices")
                    ->from("Price","p")
                    ->innerJoin('p.users', 'u')
                    ->andWhere('u.id = :userId')
                    ->getDQL();

   $query = $repository->createQueryBuilder($alias);
                $query
                    ->innerJoin($alias . '.owner', 'o')
                    ->innerJoin($alias . '.priceType', 'pt')
                    ->innerJoin($alias . '.model', 'm')
                    ->where(
                        $query->expr()->not(
                            $query->expr()->in(
                                'p.id',
                                $userPrices
                            )
                        )
                    )
                    ->andWhere('m.status = :m_status')
                    ->andWhere('o.id = :adminId')
                    ->andWhere('pt.site <> 1')
                    ->setParameter('m_status',  Model::STATUS_ACTIVE);

                $result = $query->getQuery()->getResult();

3) $query = $repository->createQueryBuilder($alias);
                $query
                    ->innerJoin($alias . '.owner', 'o')
                    ->innerJoin($alias . '.users', 'u', 'WITH', 'u.id = 
                     :userId')
                    ->innerJoin($alias . '.priceType', 'pt')
                    ->innerJoin($alias . '.model', 'm')
                    ->where('m.status = :m_status')
                    ->andWhere('o.id = :adminId')
                    ->andWhere('u.id IS NULL')
                    ->andWhere('pt.site <> 1')
                    ->setParameter('adminId', $adminUser->getId())
                    ->setParameter('userId',  $user->getId())
                    ->setParameter('m_status',  Model::STATUS_ACTIVE);

                $test = $query->getQuery()->getResult();

Method #1 results in user1 prices only

Method #2 results in this error: Error: Method Doctrine\Common\Collections\ArrayCollection::__toString() must not throw an exception, caught Symfony\Component\Debug\Exception\ContextErrorException: Catchable Fatal Error: Object of class Doctrine\ORM\EntityManager could not be converted to string

Methos #3 results in owner prices only

THIS IS WHAT ACTUALLY WORKED BASED ON M Khalid Junaid ANSWER

$userPrices =   $repository->createQueryBuilder('pr')
                    ->innerJoin('pr.users', 'u')
                    ->andWhere('u.id = :userId')
                    ->setParameter('userId',  $user->getId())
                    ->getDQL();

                $query = $repository->createQueryBuilder($alias);
                $query
                    ->innerJoin($alias . '.owner', 'o')
                    ->innerJoin($alias . '.priceType', 'pt')
                    ->innerJoin($alias . '.model', 'm')
                    ->where(
                        $query->expr()->not(
                            $query->expr()->in(
                                $alias . '.id',
                                $userPrices
                            )
                        )
                    )
                    ->andWhere('m.status = :m_status')
                    ->andWhere('o.id = :adminId')
                    ->andWhere('pt.site <> 1')
                    ->setParameter('m_status',  Model::STATUS_ACTIVE)
                    ->setParameter('adminId', $adminUser->getId())
                    ->setParameter('userId',  $user->getId());

                $result = $query->getQuery()->getResult();

*Methos #3 results in owner prices only* if this is not your expected output then please specify what else you need in the result set — M Khalid Junaid, Feb 10 '21 at 19:24

M Khalid Junaid · Accepted Answer · 2021-02-10T20:12:27.923

1

I would suggest to break down your logic as

First select prices that belongs to $user->getId() as

$userPrices =   $this->createQueryBuilder("u")
                     ->select("u.prices")
                     ->from("YourBundleName:Prices","p")
                     ->innerJoin('p.users', 'u')
                     ->andWhere('u.id = :user1Id')
                     ->getDQL();

Then get prices for owner which is $owner->getId() and exclude prices from the subquery for $user->getId() as

$qb = $this->createQueryBuilder("pr");
 $qb->select("pr")
    ->from("YourBundleName:Price", "pr")
    ->innerJoin('pr.owner', 'o')
    ->where(
        $qb->expr()->not(
            $qb->expr()->in(
            "pr.id",
            $userPrices
            )
        )
    )
    ->andWhere('o.id = :ownerId')
    ->setParameter('owner1Id', $owner->getId())
    ->setParameter('user1Id', $user->getId())
;
$query = $qb->getQuery();
$result = $query->getResult();

This would be more like to your original query but not the exact one I guess and might need some tweaks as per your mappings, but will give you an idea to move forward with this

References

edited Feb 10 '21 at 20:12

answered Feb 10 '21 at 15:32

M Khalid Junaid

63,861
10
90
118

Thanks for the answers. I'm getting this error when I tried using a similar method: Error: Method Doctrine\Common\Collections\ArrayCollection::__toString() must not throw an exception, caught Symfony\Component\Debug\Exception\ContextErrorException: Catchable Fatal Error: Object of class Doctrine\ORM\EntityManager could not be converted to string – Andre Van Veen Feb 10 '21 at 18:57
@AndreVanVeen can you update your question and show the details how you are trying to run this code this error look like that you are not calling it properly like mentioned here https://stackoverflow.com/questions/33100279/symfony2-error-object-of-class-doctrine-orm-entitymanager-could-not-be-converte – M Khalid Junaid Feb 10 '21 at 19:10
Please check now – Andre Van Veen Feb 10 '21 at 19:15
@AndreVanVeen Can you change `->from("Prices","p")` to `->from("YourBundleName:Prices","p")` also other than this you haven't provided values some placeholders like `adminId` and `userId` via `setParameter()` – M Khalid Junaid Feb 10 '21 at 19:21
1

Done! You may want to update the alias in the $userPrices query to avoid the 'p' is already defined error. Thanks a milli! – Andre Van Veen Feb 10 '21 at 20:10
@AndreVanVeen Yes you are right that will also cause an issue thanks for the correction – M Khalid Junaid Feb 10 '21 at 20:13

score 1 · Answer 2 · answered Feb 10 '21 at 15:59

I guess that would be very handy to transform your logic directly in DQL by doing a left join with price.users and with additional filter clause in joining part so that it will join only rows for price where user id is $user->getId() and to exclude these prices which belongs to $user->getId() we can use a where clause as u.id IS NULL

DQL

SELECT p
FROM Price p
JOIN p.owners o 
LEFT JOIN p.users u WITH u.id = :user1Id
WHERE u.id IS NULL
AND o.id = :ownerId

Query builder will be like

$qb =  $this->createQueryBuilder("p")
            ->select("p")
            ->from("Price", "p")
            ->innerJoin('p.owner', 'o')
            ->leftJoin(
                'p.users',
                'u',
                'WITH',
                'u.id = :user1Id'
            )
            ->where('u.id IS NULL')
            ->andWhere('o.id = :ownerId')
            ->setParameter('owner1Id', $owner->getId())
            ->setParameter('user1Id', $user->getId())
        ;
$query = $qb->getQuery();
$result = $query->getResult();

Need Doctrine Many to Many QueryBuilder Query to Return NOT In Common Rows

2 Answers2

References