Getting Conditional Count in Join with Laravel Query Builder

Question

I am trying to achieve the following with Laravel Query builder.

I have a table called deals . Below is the basic schema

id
deal_id
merchant_id
status
deal_text
timestamps

I also have another table called merchants whose schema is

id
merchant_id
merchant_name
about
timestamps

Currently I am getting deals using the following query

$deals = DB::table('deals')
        -> join ('merchants', 'deals.merchant_id', '=', 'merchants.merchant_id')
        -> where ('merchant_url_text', $merchant_url_text)
        -> get();

Since only 1 merchant is associated with a deal, I am getting deals and related merchant info with the query.

Now I have a 3rd table called tbl_deal_votes. Its schema looks like

id
deal_id
vote (1 if voted up, 0 if voted down)
timestamps

What I want to do is join this 3rd table (on deal_id) to my existing query and be able to also get the upvotes and down votes each deal has received.

Answer 1

To do this in a single query you'll probably need to use SQL subqueries, which doesn't seem to have good fluent query support in Laravel 4/5. Since you're not using Eloquent objects, the raw SQL is probably easiest to read. (Note the below example ignores your deals.deal_id and merchants.merchant_id columns, which can likely be dropped. Instead it just uses your deals.id and merchants.id fields by convention.)

$deals = DB::select(
    DB::raw('
        SELECT
            deals.id AS deal_id,
            deals.status,
            deals.deal_text,
            merchants.id AS merchant_id,
            merchants.merchant_name,
            merchants.about,
            COALESCE(tbl_upvotes.upvotes_count, 0) AS upvotes_count,
            COALESCE(tbl_downvotes.downvotes_count, 0) AS downvotes_count
        FROM
            deals
        JOIN merchants ON (merchants.id = deals.merchant_id)
        LEFT JOIN (
            SELECT deal_id, count(*) AS upvotes_count
            FROM tbl_deal_votes
            WHERE vote = 1 && deal_id
            GROUP BY deal_id
        ) tbl_upvotes ON (tbl_upvotes.deal_id = deals.id)
        LEFT JOIN (
            SELECT deal_id, count(*) AS downvotes_count
            FROM tbl_deal_votes
            WHERE vote = 0
            GROUP BY deal_id
        ) tbl_downvotes ON (tbl_downvotes.deal_id = deals.id)
    ')
);

If you'd prefer to use fluent, this should work:

$upvotes_subquery = '
    SELECT deal_id, count(*) AS upvotes_count
    FROM tbl_deal_votes
    WHERE vote = 1
    GROUP BY deal_id';

$downvotes_subquery = '
    SELECT deal_id, count(*) AS downvotes_count
    FROM tbl_deal_votes
    WHERE vote = 0
    GROUP BY deal_id';

$deals = DB::table('deals')
    ->select([
        DB::raw('deals.id AS deal_id'),
        'deals.status',
        'deals.deal_text',
        DB::raw('merchants.id AS merchant_id'),
        'merchants.merchant_name',
        'merchants.about',
        DB::raw('COALESCE(tbl_upvotes.upvotes_count, 0) AS upvotes_count'),
        DB::raw('COALESCE(tbl_downvotes.downvotes_count, 0) AS downvotes_count')
    ])
    ->join('merchants', 'merchants.id', '=', 'deals.merchant_id')
    ->leftJoin(DB::raw('(' . $upvotes_subquery . ') tbl_upvotes'), function($join) {
        $join->on('tbl_upvotes.deal_id', '=', 'deals.id');
    })
    ->leftJoin(DB::raw('(' . $downvotes_subquery . ') tbl_downvotes'), function($join) {
        $join->on('tbl_downvotes.deal_id', '=', 'deals.id');
    })
    ->get();

A few notes about the fluent query:

1. Used the DB::raw() method to rename a few selected columns. Otherwise, there would have been a conflict between deals.id and merchants.id in the results.
2. Used COALESCE to default null votes to 0.
3. Split the subqueries into separate PHP strings to improve readability.
4. Used left joins for the subqueries so deals with no upvotes/downvotes still show up.