How can I sort a result in Laravel with a custom attribution from my model?

Asked

Viewed 587 times

1

My real table is very large, so I will 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 redeem the full name of the user):

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

So when using the model, I can redeem the full name in 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 do a full name search:

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

I won’t be able to, since he won’t find the field 'full_name' in my bank, so to get around 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 from the bank only 3 values, but if I run the following command:

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

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

How can I sort a result in Laravel with a custom attribution from my model?

php laravel

3 answers

1

Short answer: There’s no way.

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

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

Would that be:

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

  • I just put as an example this code, to make it simpler to explain, but the field to which I want to make this comparison has several tests, and the code would be gigantic if it were to do so.

  • So, there’s no way you can pass a property computed by Eloquent to your query.

  • The way will be to bring in all the records and filter later. But maybe there’s something more obvious in the table that allows you to reduce the amount of records you’ll have to bring before running the filters.

  • Thank you very much, but I’ll wait a little longer, maybe someone knows the answer.

  • https://stackoverflow.com/questions/45586280/ordering-by-a-custom-attribute-in-laravel-5-model

  • I saw this link, the solution he gave and exactly as I described in the question, he takes from Collection.

  • Yeah, I put the link to stress that it’s impossible to do what you’re trying to do.

  • Anyway I’ll wait, because I’ve had experiences here in the stack of "impossible" questions and someone comes along and gives a brilliant answer, like this one: https://answall.com/questions/240236/howto perform

  • 1

    Well, I hope someone can find that solution for you :)

Show 4 more comments

0


Had a datatable and needed to sort it, but there were some formatted fields.

All my formatted fields I left with the default "fieldname", IE, it was always the original name plus the "_Formatted" at the end.

What I did to solve this problem, I created the following method

private function filterField($column)
{
    return str_replace(
        '_formatted',
        '',
        $column
    );
}

So when ordering, instead of using "field_name" I used "field_name" and if I entered a field that was not formatted the function would not replace anything and employee in the same way.

0

You cannot use data computed in SQL from your database, but you can use twice the orderBy and solve this problem as follows:

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

another way is to create a vision with concat bringing together the two pieces of information and doing a simple search for Databaseof Laravel, example:

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();

Browser other questions tagged php laravel

You are not signed in. Login or sign up in order to post.