CODEWITHSUNDEEP
symfonypaginationdoctrine-orm
Faery
Faery

Reputation: 4650

Use Limit and Offset in Doctrine2 query

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.

Upvotes: 70

Views: 121575

Answers (5)

satanio
satanio

Reputation: 31

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

Upvotes: 3

cryptonico
cryptonico

Reputation: 278

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

Upvotes: 25

matzeihnsein
matzeihnsein

Reputation: 676

you can also use

$query->getSingleResult();

Upvotes: 0

vundek
vundek

Reputation: 361

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

Upvotes: 36

Thomas K
Thomas K

Reputation: 6216

Nope. Use:

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

Upvotes: 165

Related Questions