3

I have an entity Book and it has too many relations with other entities. now I want to sort a COUNT column of one of many to many relation properties and I use this approach

but it gave me a wrong response:

  • It shows me only one row from any of my join entity because it group by a book id
  • Count number is wrong because it count all join rows so number is very large usually

this happen because I have too many left join in my query builder like this:

$this->createQueryBuilder('b')
        ->addSelect('bookTranslations')
        ->addSelect('bookGenres')
        ->addSelect('bookCrews')
        ->addSelect('feels')
        ->addSelect('genre')
        ->addSelect('user')
        ->leftJoin('b.translations', 'bookTranslations')
        ->leftJoin('b.bookGenres', 'bookGenres')
        ->leftJoin('b.bookCrews', 'bookCrews')
        ->leftJoin('bookCrews.user', 'user')
        ->leftJoin('bookGenres.genre', 'genre')
        ->leftJoin('b.feels', 'feels')
        ->addSelect('COUNT(bookGenres) AS HIDDEN genreCount')
        ->groupBy('b.id')
    ;

I use symfony serializer for responce data

any ideas?

Milad Ghofrani
  • 106
  • 4

1 Answers1

0

It is supposed to show you one row by book because of the ->groupBy('b.id'), how many rows do you expect by books ?

As for the sorting, you're missing the orderBy :

  ->orderBy('genreCount', 'DESC')
Motchouk
  • 9
  • 5
  • I want all books with all its relation, for example, if the book has three genres, I want to show all three genres but it shows me only one genre and about orderBy, I forgot to add here, I have it in my code – Milad Ghofrani Apr 07 '21 at 12:10
  • If you want one a single row per book but with all it's many-to-many relationship, you might need a subquery per many-to-many. Take a look other there : https://stackoverflow.com/questions/194852/how-to-concatenate-text-from-multiple-rows-into-a-single-text-string-in-sql-serv/42778050#42778050 – Motchouk Apr 08 '21 at 07:23