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.

Nope. Use:

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

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

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

you can also use

$query->getSingleResult();

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