Wrong query for selecting IDs for pagination

[StanislavUngr]

There is a bug in query selecting IDs that are passed for query selecting paged items.

1st query = selecting count;
2nd query = selecting IDs that are selected in 3rd query ( using LIMIT ... OFFSET... )
3rd query = selecting items that are displayed

How to get to bug:

Lets say I have this query:

Entity\Gallery = media__gallery
Entity\Media = media__media
Entity\GalleryHasMedia = media__gallery_media

So basically what Im doing is selecting 'm' (Media) for displaying in paginated results.

The 2nd query thus should select not u0_id but m1_.id so it can be used in 3rd query in WHERE statement.

2nd query

What it does now is instead of filling WHERE with (m1.)_Media IDs its using (u0.)_UserGallery IDs...

3rd query

[nicolasmure]

Hello,
Can you post your complete code please (ie the multiple queries you're performing) ?
Also, please avoid to post screenshots, they're not easy to read. Please use markdown syntax highlighting instead.

[StanislavUngr]

Hello. This is the DQL query:

SELECT m 
FROM AppBundle:User\UserGallery ug 
INNER JOIN Application\Sonata\MediaBundle\Entity\Gallery g 
	WITH g.id = ug.gallery 
INNER JOIN Application\Sonata\MediaBundle\Entity\GalleryHasMedia hm 
	WITH hm.gallery = g.id 
INNER JOIN Application\Sonata\MediaBundle\Entity\Media m 
	WITH m.id = hm.media 
WHERE ug.user = :user AND ug.gallery = :gallery 
ORDER BY m.sortablePosition DESC

$pagination = $paginator->paginate($qb, $request->query->getInt('p', 1), 5);

Paginator then generates query for selecting IDs (results to be displayed). Problem is it should select m1_.id instead of u0_.id

SELECT 
  DISTINCT u0_.id AS id_0, 
  m1_.sortable_position AS sortable_position_1 
FROM 
  user__galleries u0_ 
  INNER JOIN media__gallery m2_ ON (m2_.id = u0_.gallery_id) 
  AND (m2_.deleted_at IS NULL) 
  INNER JOIN media__gallery_media m3_ ON (m3_.gallery_id = m2_.id) 
  AND (m3_.deleted_at IS NULL) 
  INNER JOIN media__media m1_ ON (m1_.id = m3_.media_id) 
  AND (m1_.deleted_at IS NULL) 
WHERE 
  (
    u0_.user_id = ? 
    AND u0_.gallery_id = ?
  ) 
  AND (u0_.deleted_at IS NULL) 
ORDER BY 
  m1_.sortable_position DESC 
LIMIT 
  5 OFFSET 0

So in short - paginator selects ug.id instead of m.id. As the ug.id is everywhere the same (which means same gallery ID) it basically displays all images of a gallery instead of only 5

[nicolasmure]

Okay, it looks like the problem is more related to the query rather than to the paginator itself. Can you also explain the use case please ? What are you attempting to do ?