Complex Query in Laravel Query Builder

Question

I've made a Search function within my site. However my old query was bringing in deleted_at columns, Despite them being null.

So I've written the query in raw MySQL and am getting the results I expect back.

However, I am struggling to actually write this using Laravel's Query Builder. The working MySQL Query I'd like is :

select * from `packs` 
left join `keywords` 
on `keywords`.`pack_id` = `packs`.`pack_id` 
inner join `categories` 
on `categories`.`category_id` = `packs`.`primary_category_id` 
left join `ratings` 
on `ratings`.`pack_id` = `packs`.`pack_id` 
where (`pack_title` LIKE '%sams%' 
or `keywords`.`keyword_title` LIKE '%sams%')
and `packs`.deleted_at is null
group by `pack_title` 
order by `packs`.`created_at` 
desc

My current attempt using Laravel looks as so :

// Explode Terms
            $terms = explode(' ', $q);

            // Produce Query (Initially)
            $query = DB::table('packs')
                       ->leftJoin('keywords', 'keywords.pack_id', '=', 'packs.pack_id')
                       ->leftJoin('categories', 'categories.category_id', '=', 'packs.primary_category_id')
                       ->whereNotNull('packs.deleted_at')
                       ->leftJoin('ratings', 'ratings.pack_id', '=', 'packs.pack_id');

            // Loop through each term
            foreach($terms as $term)
            {
                $query->where('pack_title', 'LIKE', '%'. $term . '%')
                        ->orWhere(function($query, $term)
                        {
                            $query->orWhere('pack_description', 'LIKE', '%'. $term . '%')
                                ->orWhere('keywords.keyword_title', 'LIKE', '%'. $term . '%');
                        })
                        ->whereNotNull('packs.deleted_at')
                        ->groupBy('pack_title')
                        ->orderBy('packs.created_at', 'DESC');

            }

            // Log
            Log::info('User Searched using term : '.$q.'');

            $results = $query->get();

This is producing the error :

Missing argument 2 for SearchesController::{closure}()

Is this possible to write in Query Builder, If so how. I don't mind exploring writing it as a RAW Query if needs be.

Thanks

Try function($query)use($term) instead of function($query, $term) — Narendrasingh Sisodia
– Narendrasingh Sisodia, Commented Nov 26, 2015 at 8:57

prava · Accepted Answer · 2015-11-26 09:04:49Z

2

Try this:

$terms = explode(' ', $q);

// Produce Query (Initially)
$query = DB::table('packs')
        ->leftJoin('keywords', 'keywords.pack_id', '=', 'packs.pack_id')
        ->leftJoin('categories', 'categories.category_id', '=', 'packs.primary_category_id')
        ->whereNotNull('packs.deleted_at')
        ->leftJoin('ratings', 'ratings.pack_id', '=', 'packs.pack_id');

// Loop through each term
foreach($terms as $term)
{
    $query->where('pack_title', 'LIKE', '%'. $term . '%')
          ->orWhere(function($query) use ($term)
    {
        $query->orWhere('pack_description', 'LIKE', '%'. $term . '%')
              ->orWhere('keywords.keyword_title', 'LIKE', '%'. $term . '%');
    })
    ->whereNotNull('packs.deleted_at')
    ->groupBy('pack_title')
    ->orderBy('packs.created_at', 'DESC');
}

// Log
Log::info('User Searched using term : '.$q.'');
$results = $query->get();

Note: I made the change over here ->orWhere(function($query) use ($term)

answered Nov 26, 2015 at 9:04

prava

3,9762 gold badges26 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

StuBlackett Over a year ago

That's great. Thanks for that prava. I've changed whereNotNull to whereNull and moved the orWhere's into the brackets.

Collectives™ on Stack Overflow

Complex Query in Laravel Query Builder

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related