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.

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

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

you forgot the comparison operator '='.

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.

<?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();

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!

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

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).

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.

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

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?

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();

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.