Using a Laravel model method in an Eloquent query

Question

This is for Laravel 5.2. I have a method defined as follows in my Users model:

public function name()
{
    return "$this->name_first $this->name_last";
}

I'm trying to figure out how to use that as part of a query, but it seems like it isn't possible for a somewhat obvious reason: the database doesn't know anything about the method and that makes perfect sense. However, the concept of what I'm trying to achieve makes sense in certain contexts, so I'm trying to see if there's a way to accomplish it naturally in Eloquent.

This doesn't work, but it represents what I'm trying to accomplish:

public function index(Request $request)
{
    $query = new User();

    if(Request::has('name')) {
        $query = $query->where('name', 'LIKE', '%' . Request::input('name') . '%');
    }

    return $query->get();
}

In short, the database only knows about name_first and name_last, but I'd like to be able to search (and sort) on name without storing it. Maybe storing the concatenated name is no big deal and I should just do it, but I'm also trying to learn.

score 4 · Accepted Answer · answered Mar 08 '16 at 00:17

I agree with Bogdan, The issue of having spaces either in the first or the last name makes querying on the individual columns difficult, so this is probably the way to go. Code reuse can be increased by defining it as a custom scope: https://laravel.com/docs/5.2/eloquent#local-scopes

// class User
public function scopeOfFullNameLike($query, $fullName)
{
    return $query->whereRaw('CONCAT(name_first, " ", name_last) LIKE "%?%"', [$fullName]);
}
// ...
User::ofFullNameLike('john doe')->get();

Thanks for the great example of how to use a local scope. – Anthony Mar 08 '16 at 15:13 — Anthony, Mar 08 '16 at 15:13

Bogdan · Answer 2 · 2016-03-08T19:03:11.840

3

That would mean you should be concatenating the column value at the database level. Which means you could use CONCAT and a whereRaw clause:

$query->whereRaw('CONCAT(name_first, " ", name_last) LIKE ?', ['%' . Request::input('name') . '%']);

Or as an alternative if you want the full name to be selected as part of the result, you could concatenate within the select and use having instead of where to be able to use a column alias:

$query->select('*', DB::raw('CONCAT(name_first, " ", name_last) as name'))
      ->having('name', 'LIKE', '%' . Request::input('name') . '%');

Not the most compact solutions, but things involving MySQL functions need some raw SQL to work with the Query Builder.

edited Mar 08 '16 at 19:03

answered Mar 08 '16 at 00:02

Bogdan

43,166
12
128
129

Thanks. This was very informative and helpful. The use of `having ` instead of some type of `where` could potentially cause the query to return a huge amount of records, right? – Anthony Mar 08 '16 at 13:39
1

Using `having` is equivalent to `where` with the notable exceptions that it is generally used when using aggregate functions and it is executed after `GROUP BY`. In my second example it facilitates access to column aliases, otherwise using `where name = ?` would not work since `name` is a column alias. For this simple example it does not affect the query result, for more complex queries you should take into consideration the execution order mentioned above. – Bogdan Mar 08 '16 at 14:03
I see what you mean, but I'm still not sure how that works under the hood. This no longer has anything to do with my question, but wouldn't `having` in a situation like this consume more RAM to do the same thing since `where` would exclude rows in real time instead of after the fact? – Anthony Mar 08 '16 at 15:15
1

@Anthony for this particular case, they're equivalent, no noticeable performance or memory consumption. I actually did a simple benchmark a while back in response to a similar question here on SO, and I saw no difference for simple queries such as this on a table with 1 million entries. – Bogdan Mar 08 '16 at 15:48
That's very interesting. Thanks. – Anthony Mar 08 '16 at 15:49

Using a Laravel model method in an Eloquent query

2 Answers2

Linked