Support the ongoing development of Laravel.io →
Database Eloquent
Last updated 1 year ago.
0

First time through I missed this post...

http://laravel.io/forum/03-05-2014-search-through-relationships

which referenced this solution on Laracasts.com...

https://laracasts.com/forum/1085-search-through-relationship

So here is where I am now...

$keyword = Input::get('keyword');

$lessons = Lesson::whereHas('tags', function($query) use ($keyword)
{
    $query->where('name', 'LIKE', "%$keyword%");
})->orWhere('short_desc', 'LIKE', "%$keyword%")
  ->orWhere('long_desc', 'LIKE', "%$keyword%")
  ->with('tags')
  ->get();

(sorry for the funky formatting. It seems the code block doesn't like double quotes like this)

This returns all lessons where the short_desc, long_desc, and tag.name contain $keyword. But how would I further constrain this? eg. lessons.status = 'published'. I've tried placing `->where('status', 'published') at various points in the query to no avail.

Thanks again for any assistance.

Last updated 1 year ago.
0

I haven't made any progress on this. Anyone have an idea?

Last updated 1 year ago.
0

whereStatus('published') or where('status', '=', 'published')

you forgot the comparison operator '='.

Last updated 1 year ago.
0

The operator is not required when the operator is '='. The query above returns results, but the nested query on tags returns all lessons regardless of status, even when I include where('status', 'published'). The challenge is how to only return published results.

Thanks.

Last updated 1 year ago.
0
<?php

$keyword = Input::get('keyword');

$lessons = Lesson::with('tags')->where('status', '=', 'published')
				->whereHas('tags', function($query) use ($keyword) {
    				$query->where('name', 'LIKE', "%$keyword%");
				})
				->orWhere('short_desc', 'LIKE', "%$keyword%")
  				->orWhere('long_desc', 'LIKE', "%$keyword%")
  				->get();
Last updated 1 year ago.
0

Hi zenry,

Thanks for this, and it is something I tried. Unfortunately, the 'published' constraint is not applied to the nested query. It returns ALL lessons that have a tag with the keyword in it. Is there a way to constrain the nested query as well?

Thanks!

Last updated 1 year ago.
0

Throwing this back out there. I've been unable to find a solution. Thanks in advance.

Last updated 1 year ago.
0

Eager loading won't work here as it runs 2 separate queries, so you need simple joins like this:

Lesson::join('lesson_tag', 'lessons.id','=','lesson_tag.lesson_id')
	->join('tags','tags.id','=','lesson_tag.tag_id')
	->where('lessons.short_desc,'like',"%$keyword%")
	->orWhere('lessons.long_desc','like',"%$keyword%")
	->orWhere('tags.name','like',"%$keyword%")
	// if you need to retrieve all the tags for each lesson too then uncomment this:
	// ->with('tags')
	->distinct()
	->get(['lessons.*']);

This way you load Collection of Lesson models that match your search (or with related tags).

Last updated 1 year ago.
0

You are my hero, sir. Thank you!!! So how would I further refine this to return only lessons with a status of 'published'? status is a column in the lessons table. So far I've tried...

Lesson::whereStatus('published')
	->join('lesson_tag', 'lessons.id','=','lesson_tag.lesson_id')
        ->join('tags','tags.id','=','lesson_tag.tag_id')
        ->where('lessons.short_desc','like',"%$keyword%")
        ->orWhere('lessons.long_desc','like',"%$keyword%")
        ->orWhere('tags.name','like',"%$keyword%")
        ->with('tags')
        ->distinct()
        ->get(['lessons.*']);
Lesson::join('lesson_tag', 'lessons.id','=','lesson_tag.lesson_id')
        ->join('tags','tags.id','=','lesson_tag.tag_id')
        ->where('lessons.short_desc','like',"%$keyword%")
        ->orWhere('lessons.long_desc','like',"%$keyword%")
        ->orWhere('tags.name','like',"%$keyword%")
        ->where('lessons.status', 'published')
        ->with('tags')
        ->distinct()
        ->get(['lessons.*']);
Lesson::join('lesson_tag', 'lessons.id','=','lesson_tag.lesson_id')
        ->join('tags','tags.id','=','lesson_tag.tag_id')
        ->where('lessons.short_desc','like',"%$keyword%")
        ->orWhere('lessons.long_desc','like',"%$keyword%")
        ->orWhere('tags.name','like',"%$keyword%")
        ->where(function($q) {
    	      $q->where('lessons.status', 'published');
    	    )}
        ->with('tags')
        ->distinct()
        ->get(['lessons.*']);

It seems the 'status' constraint is ignored in the results, or rather not being applied in the join.

Thanks for your assistance.

Last updated 1 year ago.
0

edit: Well I must be tired as it's midnight where I live... Use having() as it is what you want (BUT less efficient than the 2nd solution):

Lesson::join('lesson_tag', 'lessons.id','=','lesson_tag.lesson_id')
    ->join('tags','tags.id','=','lesson_tag.tag_id')
    ->where('lessons.short_desc','like',"%$keyword%")
    ->orWhere('lessons.long_desc','like',"%$keyword%")
    ->orWhere('tags.name','like',"%$keyword%")
    ->having('status','=','published')
    ->with('tags')
    ->distinct()
    ->get(['lessons.*']);

Straightforward solution is to make pairs where status=published AND desc like XXX or status=published AND ... Obviously repeating whereStatus('published') is not the best way to do this, so I suggest you use a scope for this:

// Lesson model
public function scopePublished($query)
{
   $query->whereStatus('published');
}

Then your query would look like this:

Lesson::join('lesson_tag', 'lessons.id','=','lesson_tag.lesson_id')
    ->join('tags','tags.id','=','lesson_tag.tag_id')
    ->where('lessons.short_desc','like',"%$keyword%")
    ->published()
    ->orWhere('lessons.long_desc','like',"%$keyword%")
    ->published()
    ->orWhere('tags.name','like',"%$keyword%")
    ->published()
    ->with('tags')
    ->distinct()
    ->get(['lessons.*']);

Still this is a bit cumbersome, so maybe you'd like to extract it by creating a view in your db returning only published lessons etc.

Last updated 1 year ago.
0

2nd solution worked brilliantly. I'm ok with cumbersome at this point. I'll work on refine it down the road. Thanks!!

Last updated 1 year ago.
0

Digging a little deeper on this. Right now we are returning results based on 3 constraints against lessons.short_desc, lessons.long_desc, and tags.name. If the user were to click on a tag name to further filter the results, how could you return the search results further constrained by the tag id? I tried further constraining scopePublished() to...

public function scopeTagPublished($query, $tag)
{
	$query->with(['tags' => function($q) use ($tag)
			{
				$q->whereId($tag);
			}])
			->whereStatus('published');
}

and I have replaced all instances of published() with tagPublished()...

public function scopeTagSearch($query, $search, $tag)
{
	return $query->join('lesson_tag', 'lessons.id','=','lesson_tag.lesson_id')
	    		 ->join('tags','tags.id','=','lesson_tag.tag_id')
	    		 ->where('lessons.short_desc','like',"%$search%")
	    		 ->tagPublished($tag)
	    		 ->orWhere('lessons.long_desc','like',"%$search%")
	    		 ->tagPublished($tag)
	    		 ->orWhere('tags.name','like',"%$search%")
	    		 ->tagPublished($tag)
	    		 ->with('tags')
		    	 ->distinct();
}

But this is still returning results from other tag id's. I am probably way over-complicating things. Any thoughts?

Last updated 1 year ago.
0

This is trivial unless you want something different to what you say :)

// tag id given so we need only lessons with desc like ... and where tag id = $tagId

return $query->join('lesson_tag', 'lessons.id','=','lesson_tag.lesson_id')
                 ->join('tags','tags.id','=','lesson_tag.tag_id')
                 ->where('lessons.short_desc','like',"%$search%")
                 ->published()
                 ->orWhere('lessons.long_desc','like',"%$search%")
                 ->published()
                 ->where('tags.id','=',$tagId)
                 ->with('tags')
                 ->distinct();
Last updated 1 year ago.
0

This was still returning lessons where short and long desc were like $search, regardless of tag id. So I am filtering the results on tag id...

$filtered = $lessons->filter(function($lesson) use ($tag)
			{
				foreach($lesson->tags as $item) {
					if ($item->id == $tag) return true;
				}
			});

This works but we'll have to see how it scales as the result set grows over time. Shouldn't be too much of a peformance issue.

Last updated 1 year ago.
0

Sign in to participate in this thread!

Eventy

Your banner here too?

Moderators

We'd like to thank these amazing companies for supporting us

Your logo here?

Laravel.io

The Laravel portal for problem solving, knowledge sharing and community building.

© 2024 Laravel.io - All rights reserved.