CODEWITHSUNDEEP
phpsymfonycountdoctrine
eronn
eronn

Reputation: 1840

Symfony 3 - Doctrine - Difficulties on big query

I'm annoyed because of a big query that goes a little in all directions.

She will use several tables:

The purpose of this query is to be able to return a list that will contain:

The different existing packages with the number of times they were downloaded, for a certain period, with a given order (ASC or DESC) on the number of downloads, and all this depending on the user types whose ID table has passed as parameter.

So, I've :

Paquet :

enter image description here

Type Utilisateur :

enter image description here

User:

enter image description here

The manyToMany relation between TypeUtilisateur and Paquet:

enter image description here

And the PackageDDLExternal:

enter image description here

So I have already tried to create this query, but there seems to be synthax errors on the one hand (at the level of adding the order) and other things that seem to block...

public function getPackagesDDLBetween($debut, $fin,$typesUser,$ordre)
{
    $qb = $this->createQueryBuilder('p');

    $queryBuilder = $qb
    ->select("pa.titre, count(p.package)")
    ->join("p.package","pa")
    ->join("p.user","u")
    ->where("p.date between :debut and :fin")
    ->andWhere($qb->expr()->in("u.typeUser", $typesUser[0]))
    ->groupBy("pa.titre")
    ->orderBy("count(p.package) :ordre")
    ->setParameter('debut',$debut)
    ->setParameter('fin',$fin)
    ->setParameter('ordre', $ordre);
    return $queryBuilder->getQuery()->getResult();
}

Using the $order, I get this error:

[Syntax Error] line 0, col 213: Error: Expected known function, got 'count'

But without it, my result is just null

Someone can help me please ?

EDIT: The query is almost good. The problem remains at:

->andWhere($qb->expr()->in("u.typeUser", $typesUser))

My $ tabUser is worth this:

enter image description here

Upvotes: 1

Views: 341

Answers (1)

Andrei Lupuleasa
Andrei Lupuleasa

Reputation: 2762

Use an alias:

->select("pa.titre, count(p.package) as total")

Order by alias:

->orderBy("total", $ordre)

Upvotes: 3

Related Questions