How To Query Entire Model For Value

Just add more conditions to the query

Client::where('first_name', 'LIKE', '%'.$keyword.'%')
    ->orWhere('last_name', 'LIKE', '%'.$keyword.'%')
    ->orWhere(...)
    ->get();

show me all clients where the first_name is like x or where last_name like x, etc.

@CRONIX - Thanks! I will vote that as best answer for the question however I was wondering...

What if I wanted to do a query where it checks for

first_name + last_name

for instance the $keyword being 'John Smith' or 'Jo Sm'?

Need more specific info. Do you mean if the search term is John Smith it should check first_name for John and last_name for Smith? That would be easy if everyone only had a single word for their first name and a single word for the last name, you'd just split on the space to separate first/last name. But, what if the name was Oscar De La Cruz Jr.? How do you know which is the first name and which is the last name, if you're going by spaces in the names?

@CRONIX - Yeah so If I send a search $keyword from my search input as John Smith It will look for a record where first_name + last_name matches the given $keyword string AKA 'John Smith'...

You would be required to use concat on a select statement and then query that

$q->select(\DB::raw("CONCAT(first_name,' ',last_name) AS name") )
                              ->havingRaw(" name LIKE '%$value%' ");

@CRONIX - Well In the instance of Oscar De La Cruz Jr perhaps it would require an if() statement that checks for structure of string? if that is even possible Lol

For instance if given $keyword is more than first_name + last_name run this

Client::where('first_name', 'LIKE', '%'.$keyword.'%')
    ->orWhere('last_name', 'LIKE', '%'.$keyword.'%')
    ->orWhere(...)
    ->get();

Else run whatever method would check for first_name + last_name

Point is, if the search term is Oscar De La Cruz Jr., you can't know beforehand just based on that string which is the first name and which is the last name. We know, because we're human. There are people with multiple word first names, as well, like "Mary Jo". Combine that with a multiple word last name and you start to get into even more complex issues, eg "Mary Jo De La Cruz"

If you had 2 separate search boxes, (first/last name) that would solve this. If first name is filled in, search first_name field. If last name is filled in, search last_name field. If both are filled in, search both, but at least you will know which is the first name and which is the last name to search the appropriate fields.

Another option is to use fuzzy searching or weighted searches, which are pretty complex for this forum but you can search the terms. There are also things like Algolia search which make this easier.

@CRONIX - Well thanks for the info! I will check out the options provided and see what I can come up with.

I understand the issue of unpredictable conditions I was just throwing out ideas!

@CMDOBUENO - I gave your method a shot and I get the following alarm

call to member function select() on array

Here is what I got if you want to check it out

$client = $q->select(\DB::raw("CONCAT(first_name,' ',last_name) AS name") )
        ->havingRaw( " name LIKE '%$request->keyword%'")
        ->get();

@boldstar

let me make it a bit more clear for you... i just grabbed that out of existing code.

$results = Client::query()
            ->select(\DB::raw("CONCAT(first_name,' ',last_name) AS name") )
            ->havingRaw( " name LIKE '%$request->keyword%'")
            ->with('engagements')
            ->get();

Note: You can leave out the query(), I just added that to make the rest of the lines match up easier, it is 100% valid, but a totally optional addition... i use it in my code so my -> can all be on their own line without things looking weird... it actually adds no value otherwise

Hope that helps, and as long as I have no dumb typos that should work... but let me know if it doesnt.

This above could will make it so you can have a single entry point for name allowing it to make less strict. So the above issues of multi-word names should not be an issue anymore.

@CMDOBUENO - thank you for the clarity, so it seems to be working as far as CONCAT, however the data returned is only the name object which is created by the CONCAT.

So if I do a search for John It simply returns whatever name matches instead of the entire record like so

{
     name:  'John Smith'
}

Where as it should return the record for John Smith like

{
    first_name: 'John'
    last_name: 'Smith'
    engagements: [
            { engagement: 1},
                { engagement: 1},
                { engagement: 1},
        ]
}

Here is the set up

public function search(Request $request)
    {

        $client = $request->validate([
            'keyword' => 'required|string'
        ]);
        
        $results = Client::query()
    ->select(\DB::raw("CONCAT(first_name,' ',last_name) AS name") )       
        ->havingRaw( " name LIKE '%$request->keyword%'")
        ->with('engagements')
        ->get();

        return response()->json($results);
    }

note* this also happens if I do the more complex search of John Smith. It still only returns the name object.

I am not sure if I mentioned that I am doing this through ajax if that makes a difference? again thank you for the help!

Select the other fields you need then. The only thing it is currently selecting are the concatenated field that was aliased to name.

->select('first_name', 'last_name', 'another_field')
->select(\DB::raw("CONCAT(first_name,' ',last_name) AS name") )

Or if you want all fields, add a * to the select

->select(\DB::raw("*, CONCAT(first_name,' ',last_name) AS name") )

@CRONIX - Adding a * solved it. Thank you! any recommended courses on getting better at raw expressions?

Yes, mine was on a releation's sub-query for filtering, so i forgot about adding the additional fields into the select. But glad you got it all working.

@BOLDSTAR - > getting better at raw expressions?

I think that's the wrong way to look at it. I'd really start with learning raw SQL. SQL can do a lot more than just what's built into Eloquent. Eloquent just covers the most basic of things, which is really probably only about 15% of what SQL can do. It's like using Laravel but not knowing the other thousands of built-in php functions that are available. Those who are not proficient in the underlying language won't get the most out of it and will do various things inefficiently. For instance, selecting * is very inefficient compared to selecting specific columns (because behind the scenes the db then has to actually lookup the columns to see what they are - adding an extra step), yet that's what Laravel does by default for all queries (purely using the ORM and not raw statements).

I'd highly suggest taking some SQL tutorials.

Check out the docs for MySQL (or whatever you're using). You'll notice there are thousands of commands that aren't built into Laravels ORM. https://dev.mysql.com/doc/refman/5.7/en/functions.html

It is very good advice.

I have moved away from Model::get() and towards Model::get(['id','name',...]);

There are only specific times i want all fields, and those are actually somewhat rare, example being an edit page for a user. But in all honesty, I still do not want all the users fields here... I do not really need their timestamps (created/updated_at). Additionally depending on your routing, you may not even need their ID, because this could be do via your route model binding.

It is important to understand how to best utilize sql. It was something I was extremely against (using DB or Raw queries) for a long time. I use them more and more now, but again, purpose and place are important.

Well thanks for the help! I will take the advice.