How can I sort a result in Laravel with a custom attribute of my model?

Navigation
1

My actual table is very large, so I'll use this example I found on the internet to make it easier to explain my problem ( source ).

Table Fields: 

first_name
last_name
email
password
created_at
updated_at

In Laravel we can customize our fields in the model, for example (so we can retrieve the user's full name): 

function getFullNameAttribute()
{
  return $this->attributes['first_name'] . ' ' . $this->attributes['last_name'];
}

So while using the model, I can redeem the full name this way: 

$user = App\User::find(1);
echo $user->full_name;

We can do a search this way: 

$users = User::orderBy('name')->get();

But if I try to search by full name: 

$users = User::orderBy('full_name')->get();

I will not be able to, since it will not find the 'full_name' field in my bank, so to bypass this we do the following: 

$users = User::get()->sortBy('full_name');
return $users;

And here comes my problem, when you run the following command: 

$users = User::orderBy('name')->limit(3)->get();

It rescues the bank only 3 values, but if I execute the following command: 

$users = User::get()->sortBy('full_name')->take(3);

It takes all users from the database and then sorts and then takes the 3 values, causing slowness, since it searches all the values to make the filter.

How can I sort a result in Laravel with a custom attribute of my model?

    
asked by anonymous 29.03.2018 / 17:14

2 answers

1

Short answer: No way.

The bank only understands what's really in the bank.

The only way is to replicate the functionality of the custom attribute in your query. In your case, you need to use CONCAT of SQL. And to use these functions, you need to use orderByRaw .

It would be this: 

User::orderByRaw("CONCAT(first_name, ' ', last_name)")->limit(3)->get();
    
29.03.2018 / 19:19
0

You can not use a computed data in SQL of your database, but you can use orderBy twice and solve this problem as follows: 

$users = User::orderBy('first_name')
              ->orderBy('last_name')
              ->limit(3)
              ->get();

Another way is to create a view with concat by joining the two information and doing a simple search by Database of Laravel , example p>

View 

CREATE  
    VIEW 'database'.'view_usuario' 
    AS
    SELECT id, CONCAT(first_name, ' ', last_name) full_name FROM usuario

Laravel 

\DB::select('SELECT * FROM view_usuario ORDER BY full_name limit 3');

or 

\DB::table('view_usuario')->orderBy('full_name')->limit(3)->get();
    
30.03.2018 / 01:16
Overlapping labels Capture the moment the cell phone screen was turned on and when it was deleted