CODEWITHSUNDEEP
sqlsymfonydoctrinedql
eronn
eronn

Reputation: 1830

I can't write well my doctrine queries in DQL

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();

Upvotes: 0

Views: 86

Answers (2)

JessGabriel
JessGabriel

Reputation: 1072

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.

Upvotes: 1

Jordan
Jordan

Reputation: 166

For the first one you need to join on u.packages It would be something like this

<?php

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

    }

For the second one i've never used the notIn but i found something on stack overflow that might help you.

How can I fetch every row where column is not in (array of values) with Doctrine?

Array not in array for doctrine query

Upvotes: 0

Related Questions