jeudi 1 août 2019

Laravel: Adding multiple count results of sub queries

I wanted to add the count result of more than one queries that belong to different tables. I am using the below problem as a reference to my actual problem because this problem has already a solution (How do I add two count(*) results together on two different tables?) but I am facing problem in implementing the solution in laravel.

I have two tables: Toys and Games.

+--------------------+------------------+
| Field              | Type             |
+--------------------+------------------+
| toy_id             | int(10) unsigned |
| little_kid_id      | int(10) unsigned |
+--------------------+------------------+

+--------------------+------------------+
| Field              | Type             |
+--------------------+------------------+
| game_id            | int(10) unsigned |
| little_kid_id      | int(10) unsigned |
+--------------------+------------------+

A little kid can have multiple toys. A little kid can be participating in multiple games at once.

I want a query that will give me the total number of toys + games that a little_kid is involved with.

Basically, I want the sum of these two queries:

SELECT COUNT(*) FROM Toys WHERE little_kid_id = 900;
SELECT COUNT(*) from Games WHERE little_kid_id = 900 

The above problem has the following accepted answer

SELECT
(SELECT COUNT(*) FROM Toys WHERE little_kid_id = 900)+
(SELECT COUNT(*) from Games WHERE little_kid_id = 900)
AS SumCount

I wanted to implement the above solution in Laravel. I have tried the following method but to no avail. It gives syntax error.

$sub=DB::tabel('toys')->select(DB::raw('count(*) as total'))
->where('little_kid_id',$id);

$sub1=DB::tabel('games')->select(DB::raw('count(*) as total'))
->where('little_kid_id',$id);

$result = DB::table( DB::raw("( ({$sub->toSql()})+({$sub1->toSql()}) )  as 
total_count") )
->mergeBindings($sub)
->mergeBindings($sub1)
->select('total_count')
->get();

In short I wanted to perform the accepted solution of that problem (How do I add two count(*) results together on two different tables?) in laravel.



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire