I can't write well my doctrine queries in DQL

Question

being a great Symfony beginner and so Doctrine, I have a problem with two queries that I need to do.

I have a User table that contains a ManyToMany relationship with a Packages entity. (Creation of the "packagesDDLUser" table)

In my User entity I have a "packages" field.

My users download packages. When this happens, the downloaded package is inserted into the database in the "packagesDDLUser" entity such as "user_id, package_id".

What I want to do is be able to display on a page the list of packages that are not part of the packages to which the user has already been linked.

Basically, when a user downloads a package, it is no longer useful to display it in the list of those that can be downloaded.

I have two requests. One that will collect packages downloaded by the user with the user's id parameter. And another that should select the list of packages that do not belong to this list.

So, I've :

public function getPackagesUser($id)
    {
        $queryBuilder = $this->createQueryBuilder("u")
        ->select("u.packages")
        ->where("u.id = :id")
        ->setParameter("id", $id);
        return $queryBuilder->getQuery()->getResult();

    }

But I've this error :

[Semantical Error] line 0, col 9 near 'packages FROM': Error: Invalid PathExpression. Must be a StateFieldPathExpression.

And my second query:

public function getPackagesNotUser($packagesUser)
    {
        $qb = $this->createQueryBuilder('p');
        $queryBuilder= $qb->where($qb->expr()->notIn('p.id', array_column($packagesUser, 'id')));

        return $queryBuilder->getQuery()->getResult();
    }

Could someone please help me? I do not understand much about Doctrine, it's pretty new to me

Thanks

EDIT:

Ok now I've: controller.php

 $user = $this->getUser();
 $packagesUser = $user->getPackages()->toArray();
$packagesView = $this->getDoctrine()->getRepository('PagesBundle:Paquet')->getPackagesNotUser($packagesUser);

repository:

public function getPackagesNotUser($packagesUser)
    {
        $qb = $this->createQueryBuilder('p');
        $queryBuilder= $qb->where($qb->expr()->notIn('p.id', array_column($packagesUser, 'id')));

        return $queryBuilder->getQuery()->getResult();
    }

But I've this error

[Syntax Error] line 0, col 65: Error: Expected Literal, got ')'

On this :

return $queryBuilder->getQuery()->getResult();

Answer 1

First, you cannot directely select packages like this [in your first function]. You have to join. For example, like :

public function getPackagesUser($id)
{
   $queryBuilder = $this->createQueryBuilder("u")
        ->join("u.packages", "packages")
        ->where("u.id = :id")
        ->setParameter("id", $id);

   return $queryBuilder->getQuery()->getResult();

}

Second, your second function sounds like you have to pass an user object as parameters. Something like:

public function getPackagesNotUser(User $user)
{
    $packagesUser = $user->getPackages()->toArray();
    $qb = $this->createQueryBuilder('p');
    $queryBuilder= $qb->where($qb->expr()->notIn('p.id', array_column($packagesUser, 'id')));

    return $queryBuilder->getQuery()->getResult();
}

So, you don't need the first function.