Use Limit and Offset in Doctrine2 query

Question

I'm trying to do the pagination, but there is an error:

[Syntax Error] line 0, col 57: Error: Expected end of string, got 'limit'

I'm not quite sure if this is the right syntax (and logic) to make my query:

public function getFriendsFromTo ($user, $limit, $offset)
{
     return $this->getEntityManager()
        ->createQuery('SELECT f FROM EMMyFriendsBundle:Friend f WHERE f.user='.$user.' limit '.$limit. 'offset' .$offset)
        ->getResult();
}

Friends and users are related manyToOne and oneToMany, so in the friends table there is a field - user_id.

This is in my controller:

$user = $this->get('security.context')->getToken()->getUser();
$id = $user->getId();

$friends = $user->getFriends();
$result = count($friends)
$FR_PER_PAGE = 7;
$pages = $result/$FR_PER_PAGE;

$em = $this->getDoctrine()->getEntityManager();
$friends = $em->getRepository('EMMyFriendsBundle:Friend')
         ->getFriendsFromTo($id, $FR_PER_PAGE, $page*$FR_PER_PAGE);

I know that it's stupid and even wrong (especially the third parameter to be $page*$FR_PER_PAGE), but I just wanted to try if the query works, and it didn't.

What did you mean it didn't work? get any errors? You should also be using `setParameter("user", $user)` instead of inserting it directly into the DQL. Also, it would be nice to see your entity definitions. EDIT: just seen the edit. There should be a space appended to 'offset' — Flukey, Aug 30 '12 at 13:36
Yes, I get an error - it's in the beggining of the question. — Faery, Aug 30 '12 at 13:37
This bundle may be of interest to you: http://knpbundles.com/KnpLabs/KnpPaginatorBundle — Flukey, Aug 30 '12 at 13:38
Thank you :) but I prefer to do it without a bundle in order to understand it better. — Faery, Aug 30 '12 at 13:42

Thomas K · Accepted Answer · 2012-08-30T13:54:36.783

160

Nope. Use:

  return $this->getEntityManager()
        ->createQuery('...')
        ->setMaxResults(5)
        ->setFirstResult(10)
        ->getResult();

edited Aug 30 '12 at 13:54

answered Aug 30 '12 at 13:38

Thomas K

6,076
5
39
56

But how to set from where the results should start? – Faery Aug 30 '12 at 13:43
Be careful if you use JOIN in your DQL. It will not perform as expected: http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/dql-doctrine-query-language.html#first-and-max-result-items-dql-query-only – Alejandro Fiore Sep 16 '15 at 21:15
1

is there a simple way, how to know if there are more enities left in the DB? i mean, if the result of this query contains the last entity in the database. – dannymo Feb 13 '16 at 15:37

score 35 · Answer 2 · edited Nov 02 '13 at 19:37

35

$towary = $this->getDoctrine()
   ->getRepository('AcmeStoreBundle:Towar') 
   ->findBy(array(),array(),10,($current-1)*$numItemsPerPage);

edited Nov 02 '13 at 19:37

Stewie Griffin

14,889
11
39
70

answered Nov 02 '13 at 19:19

vundek

351
3
2

I found this more useful since I wanted to use the findBy() function – Christian May 07 '17 at 19:12
and what is `$current` please? – Josiah Jul 03 '18 at 11:54
@Josiah current page – ddruganov Mar 09 '23 at 06:39

score 25 · Answer 3 · edited Feb 21 '19 at 11:07

25

You can use findBy 3rd and 4th parameters of method of doctrine repository, which are limit and offset.

Here is the method definition:

findBy(
    array        $criteria,
    array        $orderBy  = null, 
    integer|null $limit    = null,
    integer|null $offset   = null
)

Source: http://www.doctrine-project.org/api/orm/2.2/class-Doctrine.ORM.EntityRepository.html

edited Feb 21 '19 at 11:07

Boulboulouboule

4,087
1
13
29

answered Dec 22 '15 at 17:12

cryptonico

278
3
6

score 3 · Answer 4 · answered Oct 17 '20 at 21:24

Doctrine2.6, stumbled upon this old post and tried the DQL way but it did not fit for purpose. So if you want to avoid using DQL because you already have Entities mapped and joined together, you can do paging using matching & Criteria

$criteria = Criteria::create()
            ->setMaxResults($limit ? $limit : null)
            ->setFirstResult($offset ? $offset : null)
$result = $em->getRepository('EMMyFriendsBundle:Friend')
            ->matching($criteria)->toArray();

score 1 · Answer 5 · answered Nov 12 '14 at 10:14

1

you can also use

$query->getSingleResult();

answered Nov 12 '14 at 10:14

matzeihnsein

678
7
16

Use Limit and Offset in Doctrine2 query

5 Answers5