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

JessGabriel · Accepted Answer · 2019-04-11T07:29:55.550

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.

edited Apr 11 '19 at 07:29

answered Apr 11 '19 at 07:24

JessGabriel

1,062
9
18

Indeed, I thought that I could do without the first function. I planned to use a $ user-> getPackages () thinking that it would return an array of packages except that it is not. For the second query I'll look a little more – eronn Apr 11 '19 at 07:37
1

getPackages() return PersistentCollection who extends AbstractLazyCollection who implements Collection that you can convert to array with the method toArray() – JessGabriel Apr 11 '19 at 07:41
Okay perfect I get the array of packages already owned by the user (I modified a little your request because I need the table for other features). I have one last mistake though. I'm editing my first post to show you – eronn Apr 11 '19 at 08:41
can you show the output of this larray_column($packagesUser, 'id')? – JessGabriel Apr 11 '19 at 10:35
A little upvote can help another dev who will face the same problem :-) – JessGabriel Apr 11 '19 at 11:21

score 0 · Answer 2 · answered Apr 11 '19 at 07:27

0

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

answered Apr 11 '19 at 07:27

Jordan

156
6

Thank you for your answer ! So this query returns the complete user whose id passed as a parameter. I do not know if I misspoke. I already have the user in question. What I want at first is to recover its arrayCollection packages. But the $ user-> getPackages () does not return me from arrayCollection. He's sending me something like this: – eronn Apr 11 '19 at 07:35
PersistentCollection {#338 ▼ -snapshot: [] -owner: User {#390 ▶} -association: array:20 [ …20] -em: EntityManager {#318 …11} -backRefFieldName: null -typeClass: ClassMetadata {#376 …} -isDirty: false #collection: ArrayCollection {#555 ▶} #initialized: false } – eronn Apr 11 '19 at 07:35
You get this result because of the lazy load, if you loop on the $user->getPackages() you will get it, but with a lot of data it's not really a good thing (N+1 problem) from what i know you can either enable the EAGER fetch in your entity https://stackoverflow.com/questions/26891658/what-is-the-difference-between-fetch-eager-and-fetch-lazy-in-doctrine or make a custom query that you will use when you need to access the packages – Jordan Apr 11 '19 at 07:46
I used the toArray() method to have my packages user array :) Now, I have to find those who do not belong to this list – eronn Apr 11 '19 at 08:44

I can't write well my doctrine queries in DQL

2 Answers2