How to query an appended attribute or add it to Eloquent Model

$query->where('first_name', 'like', '%' . $request->term . '%')
->where('last_name', 'like', '%' . $request->term . '%');

Why won't that work? To simple ;). Why have the first and last name separated in the database if you generally always use them together?

Another approach, like a slug field, generate the name on create and update and save it the the user table too.

Sometimes we overcomplicate things I think. Your question is asking how to do something that is easy and make it harder.

@jekinney

Not the case, because it would be:

return $query->where($request->field, 'like', '%' . $request->term . '%');

Where the field is the column name. i can have email, first_name, etc, but not the appended attribute, in this case name.

I know that is a simple example, also i know that is the easy way to go, but still doesn't answer my question. I'm trying to figure out how to append 'virtual' columns into Eloquent.

As you suspect, you can't query the database on something that is only realised when you deal with each row.

I faced a similar problem and chose to add a new column to the database just for searching.

The alternative is to hide it away in a static method in your model so that you don't have to look at the code too often.

Add searchByName($term) to your model and then split the term and search it as you have shown.

@ManuelAzar

Like I stated, if you listed the where clauses you'd be done right? Somethings just can't be dynamic as we would like as @Snapey mentioned. Unless you grab everything from the table and loop through each row and look for your result via php and not mysql.

you need to be smarter about the search though....

if the search is 'smith' then the only time your query would return a result is if the users name was something like 'smithers smith' (both names contain the search term)

if you use orWhere for the second search then this works if the search term is 'smith' but not if the term is 'bob smith' since neither the first name or the last name is 'bob smith'

you can split the search term on a space and then use different searches for single term and double terms

@Snapey

Yes, something like:

// split on 1+ whitespace & ignore empty (eg. trailing 
$searchValues = preg_split('/\s+/', $request->term, -1, PREG_SPLIT_NO_EMPTY);
foreach ($searchValues as $value){
    $query->where('first_name', 'like', "{$value}%")->orWhere('last_name', 'like', "{$value}%");    
} 
return $query;

I already figured that out. It was just a dumb example.

I appreciate all of your replies, but still gentlemen, @Snapey & @jekinney. my question has not been answered: How can we search through appended fields or add them to eloquent model?

Another example would be: if i had my product table and different tiers, every product has its cost and my price would change for the tier related to the user and if it is on sale, so i would store the sale percentage and the price for each tier on my table.

Then i would have my appended price field, and on the price accessor i would calculate the total price for the user, from its tier along with the discount if it is on sale.

And as a user i would like to search through that calculated price or do something else rather than accessing it as $product->price

//Model
App\Product {#902                      
  cost: "102.00",                      
  retail_price: "178.50",              
  wholesaler_a_price: "153.00",        
  wholesaler_b_price: "137.70",        
  wholesaler_c_price: "127.50",        
  on_sale: 1,                             
  sale_percent: 42,
  //I WANT THE TOTAL PRICE HERE                    
}      
      
//Array
 App\Product::first()->toArray()                                 
 [                                                                
  "cost" => "102.00",                                            
  "retail_price" => "178.50",                                    
  "wholesaler_a_price" => "153.00",                              
  "wholesaler_b_price" => "137.70",                              
  "wholesaler_c_price" => "127.50",                              
  "on_sale" => 1,                                                   
  "sale_percent" => 42,                                          
  "price" => "103.54",  //LIKE THIS ONE                                 
]   

Can't think of another example at the moment.

As i said, you cannot ask your database to search for something that does not actually exist in the table

Accessors are only called when you ask for the derived property. They cannot be used in the query. Or as @jekinney says grab ALL the rows in memory and then filter it with PHP, but knowing this would be totally impractical for more than a few hundred rows.

So, to answer your question... you can't.

@Snapey thanks sir, sorry if i was being stubborn.

To work with it as you want and then be able to do something like $query->where('name', 'like', '%' . $request->term . '%'); What you should do is create a local query scope, and then use concat ex:

function scopeWhereName($query, $value) {
    $query->where(\DB::raw('concat(firstname, " ", lastname)'), 'LIKE', "%{$value}%");
}
//then you can use it like this
$yourQuery->whereName($request->get('term'))->get();

Hello, Does anyone know if there is any update with Laravel 5.6 that will make any changes to the recommended solutions?

I am much stronger in using MySQL to query results; however, am trying with much reading and study (brand new to Laravel) to learn the best way to leverage the API.

Would appreciate advice from anyone, especially those others in this thread with much experience, such as @Snapey, @jekinney and @monkeyslippery.

I would like to accomplish something similar to what @ManuelAzar describes; however, my attribute is based on a belongsToMany - where the table in question is joined to 2 other tables to retrieve information.

In an app that I have inherited and am converting from an older PHP framework to Laravel 5.6, we have the concept of "Worklists". (Please excuse the weird data model naming conventions, as this was not my doing.)

I have an Entity model that has a property:

    public function worklists()
    {
        return $this->belongsToMany(
        'App\Models\User_Worklist', 'Users_Worklists_Members', 'MemberID', 'ListID');
    }

The same model has an attribute:

    public function getWorklistIdsAttribute()
    {
        return $this->worklists()->pluck('id')->flatten();
    }

The attribute 'worklist_ids' works nicely for selecting in yajra datatables, but now for the filtering...

If in my Controller, I want to query where my Entity has certain worklist_ids, would the most efficient solution be to add a Scope as recommended by @monkeyslippery? If so, does the scope go on the Model or the Controller? And is RAW still the best way to do this? My Entity table has 100s of thousands of records, so performance is of concern for me.

Thanks in advance to anyone for your time!

I would use join with a select for Users_Worklists_Members.ListId and ID instead if what you need is just the ID where the relationship Users_Worlists_Members.ListID attribute matches your certain worklist_ids. I hope it's clear enough Btw, you can ofc create a local scope for this query in case you need it in different parts of your app

Thanks so much @monkeyslippery for your reply!

Now that I know more about Laravel, I understand that I actually need a "hasManyThrough" relationship on my Entity Model, which looks like this:

public function worklists()
    {
        return $this->hasManyThrough('App\Models\User_Worklist',
            'App\Models\User_Worklist_Member',
            'MemberID',
            'id',
            'id',
            'ListID' 
        )->where('Users_Worklists.DataType', '=',$this->dataType);
    }

The "where" is important, since any instance of Entity Model can have a different dataType attribute, and can belong to many User_Worklist Model instances, but only where the dataType is the same.

Once I learned how to set up all the relationships, this is working great.

In yajra datatables, the property can be added using Entity::with('worklists')..., but I do not yet have the correct "render" method for the column. Performance is very nice, though.

Filtering does not work; however, with message "hasManyThrough is not yet supported".

I will post a question in that forum, since without the "hasManyThrough" - just using raw joins - yajra datatables performance is now unacceptable.

Do you see / hear anywhere I might be heading wrong?

Either way, I appreciate so much your time and feedback!