I would like to convert this request in sql to dql , need a little help.
SELECT *, COUNT(*) AS nb_files
FROM palettes
JOIN files_palettes ON palettes.id = files_palettes.palettes_id
GROUP BY files_palettes.palettes_id
ORDER BY nb_files DESC
Edit
The attempted query.
public function findSaveByPagesByFilters()
{
$entityManager = $this->getEntityManager();
$select = " SELECT COUNT (p) AS nb_files";
$from = " FROM App\Entity\Palettes p ";
$join = "JOIN p.files f ";
$on = " ON p.id = f.palettes ";
$groupBy = " GROUP BY f.p";
$orderBy = " ORDER BY f.name DESC ";
$dqlQuery = $select . $from . $join . $on . $groupBy;
//dd($dqlQuery);
$query = $entityManager->createQuery(
$dqlQuery
);
return $query->getResult();
}
Here's the message sent by insomnia :
[Syntax Error] line 0, col 74: Error: Expected end of string, got 'ON'
CodePudding user response:
I assume that you're within the context of a repository, so in which case I'd advise using the Doctrine Query Builder, it'd help simplify your code flow, and probably would help you with SQL conversions in the future.
To answer this specific problem, you'd probably want to do something like the following:
public function findSaveByPagesByFilters()
{
return $this->createQueryBuilder('p')
->innerJoin('p.files', 'f', Query\Expr\Join::ON, 'p.id = f.pallets')
->select(['*', 'count(p.id)'])
->groupBy('f.p')
->orderBy('f.name', 'DESC')
->getQuery()
->getResult();
}
CodePudding user response:
$dql = "SELECT p as palette, count(p.id) as cnt FROM App\Entity\Palettes p " .
"JOIN p.files f GROUP BY p.id ORDER BY cnt DESC";
;
Thx Alexis !!