CODEWITHSUNDEEP
laravel-4eloquent
Kordaat
Kordaat

Reputation: 53

Laravel 4: A where and whereIn inside a whereHas using Eloquent

Laravel 4 - advanced Where

I'm trying to retrieve Posts that have a $keyword like a certain Companion and besides that I want to retrieve the Posts the have a linking title or content as the $keyword. But when I try to use a where or whereIn inside a whereHas the query doesn't take these into account. When the state is 0 (not visible) or not inside the category 1 the Post item should not get selected.

$companion_id = Companion::where('name', 'LIKE', '%' . $keyword . '%' )->lists('id');

The code block below has to do two things:

  1. Search for Post items with a title or content like the $keyword
  2. and search for Post items that have Companions like the $keyword

code:

$results = Post::whereHas('companions', function($query) use($companion_id)
                {
                    $query->whereIn('companions.id', $companion_id)
                        ->where('state', '=', 1)
                        ->whereIn('category_id', array(1));
                })
                ->whereIn('category_id', array(1))
                ->orwhere('title', 'LIKE', '%' . $keyword . '%' )
                ->orWhere('content', 'LIKE', '%' . $keyword . '%' )
                ->where('state', '=', '1')
                ->orderBy('menu_order', 'desc')
                ->get();

The code above retrieves data succesfully except for the where and whereIn parts inside the whereHas.

Who can help me out?

Upvotes: 1

Views: 6287

Answers (2)

Kordaat
Kordaat

Reputation: 53

Thanks to Jarek Tkaczyk. His answer was almost correct. All I had to do was wrap the where inside a orWhere. Now I get the Posts that has a Companion like the $keyword and I get the Posts that has the $keyword inside the content or title.

$results = Post::whereHas('companions', function($query) use($companion_id)
                {
                    $query->whereIn('companions.id', $companion_id)
                    ->where('state', '=', '1');
                })
                ->orWhere( function($q) use ( $keyword ) {
                    $q->where('title', 'LIKE', '%' . $keyword . '%' )
                      ->orWhere('content', 'LIKE', '%' . $keyword . '%' );
                })
                ->whereIn('category_id', array(1, 3))
                ->where('state', '=', '1')
                ->orderBy('menu_order', 'desc')
                ->get();

Upvotes: 1

Jarek Tkaczyk
Jarek Tkaczyk

Reputation: 81167

  1. wrap your orWhere clauses in (..)
  2. you don't need where and whereIn in the whereHas closure, since it queries companions table
  3. you don't need whereIn for category_id, unless you want to pass multiple ids there

.

$results = Post::whereHas('companions', function($query) use($companion_id)
    {
        $query->whereIn('companions.id', $companion_id);
    })
    ->whereIn('category_id', array(1)) // why not where(..) ?
    ->where(function ($q) use ($keyword) {
      $q->where('title', 'LIKE', '%' . $keyword . '%' )
        ->orWhere('content', 'LIKE', '%' . $keyword . '%' );
    })
    ->where('state', '=', '1')
    ->orderBy('menu_order', 'desc')
    ->get();

Upvotes: 2

Related Questions