Convert SQL with queryBuilder Error: Invalid PathExpression. Must be a StateFieldPathExpression

Viewed 26

Hello I am new in Symfony 5, I try to convert SQL query with queryBuilder. I try to filter blacklisted profils by exclude them with profil entity found in my subquery.

My SQL example, works fine with user_id 66 :

SELECT p.first_name, p.id, i.interest_sender_id, i.accepted
FROM interest as i
INNER JOIN profil p on p.user_id = i.interest_sender_id
WHERE i.interest_receiver_id = 66  AND  p.id NOT IN(
 SELECT  b.blocked_profil_id
 FROM blacklist b 
 WHERE b.profil_id = p.id
)

I try to convert into createQueryBuilder(), but I have an error :

public function findInterestSended($sender_id) {

        $main = $this->_em->createQueryBuilder();

        $blacklisted = $this->_em->createQueryBuilder()
            ->select('b.blocked_profil')
            ->from('App\Entity\Blacklist', 'b',)
            ->where('b.profil = p');

        $filtered = $main->select('p, i.accepted')
            ->from('App\Entity\Interest', 'i',)
            ->join( 'i.interestReceiver', 'r')
            ->join( 'App\Entity\Profil', 'p', Join::WITH,   'p.user = r')
            ->where('i.interestSender = :userId')
            ->andWhere(
                $main->expr()->notIn('p', $blacklisted->getDQL()),
            )
            ->orderBy('i.last_send', 'DESC')
            ->setParameter('userId', $sender_id)
            ->getQuery()
            ->getResult();

        return $filtered;
    }

DQL result :

SELECT p, i.accepted FROM App\Entity\Interest i 
INNER JOIN i.interestReceiver r 
INNER JOIN App\Entity\Profil p WITH p.user = r 
WHERE i.interestSender = :userId AND p NOT IN(
SELECT b.blocked_profil FROM App\Entity\Blacklist b WHERE b.profil = p
) 
ORDER BY i.last_send DESC

My Blacklist class :

/**
 * @ORM\Entity(repositoryClass=BlacklistRepository::class)
 */
class Blacklist
{
    /**
     * @ORM\Id
     * @ORM\GeneratedValue
     * @ORM\Column(type="integer")
     */
    private $id;

    /**
     * @ORM\ManyToOne(targetEntity=Profil::class, inversedBy="blacklist")
     * @ORM\JoinColumn(nullable=false)
     */
    private ?Profil $profil;

    /**
     * @ORM\Column(type="datetime_immutable")
     */
    private $createdAt;

    /**
     * @ORM\ManyToOne(targetEntity=Profil::class, inversedBy="blockedProfil")
     * @ORM\JoinColumn(nullable=false)
     */
    private ?Profil $blocked_profil;

...}

My Profil class :

/**
 * @ORM\Entity(repositoryClass=ProfilRepository::class)
 * @ORM\HasLifecycleCallbacks()
 * @Vich\Uploadable
 */
class Profil
{
    /**
     * @ORM\Id
     * @ORM\GeneratedValue
     * @ORM\Column(type="integer")
     */
    private $id;

    /**
     * @ORM\OneToOne(targetEntity=User::class, inversedBy="profil", cascade={"persist", "remove"})
     * @ORM\JoinColumn(nullable=false)
     */
    private $user;

    /**
     * User who block other
     * @ORM\OneToMany(targetEntity=Blacklist::class, mappedBy="profil")
     */
    private Collection $blacklist;

    /**
     * user profil blocked
     * @ORM\OneToMany(targetEntity=Blacklist::class, mappedBy="blocked_profil")
     */
    private Collection $blockedProfil;

    ...
    }

I don't uderstand what is missing

0 Answers
Related