group of people choosing books

🐬 MySQL Order By Priority

Today I have been faced on situation where I need to display a list of users ordered by their name and their level. My table looks like below

idfirst_namelast_naelevel
1MuhammadSalahplayer
2JokoToleadmin
3CristioanoRonaldoplayer
4ZlatanIbrahimovicplayer
5JoseMourinhocoach
6MesutOzilplayer
7PepGuardiolacoach
8EdenHazardplayer
9PetrCechassistant manager
example table

My task is to show the table sorted by name, but give a priority order as: admin, coach, assistant manager, player.

You know we just can not order by level column since the result will sorted alphabetically.

Continue reading

How to Use `USE INDEX`, `FORCE INDEX` MySQL in Laravel Eloquent

Using Laravel Eloquent become one of the code standards in my organization. But as we all know sometimes using ORM like Eloquent limits us from using advanced SQL features.

Here is my experience when I faced an Eloquent query that was very dynamic and required me to tell MySQL to use a specific index for the query. To do so, basically I just needed to add the USE INDEX function. However, Eloquent does not come with this functionality by default.

So in order to do it I add a scope in my model like below:

// app/Models/User.php

use Illuminate\Contracts\Database\Eloquent\Builder;

public function scopeUseIndex(Builder $query, string $index): Builder
{
    return $query->from(DB::raw("{$this->getTable()} USE INDEX({$index})"));
}

This way I can just call

User::useIndex('my_index_name');

Thats it! 🤭🤭