Laravel Query Builder MySQL - Count /w Group By

Question

Really struggling with the query and look around I can't find the answer that I require!

I have 4 tables, offers, users, savedoffers and usedoffers. savedoffers/usedoffers tables are relational tables between users & offers.

I want to return a list of offer then left join usedoffers and savedoffers. Then group them by the offer (say offer id) and then count how many users have saved the offers and how many have used them.

After all my efforts I am here...

$offers = DB::table('offers')
            ->select('offers.id as id', 
                     'offers.code', 
                     'offers.title', 
                     'offers.date', 
                      DB::raw('count(usedoffers.id) as used_count'),
                      DB::raw('count(savedoffers.id) as saved_count'))
            ->leftjoin('savedoffers', 'offers.id', '=', 'savedoffers.offer_id')
            ->leftjoin('usedoffers', 'offers.id', '=', 'usedoffers.offer_id')
            ->orderBy('offers.date', 'asc')
            ->groupBy('offers.id')
            ->get();

This still doesn't work as it seems to count both :/

Much help is appreciated as feel like im banging my head against a wall!

Answer 1

General impatience I have answered my own question..

I needed to count the distinct IDs of the used and saved which gave me the output I was looking for...

So as below...

$offers = DB::table('offers')
                    ->select('offers.id as id', 
                             'offers.code', 
                             'offers.title', 
                             'offers.date', 
                              DB::raw('count(DISTINCT usedoffers.id) as used_count'),
                              DB::raw('count(DISTINCT savedoffers.id) as saved_count'))
                    ->leftjoin('savedoffers', 'offers.id', '=', 'savedoffers.offer_id')
                    ->leftjoin('usedoffers', 'offers.id', '=', 'usedoffers.offer_id')
                    ->where('company_id', '=', User::find(\Auth::user()->id)->company_id)
                    ->orderBy('offers.date', 'asc')
                    ->groupBy('offers.id')
                    ->get();

Was difficult trying to work it out like this, so ended up getting it correct just with MySQL.

I was trying to output the statement with var_dump(DB::getQueryLog()); and it wasn't working. this is because in Laravel you need to initiate the Querylog before the query by adding the following statement DB::enableQueryLog();

Hope this help someone else out too!