Laravel: Adding multiple count results of sub queries

Question

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.total')
->get();

I have also tried this method. It works but gives collection but I need an integer value of total count

$result=$sub->unionAll($sub1);
dd($result->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.

Answer 1

You can use those codes :

$q = DB::tabel('toys')->select('toy_id','little_kid_id')->where('little_kid_id',900);

    $q1 = DB::tabel('games')->select('game_id','little_kid_id')->where('little_kid_id',900)

  $data = $q->union($q1)->count();

Don't forget Union require the tables must have the same columns so that I select the columns if your columns will not match each other then don't touch the select statement otherwise feel free to remove the codes

The second way is :

DB::table(DB::raw('(SELECT COUNT(*) as c FROM Toys WHERE little_kid_id = 900) t,(SELECT COUNT(*) as c1 from Games WHERE

little_kid_id = 900) t2'))->selectRaw('t.c+t2.c1 as SumCount')->toSql(); //change toSql() to get() if you want to get datas instead of sql code

You can try this. for more information look at https://laravel.com/docs/5.8/queries#raw-expressions. if you want to get request parameters by any user then be confirmed to prevent SQL Injection