CODEWITHSUNDEEP
phpmysqllaravel-4
StuBlackett
StuBlackett

Reputation: 3857

Complex Query in Laravel Query Builder

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

Upvotes: 2

Views: 516

Answers (1)

prava
prava

Reputation: 3986

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)

Upvotes: 2

Related Questions