Just a quick tip that really extends an old post from here about MySQL's ORDER BY FIELD ( ENUM and ORDER BY FIELD - customized sorting in mysql)

Building an API and we want to allow users to request items and have them returned in the same order they ask for them. In other words, if they request items/?id=2,7,3,1 the response should come back ordered by id's 2,7,3,1

The solution is quite simple (once I remembered my own old trick for this) - we collect the id's string and explode() them to make a whereIn() clause, then use that same string in our ORDER BY FIELD. So the query we are after looks like:

SELECT id, morestuff
FROM items
WHERE id IN (2,7,3,1)
ORDER BY FIELD (id, 2,7,3,1);

The "quick tip" is how to get the order by to work with Laravel. The usual function is ->orderBy('field', 'ASC|DESC'), but that won't work for us because if you use ASC or DESC, it overrides your FIELD and won't sort as you like. However, the function expects a second parameter and will add a default ASC if you leave it out.

Fortunately, Taylor was nice enough to always make sure developers can get in there and write their own code when they need to. While the Laravel.com's site doesn't list everything, the API docs have a lot of hidden gems. If you look at the API docs and drill down to Illuminate\Database\Query\Builder you see that each of the main clauses of your CRUD statements have an underlying raw() version where you can write your own code. The one we wanted was orderByRaw(). Here's the whole thing in all it's glory (a bit modified from my actual code, which is not something I can simply copy/paste for you):

// $input['id'] is a string = "2,7,3,1"
// $ids is an array of that string

$ids = $this->urlHelper->explode_ids($input['id']);

// NOTICE! id in the FIELD function should NOT have quotes - this is mysql. After the field, we
// list ids, again no array as this is raw sql.

$this->repo->whereIn($ids)->orderByRaw(\DB::raw("FIELD(id, ".$input['id']." )"))->get();

That's all there is to it! I probably don't even need that \DB::raw() wrapper, but I like to use it whenever I'm adding my own sql this way, so call it habit. A good lesson in why you should try to get as familiar with the API Docs as you can.

Hope that helped!

Read it Monday, Use it by Friday!

Laravel Quick Tips Weekly Newsletter. Short, immediately helpful bits you'll use in your own codebase before the next one arrives.

Join us now and get a FREE PDF of the first fix months of Laravel Quick Tips!

No Spam, Unsubscribe Anytime

Contact me

Get Laravel Quick Tips Every Week!

Join us now and get a FREE PDF of the first fix months of Laravel Quick Tips!

No Spam, Unsubscribe Anytime