Combine two queries into one query Laravel

Question

I need to combine two queries into one so i can call it one time on my foreach inside a table in my blade view. I tried using merge() but it displays two separate queries. CountofPatents are all the patents that were submitted by a user and CountofApprovedPatents are the patents which has the status of "2". Here's how it looks now:

array 0 [person_id : 1
CountofApprovedPatents: 4
status: 2]

array 1 [person_id : 2
CountofApprovedPatents: 2
status: 2]

array 2 [person_id : 1
CountofPatents: 5]

array 3 [person_id : 2
CountofPatents: 4]

Here's how it's supposed to look like

array 0 [person_id : 1
CountofApprovedPatents: 4
CountofPatents: 5]

array 1 [person_id : 2
CountofApprovedPatents: 2
CountofPatents: 4]

Here's my code so far

AdminController

$start = $request->input('start');
$end = $request->input('end');
$approvedPatents = DB::table('patents')
->select('person_id', DB::raw('count(*) as CountofApprovedPatents'))
->where([
    ['status', '=', 2],
    ['date_approved', '>=', $start],
    ['date_approved', '<=', $end],
])
->groupBy('person_id')
->orderBy('status', 'desc')
->get();

$totalPatents = DB::table('patents')
->select('person_id', DB::raw('count(*) as CountofPatents'))
->where([
    ['date_approved', '>=', $start],
    ['date_approved', '<=', $end],
])
->groupBy('person_id')
->orderBy('status', 'desc')
->get();  

$patents = $approvedPatents->merge($totalPatents);
$results = $patents->all();  
return view('admin.patents.showPatents', compact('results', 
'start', 'end'));

Is it possible to combine these two queries?

$results = DB::table('patents')
    ->union($approvedPatents)
    ->union($totalPatents)
    ->get();

Doesn't your first query have 3 columns ? Where did status go? — mighty_dev
– mighty_dev, Commented Mar 11, 2019 at 12:34
If you join these 2 queries, should status = 2 apply to all rows? — mighty_dev
– mighty_dev, Commented Mar 11, 2019 at 12:41
No it only applies with the CountofApprovedPatents , because the other query disregards the status value. CountofPatents counts all associated with the person_id whether the status is 1 or 2. — Andy Stone
– Andy Stone, Commented Mar 11, 2019 at 12:55
So how would expected output look like after joining them since status = 2 doesn't apply to all? — mighty_dev
– mighty_dev, Commented Mar 11, 2019 at 12:57

Tim Rowley · Accepted Answer · 2019-03-11 13:49:35Z

2

What I think you are looking for is Unions.

It'll allow you to specify a query and then join it with the final query.

Eg.

$first = DB::table('users')
            ->whereNull('first_name');

$users = DB::table('users')
            ->whereNull('last_name')
            ->union($first)
            ->get();

Try:

$approvedPatents = DB::table('patents')
->select('person_id', DB::raw('count(*) as CountofApprovedPatents, status'))
->where([
    ['status', '=', 2],
    ['date_approved', '>=', $start],
    ['date_approved', '<=', $end],
])
->groupBy('person_id')
->orderBy('status', 'desc')


$totalPatents = DB::table('patents')
->select('person_id', DB::raw('count(*) as CountofPatents'))
->where([
    ['date_approved', '>=', $start],
    ['date_approved', '<=', $end],
])
->groupBy('person_id')
->orderBy('status', 'desc')
->union($approvedPatents)
->get();

Otherwise you could be looking for Joins - worth a look at the docs :)

Unions: https://laravel.com/docs/5.8/queries#unions

Joins: https://laravel.com/docs/5.8/queries#joins

answered Mar 11, 2019 at 13:49

Tim Rowley

4407 silver badges17 bronze badges

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

1 Comment

Andy Stone Over a year ago

Thank you for the reply. I tried the above code, but I'm only getting the CountofPatents. It didn't include CountofApprovedPatents in the query.

mighty_dev · Accepted Answer · 2019-03-11 14:25:28Z

0

You can use MySQL's IF condition in your raw selection of columns.

$results = DB::table('patents')
->select('person_id', 
          DB::raw('sum(IF(status = 2,1,0)) as CountofApprovedPatents'),
          DB::raw('count(*) as CountofPatents')
->where([
    ['date_approved', '>=', $start],
    ['date_approved', '<=', $end],
])
->groupBy('person_id')
->orderBy('status', 'desc')
->get();

The above IF condition in sum() adds 1 to the result if current row's status is 2, else it adds 0 which effectively doesn't break the sum.

edited Mar 11, 2019 at 14:25

answered Mar 11, 2019 at 14:08

mighty_dev

17.8k3 gold badges24 silver badges39 bronze badges

4 Comments

Andy Stone Over a year ago

It already fetches both of CountofApprovedPatents and CountofPatents in one query. But both of them have the same count(). CountofApprovedPatents should have a lower value, i tried editing my database values to change some of the status as well.

mighty_dev Over a year ago

@AndyStone My bad. It's actually sum() instead of count(). I fixed it.

Andy Stone Over a year ago

Thank you thank you very much! It's working as intended. I can't thank you enough. Already accepted it as the answer. Have a good day @vivek_23

mighty_dev Over a year ago

@AndyStone Welcome to SO. Glad to help :)

Collectives™ on Stack Overflow

Combine two queries into one query Laravel

2 Answers 2

1 Comment

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related