[FIXED] Convert sql to dql symfony

Issue

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'

Solution

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

Answered By - thurston