Convert sql to dql symfony

Question

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'

SO is not actually a code conversion service. The best way is to have a try yourself and then if you have problems, show us your attempt and what you are trying to convert. That way we feel less like we are being used as a free coding service — RiggsFolly, Nov 08 '21 at 12:16
Learn DQL step by step, try join palettes with file_palettes, then try to group by files_palettes, add `count` and `orderBy`. Stack Overflow is not fiverr, the point is to make questions that can help future users. — Dylan KAS, Nov 08 '21 at 12:19
Just checking, you actually want to count the number of files per pallettes, right? Do you actually need the `SELECT *` part ? Or just `SELECT COUNT(*)` is enough ? — Preciel, Nov 08 '21 at 13:15

score 1 · Accepted Answer · answered Nov 08 '21 at 13:40

1

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 Nov 08 '21 at 13:40

thurston

26
1

The `Join::ON` is automatically appended by the `p.files` association mapping, unless manually specifying the Entity association. `f.p` is not referenceable, it should be `p.id` as the `INNER JOIN` limits the records to those matching the `files_palettes.palettes_id` reference. Lastly when using the ORM DQL/QueryBuilder, `Join::ON` should be `Join:WITH`. – Will B. Nov 08 '21 at 14:48
Thank you very much thurston, I would like to count the number of results to display it – bjd Nov 09 '21 at 13:31

score -1 · Answer 2 · answered Nov 08 '21 at 13:11

-1

$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 !!

answered Nov 08 '21 at 13:11

bjd

9
1

Convert sql to dql symfony

2 Answers2