Getting Conditional Count in Join with Laravel Query Builder

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.