XF 2.0 Order by FIELD with the entity finder system?

[Jaxel]

I have an array of IDs. I want to fetch from a table, all items that match those IDs... But I also want the results to match the order of the original array.

If I were to do this in pure MySQL, I would do the following:

SELECT *
    FROM ewr_medio_media
WHERE media_id IN('9292','9301','9290')
ORDER BY FIELD(media_id, '9292','9301','9290')

Is something like this possible with the entity system?

 

[S Thomas]

I'm pretty sure, you shouldn't do that, but if you insist on using the finder system..

$dirty = $this->finder("XF:Thread")->whereSql("user_id IN (1,2)) ORDER BY FIELD(user_id, 2, 1")->fetchAll();

Yea, it's pretty much injecting sql
Other than that, if you have a look at Mvc\Entity\Finder, you'll notice that the order by clause is pretty static. Also stated here: https://xenforo.com/xf2-docs/dev/entities-finders-repositories/#order-method

 

[Jaxel]

Yeah... not a fan of injecting SQL like that...

I need the entity system though.

 

[S Thomas]

Well, you could always manually order your result entities by emulating the group by field behaviour within your script -> https://stackoverflow.com/a/11145568/8461611

 

[Jaxel]

I got this working:

        $mediaIDs = $this->db()->quote($playlist->playlist_media);
       
        $medias = $this->db()->fetchAll("
            SELECT *
                FROM ewr_medio_media
            WHERE media_id IN( $mediaIDs )
            ORDER BY FIELD(media_id, $mediaIDs )
        ");
       
        foreach ($medias AS &$media)
        {
            $media = \XF::em()->instantiateEntity('EWR\Medio:Media', $media);
        }

Is it bad form to do what I did?

 

[S Thomas]

I guess it's fine, because this is ultimately what the finder itself does. ( $this->db->query($q)->fetchAll(); )

 

[Jeremy P]

You can use a finder expression with order():

$finder = \XF::finder('Vendor\Addon:Entity');
$entities = $finder
    ->order($finder->expresssion('FIELD(%s, 1, 2, 3)', 'column_name'))
    ->fetch();