Preserve the initial order of array in Symfony2 Doctrine findBy()

Question

I have an array ($newsList) of IDs with the following values: 4,2,1,3.

I'm trying to get some data from the database from entity:

$news=$this->getDoctrine()->getRepository('Bundle:News')->findBy(array('id' => $newsList));

$newsList array represents real IDs from the News entity.

But when I do:

foreach($news as $n){
    $n->getId();
}

IDs are in order: 1,2,3,4.

How can I preserve the order from beginning in foreach?

Bechuanaland

Answer 1

As you are doing a global query, you can just order your results by a given property, and a given sort order, so as pointed by the previous answer, you can't.

To have your results ordered exactly as the array you given, you can do this:

$newsList = [3, 1, 4, 2];
$newsRepo = $this->getDoctrine()->getRepository('Bundle:News');

foreach ($newsList as $id) {
    $new = $newsRepo->findOneBy['id' => $id];

    if ($new !== null) {
        $news[] = $new;
    }
}

Like this, your results are ordered exactly like $newsList, e.g:

foreach ($news as $n) {
    print $n->getId();
}

// Output: 3 1 4 2

Kwangtung

Thanks a lot! I had a similar conception for this but missed some crucial point, that dit the trick. – Bechuanaland

That may be a lot of queries if the $newsList is bigger... ;-) – Searcy

Sure, it may be better to filter the results after doing the global query, but ATM I don't have a "nice" way in mind. For this small example, it's not a big deal :) – Kwangtung

Answer 2

You can't.

It's how it's returned from DB. If you won't specify ORDER BY clause in query, database will return rows as they are in storage, and this is usually the same as id ASC.

You should sort them on your own in PHP.

Searcy

Answer 3

You can use usort and the spaceship operator:

$newsList = [4, 2, 1, 3];
$news = $this->getDoctrine()->getRepository(News::class)->findBy(['id' => $newsList]);

$order = array_flip($newsList);
usort($news, function($a, $b) use ($order) {
    return $order[$a->getId()] <=> $order[$b->getId()];
});

// $news is now ordered by id 4,2,1,3

Vite

Answer 4

IMO the best solution is to use field function to get proper news order:

$ids = [4, 1, 2, 3];
$news  = $this->getDoctrine()->getRepository('Bundle:News')->createQueryBuilder('n')
    ->select('n, field(n.id, :ids) as HIDDEN field')
    ->where("n.id IN (:ids)")
    ->setParameter('ids', $ids )
    ->orderBy(field)
    ->getQuery()
    ->getResult();

Note: this solution require custom field function that can be found here.

Jaquenette

Answer 5

I don't know if you can actually get them sorted from the returning query results, but you can do either of these options:

Do a foreach loop and execute 'findByOne' for each result:

foreach($newsList as $id) {
    $news[]=$this->getDoctrine()->getRepository('Bundle:News')->findBy(array('id' => $id));
}

Order the results of the query afterwards:

$news=$this->getDoctrine()->getRepository('Bundle:News')->findBy(array('id' => $newsList));
foreach($newsList as $id) {
    $sorted_news[] = $news[$id];
}

Zeal

Answer 6

Try this:

$news = $this->getDoctrine()->getRepository('Bundle:News')->createQueryBuilder('n')
        ->where("n.id IN(:Ids)")
        ->setParameter('Ids', $newsList ) // $newsList = array(1,2,3,4,...)
        ->getQuery()
        ->getResult();

Dianndianna

This will generate the same query. Where will be difference here? – Searcy

@dragoste I did not know before – Dianndianna