How to sort findAll Doctrine's method?

Question

I've been reading Doctrine's documentation, but I haven't been able to find a way to sort findAll() Results.

I'm using symfony2 + doctrine, this is the statement that I'm using inside my Controller:

$this->getDoctrine()->getRepository('MyBundle:MyTable')->findAll();

but I want the results to be ordered by ascending usernames.

I've been trying to pass an array as an argument this way:

findAll( array('username' => 'ASC') );

but it doesn't work (it doesn't complain either).

Is there any way to do this without building a DQL query?

score 258 · Accepted Answer · edited Apr 04 '17 at 20:38

258

As @Lighthart as shown, yes it's possible, although it adds significant fat to the controller and isn't DRY.

You should really define your own query in the entity repository, it's simple and best practice.

use Doctrine\ORM\EntityRepository;

class UserRepository extends EntityRepository
{
    public function findAll()
    {
        return $this->findBy(array(), array('username' => 'ASC'));
    }
}

Then you must tell your entity to look for queries in the repository:

/**
 * @ORM\Table(name="User")
 * @ORM\Entity(repositoryClass="Acme\UserBundle\Entity\Repository\UserRepository")
 */
class User
{
    ...
}

Finally, in your controller:

$this->getDoctrine()->getRepository('AcmeBundle:User')->findAll();

edited Apr 04 '17 at 20:38

Lighthart

3,648
6
28
47

answered Jun 15 '13 at 04:09

Pier-Luc Gendreau

13,553
4
58
69

3

This is a better approach than mine, but you will be writing dql; my method has less dql and so answers the OP's constraint. Frankly, fear of dql should just be overcome. Use this method in preference to mine if at all possible. – Lighthart Jun 15 '13 at 08:22
1

well it's not fear of dql, and before reading this answer I ultimately used DQL to achieve this, but I didn't want to use DQL at the beginning because my controller did not have any DQL in it, and I wanted to stick to the code style the controller already had. This solutions works really good for me! – ILikeTacos Jun 15 '13 at 16:22
3

Or simply : $this->getDoctrine()->getRepository('AcmeBundle:User')->findBy(array(), array('username' => 'ASC')); – Benji_X80 Oct 23 '14 at 09:54
2

@Benji_X80 While that one-liner is certainly shorter, it is not DRY at all. The findAll method belongs to the repository, not the controller. – Pier-Luc Gendreau Oct 23 '14 at 15:34
2

Can you tell entity to look for queries in the custom repository in any other way than using comments? It's the most terrible programming practice I've ever seen – Sejanus Dec 15 '14 at 10:04
1

@Sejanus That's how the docs want you to do it: http://symfony.com/doc/current/book/doctrine.html#custom-repository-classes – Pier-Luc Gendreau Dec 15 '14 at 19:55
1

For those of you who want to sort on an associated entity, see http://stackoverflow.com/questions/22828669/ordering-doctrine-collection-based-on-associated-entity-when-it-is-not-possible – eddy147 Oct 11 '16 at 09:59
1

I find it easier to just call the function `findBy()` directly rather than enmaskarating it as a `findAll()` – S. Dre May 06 '22 at 07:18

score 92 · Answer 2 · answered Sep 02 '14 at 10:18

92

$this->getDoctrine()->getRepository('MyBundle:MyTable')->findBy([], ['username' => 'ASC']);

answered Sep 02 '14 at 10:18

Stiig

1,265
13
19

score 29 · Answer 3 · answered Apr 04 '17 at 09:58

29

Simple:

$this->getDoctrine()->getRepository('AcmeBundle:User')->findBy(
    array(),
    array('username' => 'ASC')
);

answered Apr 04 '17 at 09:58

Daniele Dolci

884
1
9
22

This worked like a charm! And still works exactly this way with Symfony 4 – Robert Saylor Sep 10 '18 at 18:29

score 25 · Answer 4 · answered Nov 12 '15 at 10:03

It's useful to look at source code sometimes.

For example findAll implementation is very simple (vendor/doctrine/orm/lib/Doctrine/ORM/EntityRepository.php):

public function findAll()
{
    return $this->findBy(array());
}

So we look at findBy and find what we need (orderBy)

public function findBy(array $criteria, array $orderBy = null, $limit = null, $offset = null)

score 8 · Answer 5 · edited Feb 25 '15 at 15:35

8

This works for me:

$entities = $em->getRepository('MyBundle:MyTable')->findBy(array(),array('name' => 'ASC'));

Keeping the first array empty fetches back all data, it worked in my case.

edited Feb 25 '15 at 15:35

Bono

4,757
6
48
77

answered Feb 25 '15 at 14:55

Buttler

81
1
1

Shaz · Answer 6 · 2018-11-25T06:50:47.137

8

findBy method in Symfony excepts two parameters. First is array of fields you want to search on and second array is the the sort field and its order

public function findSorted()
    {
        return $this->findBy(['name'=>'Jhon'], ['date'=>'DESC']);
    }

edited Nov 25 '18 at 06:50

answered Nov 20 '18 at 08:30

Shaz

419
3
11

Can you add some explanation to your very short answer? – Nico Haase Nov 20 '18 at 09:00
1

That's a mighty short answer. Elaborate - explain... *edit*. – Paul Hodges Nov 20 '18 at 14:29
that was the perfect answer! findBy(array(), array('fieldname' => 'ASC') This will find all and sort on the field with the direction indicated. – Robert Saylor Jan 27 '20 at 12:26

score 7 · Answer 7 · edited Mar 02 '16 at 12:37

7

Look at the Doctrine API source-code :

class EntityRepository{
  ...
  public function findAll(){
    return $this->findBy(array());
  }
  ...
}

edited Mar 02 '16 at 12:37

John Slegers

45,213
22
199
169

answered Mar 02 '16 at 12:24

Gilles Vanderstraeten

79
1
3

score 6 · Answer 8 · edited Jan 13 '17 at 23:25

You need to use a criteria, for example:

<?php

namespace Bundle\Controller;

use Symfony\Bundle\FrameworkBundle\Controller\Controller;
use Symfony\Component\HttpFoundation\Request;
use Doctrine\Common\Collections\Criteria;

/**
* Thing controller
*/
class ThingController extends Controller
{
    public function thingsAction(Request $request, $id)
    {
        $ids=explode(',',$id);
        $criteria = new Criteria(null, <<DQL ordering expression>>, null, null );

        $rep    = $this->getDoctrine()->getManager()->getRepository('Bundle:Thing');
        $things = $rep->matching($criteria);
        return $this->render('Bundle:Thing:things.html.twig', [
            'entities' => $things,
        ]);
    }
}

score 3 · Answer 9 · answered Jun 15 '13 at 12:10

3

You can sort an existing ArrayCollection using an array iterator.

assuming $collection is your ArrayCollection returned by findAll()

$iterator = $collection->getIterator();
$iterator->uasort(function ($a, $b) {
    return ($a->getPropery() < $b->getProperty()) ? -1 : 1;
});
$collection = new ArrayCollection(iterator_to_array($iterator));

This can easily be turned into a function you can put into your repository in order to create findAllOrderBy() method.

answered Jun 15 '13 at 12:10

Nicolai Fröhlich

51,330
11
126
130

5

What's your point here? There are more than enough use-cases for this ... i.e. sorting an already fetched collection in PHP is always faster than performing another mysql query just for sorting! Imagine you need to output the same collection data in two different sorting styles on one page ... – Nicolai Fröhlich Oct 21 '13 at 13:36
3

In general, returning an ordered query should be the database's job. OTOH, this technique does have applicability to the more involved cases nifr mentions. – Lighthart Jan 23 '14 at 19:06

score 3 · Answer 10 · edited Jun 24 '15 at 19:18

3

Try this:

$em = $this->getDoctrine()->getManager();

$entities = $em->getRepository('MyBundle:MyTable')->findBy(array(), array('username' => 'ASC'));

edited Jun 24 '15 at 19:18

ZygD

22,092
39
79
102

answered Jun 24 '15 at 18:57

Mahdi Dhifi

129
1
1

Moisés Márquez · Answer 11 · 2014-12-30T12:05:26.747

2

I use an alternative to the solution that wrote nifr.

$resultRows = $repository->fetchAll();
uasort($resultRows, function($a, $b){
    if ($a->getProperty() == $b->getProperty()) {
        return 0;
    }
    return ($a->getProperty()< $b->getProperty()) ? -1 : 1;
});

It's quicker than the ORDER BY clause, and without the overhead of the Iterator.

edited Dec 30 '14 at 12:05

answered Dec 30 '14 at 11:53

Moisés Márquez

323
2
8

1

Please add some further explanation to your answer. How could sorting in your application be faster than doing it on the database level? – Nico Haase Jun 29 '20 at 10:34

score 2 · Answer 12 · answered Mar 27 '18 at 12:54

Modify the default findAll function in EntityRepository like this:

public function findAll( array $orderBy = null )
{
    return $this->findBy([], $orderBy);
}

That way you can use the ''findAll'' on any query for any data table with an option to sort the query

How to sort findAll Doctrine's method?

12 Answers12

Linked