How do I search records within the eager loaded data?

Level 75

If searching for titles starting with something, but want a user name also who wrote them, you'd have to add a second where to get the user name. You can have more than one where clause.

Or just show post titles by that user id using where('title', 'LIKE', '%' . $query . '%')

Level 67

Are you talking about searching within the results of the query, or within the query itself? Like get all posts where user->name = 'bob' AND blog.title like %term%?

You'd also want to eager load the user relationship.

Level 3

@Cronix

Ah sorry, I'm referring to searching within the results of the query. I'd like to include {{ $post->user->fullname }} into my search query.

https://laravel.com/docs/5.6/collections#method-where

Level 67

I assume you have a collection of results then ($posts). So you can use the collection method where to search within them. It's almost like the db method where, but it's a collection method to search within the collection (results).

Level 3

@Cronix

Are you suggesting that I need to combine these into collection

Blog::where('title', 'LIKE', '%' . $query . '%')->get();

User::where('name', 'LIKE', '%' . $query . '%')->get();

Level 67

No. Did you read the docs I posted the link to for searching within a collection?

My answer was based on your reply here:

Ah sorry, I'm referring to searching within the results of the query

Level 67

So you have this query returning results.

$posts = Blog::where('title', 'LIKE', '%' . $query . '%')->latest()->paginate(10);

So you'd search within those results (from the docs I linked to)...

$postsByUser = $posts->where(your-conditions);

However, if you want to query the db and get only the posts made by a user (instead of by any user), the answer is different. I'm only going by what you said though...

Level 3

Yeah, I did read but I'm just not quite sure how to apply it to my search query. I could do a search within a single DB using where() or orWhere but when it comes to data that is eager loaded from another Model, in this case, $post->user->name, I'm clueless.

But no worries about it. I'll probably do more research. Thanks, @Cronix .

Level 3

So you have this query returning results.

Oh no, I have already done the query returning the results and I'm preparing a search form to do a query.

        $query = request('q');

        $posts = Blog::where('title', 'LIKE', '%' . $query . '%')
        ->orWhere( ????,  'LIKE', '%' . $query . '%') // this is where I'm clueless
        ->latest()->paginate(10);

        if(request()->ajax()) {
            return response()->json([
                'html' => view('user.posts', compact('posts', 'tags'))->render()
            ]);
        } 

        return view('dashboard', compact('posts'));

As you can see, I'm only able to search for the title but I'd like to the query to search for $post->user->name too.

Level 67

You're confusing me with your terminology...

not quite sure how to apply it to my search query

Are you wanting

A single query to get results where title like (title) AND username is (username). This would only get posts where the username is (username) and the title is like (title)
A single query to get results where title like (title), then search within those results for a user with a username (username). This would get all results for all users where title like (title), and then later filter through those results to find the results by username. If you're only displaying the results for a single user, this is not the way to do it. You'd use #1.

Level 67

Ok, so what you've been telling me is not what you're really wanting.

Ah sorry, I'm referring to searching within the results of the query

You're wanting a single query (#1 in my above post)

$username = 'bob';
$title = 'some-title';

$posts = Blog::with(['user' => function($query) use ($username) {
    $query->where('name', $username);
}])
    ->where('title', 'LIKE', '%' . $title . '%')
    ->latest()
    ->paginate(10);

This will get all posts where the users name is 'bob', and the title is like 'some-title'

This assumes you have a user relationship setup in your Blog model. https://laravel.com/docs/5.6/eloquent-relationships#constraining-eager-loads

Level 3

Big apology that I have confused you. It's my fault for not being clear in my question.

If you don't mind, have a look at this. As an admin, I have a list of posts sorted by title and authors ($post->user->name) which is eager loaded within.

Here's my search controller

        $tags  = Tag::with('posts')->get();

        $query = request('q');

        $posts = Blog::where('title', 'LIKE', '%' . $query . '%')
        ->latest()->paginate(10);

        if(request()->ajax()) {
            return response()->json([
                'html' => view('user.posts', compact('posts', 'tags'))->render()
            ]);
        } 

        return view('dashboard', compact('posts', 'tags'));

As for now, I could do a search for post title only and I'm wondering if it is possible to include the author name in my search results.

Level 67

Lol, ok, try this.

$title = 'some title';

$posts = Blog::with('user')
    ->where('title', 'LIKE', '%' . $title . '%')
    ->latest()
    ->paginate(10);

It will get the posts where title like ($title), as well as the user for each post.

This again assumes you have a user relationship set up on your Blog model.

@foreach ($posts as $post)
    <div>Title: {{ $post->title }} posted by {{ $post->user->name }}</div>
@endforeach

Level 3

But I'm getting the query from the search form, how could I use

$posts = Blog::with('user')
    ->where('title', 'LIKE', '%' . $title . '%')
    ->latest()
    ->paginate(10);

Shouldn't $title be the value collected from the search form?

Level 67

Of course. The same way you were doing it before. I just prefer to name things to represent what they are. Since you're searching for a title, I'd use $title instead of just $query. It makes more sense.

$title = request('q');

Level 3

I tried using your code but when I search for name it returns null.

Level 67

Do you have a user relationship set up on your Blog model? What do you mean "Search for name"?

If you don't, you need to set that up. https://laravel.com/docs/5.6/eloquent-relationships#one-to-one

Level 3

Do you have a user relationship set up on your Blog model? What do you mean "Search for name"?

Of course, I have. Otherwise, I'm not able to output the name of the author {{ $post->user->name }}.

Search for name means that say, I have an author name "Cronix" who wrote an article "I love Laravel".

So I do a search for Laravel, it returns the result because the title has the search term "Laravel".

Say I search for "Cronix", it returns "null".

Not sure if I'm explaining it right.

Level 3

Coming back to this screenshot.

If I search for "Laravel", it returns 2 results. If I search for "API", it returns 1 result.

But if I search for "Alexis" , it returns "null" which is of course it's obvious because I did not set the search query to include {{$post->user->name}}.

        $query = request('q');

        $posts = Blog::where('title', 'LIKE', '%' . $query . '%')
        ->latest()->paginate(10);

        return view('dashboard', compact('posts', 'tags'));

Best Answer

Level 67

Ok, now it's almost the same thing going back to this post: https://laracasts.com/discuss/channels/code-review/how-do-i-search-records-within-the-eager-loaded-data/replies/428700

$searchTerm = request('q');

$posts = Blog::with(['user' => function($query) use ($searchTerm) {
    $query->where('name', $searchTerm);
}])
    ->orWhere('title', 'LIKE', '%' . $searchTerm . '%')
    ->latest()
    ->paginate(10);

You didn't mention that you wanted to search by blog title OR username

Level 3

Yes, it's almost there. Thanks for being so patient, @Cronix. I relook at your replies earlier and I did this

        $posts = Blog::with(['user' => function($query) use ($term) {
            $query->where('first_name', $term)->orWhere('last_name', $term); }])
                    ->orWhere('title', 'LIKE', '%' . $term . '%')
                    ->latest()
                    ->paginate(10);

And in User model

    public function getFullNameAttribute()
    {
        return "{$this->first_name} {$this->last_name}";
    }

I'm getting the following error,

Trying to get property 'fullname' of non-object

jlrdw

Level 75

I have an author name "Cronix" who wrote an article "I love Laravel". So I do a search for Laravel, it returns the result because the title has the search term "Laravel".

Say I search for "Cronix", it returns "null".

You shouldn't have to search for the author, as there will be an author.

Surely you have the blog table and author linked by author_id.

You probably could in your case just do a simple left join along with a group by and use two order by statements like

order by title order by author

Because you only want matches that have part of that title and show the author. Remember could be several authors and several post

But again you may want to order by author first and title second. Hard to say.

Sometimes eloquent is alright for a simpler one to many, but as things get a little more complex, I just prefer normal queries.

Just done in raw mysql quickly from my test db, but something like:

SELECT dc_powners.ownerid, dc_pets.petid, dc_pets.petowner, dc_pets.petname
FROM dc_powners LEFT JOIN dc_pets ON dc_powners.ownerid = dc_pets.ownerid
WHERE dc_powners.ownerid IS NOT NULL AND dc_powners.ownerid > 0 AND dc_pets.petid IS NOT NULL
GROUP BY dc_powners.ownerid, dc_pets.petname;

Here is image https://imgur.com/AMDPh9X

Remember

Even regular queries need some trial and error and tweaking to get right, unless you use a query designer. I usually use the one in MS ACCESS and convert over to PDO as needed.

But again I just whipped this up quickly before bed time.

But you could take those results and loop (foreach) easily in the view.

If thousands of results, that's where a temp table comes in, or a custom LengthAware Paginator.

It is nothing for some large industry to run large queries (reports) over night. They deal with tens of thousands of records or millions.

But you probably only have a few hundred, but remember it can grow.

nolros

Level 23

@yansusanto here you go

    public function blogPostOfTitle($title, $firstName, $lastName)
    {

        return Blog::where('title', 'LIKE', '%' . $title . '%')
            ->with(['user', function ($q) use ($firstName, $lastName) {
                $q->where('first_name', $firstName);
                $q->where('last_name', $lastName);
            }])
            ->latest()
            ->paginate(10);
    }

you also have the option of map but that wouldn't work with pagination

    public function blogPostWithNoPagination($title, $firstName, $lastName)
    {
        return Blog::where('title', 'LIKE', '%' . $title . '%')
               ->with(['user'])
                ->latest()
                ->map(function ($post) use ($firstName, $lastName) {
                    return $post->first_name === $firstName && $post->last_name === $lastName;
                });

    }

sujancse

Level 10

@yansusanto are you clear about, what are you looking for? If you are clear then your answer is already given by @Cronix. If your search field name is search_keyword then

$search_keyword = $request->input('search_keyword');

$posts = Blog::with(['user' => function($query) use ($search_keyword) {
    $query->where('name', 'LIKE', '%', .$search_keyword. '%');
}])
    ->where('title', 'LIKE', '%' . $search_keyword . '%')
    ->latest()
    ->paginate(10);

This should solve your problem, just make sure you are getting the search keyword using dd($search_keyword);

Level 3

@sujancse

yes, the answer given by @Cronix works if there is a name field but in my case, there isn't fullname in the field.

Why? Because my list of records consists of {{ $post->title }} by {{ $post->user->fullname }} // eager loaded here.

where

    public function getFullNameAttribute()
    {
        return "{$this->first_name} {$this->last_name}";
    }

$search_keyword = $request->input('search_keyword');

$posts = Blog::with(['user' => function($query) use ($search_keyword) {
    $query->where('fullname', 'LIKE', '%', .$search_keyword. '%'); // can't use fullname here
}])
    ->where('title', 'LIKE', '%' . $search_keyword . '%')
    ->latest()
    ->paginate(10);

@nolros

Not sure if that actually works in my case as what I'm trying to do is to create a search function but I do appreciate your answer here.

sujancse

Level 10

@yansusanto then go with both first_name and last_name

$search_keyword = $request->input('search_keyword');

$posts = Blog::with(['user' => function($query) use ($search_keyword) {
    $query->where('first_name', 'LIKE', '%', .$search_keyword. '%')->orWhere('last_name', 'LIKE', '%', .$search_keyword. '%');
}])
    ->where('title', 'LIKE', '%' . $search_keyword . '%')
    ->latest()
    ->paginate(10);

Level 67

@yansusanto Ugg...again...I was basing it off of what you said. In your first post, you said

How do I search for {{ $post->user->name }}?

This lead me to believe that there is a name field for users..........

It's quite frustrating to not have all of the info, or accurate info, when people request help. And learning little bits as we go along. Had all of the information been provided, and an accurate description of what you were needing, this thread would be like 2-3 posts long and been solved like 11 hours ago. I've put enough time into this one... time for some bourbon lol

1 like

Level 3

@Cronix

I'm really sorry for all the confusion. It was a $post->user->name initially which works perfectly fine. Thanks to you.

Just that on another list of records I have $post->user->fullname that's why I re-ask the question.

Anyway, it is not really important to search by fullname as I could click the link and show all the post by that username. I'm just trying to figure out possibilities.

And yes, you have been very helpful here and yes, you deserve some bourbon.

Once again, thank you, my friend.

Level 3

@sujancse

I did that exactly and I'm getting the following error Trying to get property 'fullname' of non-object.

Here's what I did

        $posts = Blog::with(['user' => function($query) use ($term) {
            $query->where('first_name', $term)->orWhere('last_name', $term); }])
                    ->orWhere('title', 'LIKE', '%' . $term . '%')
                    ->latest()
                    ->paginate(10);