All our highlights from 2023.
WordPress.com’s Year in Review
🐬 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
id | first_name | last_nae | level |
1 | Muhammad | Salah | player |
2 | Joko | Tole | admin |
3 | Cristioano | Ronaldo | player |
4 | Zlatan | Ibrahimovic | player |
5 | Jose | Mourinho | coach |
6 | Mesut | Ozil | player |
7 | Pep | Guardiola | coach |
8 | Eden | Hazard | player |
9 | Petr | Cech | assistant manager |
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.
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! 🤭🤭
Hello world!
Welcome to WordPress. This is your first post. Edit or delete it, then start writing!