CODEWITHSUNDEEP
laravellaravel-query-builder
Laravel Oscar
Laravel Oscar

Reputation: 35

How to count two related tables using Laravel Query Builder?

I have two tables in my database

My first table

enter image description here

And my second table

enter image description here

I would like to count how many gold members exists and how many silver members exists... I want to do a single count for each category... The rif can be repeated in the column but is the key to join the two tables.. I'm working with Query Builder and I would like to continue work with that. Someone can help me?

I tried with this code but didn't work

$count = DB::table('table1')
    ->join('table2', 'table1.rif', '=', 'table2.rif')
    ->select(DB::raw('category')
    ->count();

Upvotes: 0

Views: 1880

Answers (1)

Hafez Divandari
Hafez Divandari

Reputation: 9049

Try this:

use Illuminate\Support\Facades\DB;

DB::table('table2')
    ->join('table1', 'table2.rif', '=', 'table1.rif')
    ->select(DB::raw('count(*) as count'), 'table1.category as category')
    ->groupBy('category')
    ->get();

If you want to count only a specific category:

DB::table('table2')
    ->join('table1', 'table2.rif', '=', 'table1.rif')
    ->where('table1.category', 'Silver')
    ->count()

See Laravel docs for more info.

Upvotes: 1

Related Questions