Laravel 5.2 using sub queries in Eloquent

Question

Hope you're all doin well, I wanted to post a question to find out what the best approach would be to convert the following SQL to Eloquent using count and sub queries:

SELECT pkgCnt.holiday_type_desc, COUNT(DISTINCT pkgCnt.package_heading) AS count
FROM package pkg,
(
    SELECT holiday_type_desc, package_heading
    FROM package pkg
    INNER JOIN holiday_type ht ON pkg.holiday_type_id = ht.holiday_type_id
    GROUP BY pkg.holiday_type_id, package_heading
) pkgCnt
WHERE pkg.package_heading = pkgCnt.package_heading
GROUP BY pkg.holiday_type_id;

and

SELECT country_name, 0
FROM country
WHERE country_id NOT IN (
    SELECT country_id
    FROM package
)
UNION ALL
SELECT pkgCnt.country_name, COUNT(DISTINCT pkgCnt.package_heading) AS count
FROM package pkg,
(
    SELECT country_name, package_heading
    FROM package pkg
    LEFT JOIN country cnty ON pkg.country_id = cnty.country_id
    GROUP BY pkg.country_id, package_heading
) pkgCnt
WHERE pkg.package_heading = pkgCnt.package_heading
GROUP BY pkg.country_id;

I've managed to get the 1st query working using Eloquent and DB::raw, just working on the 2nd query now. — Tim Kruger, Mar 23 '16 at 11:50
Have you looked into [mergeBindings](http://stackoverflow.com/a/24838367/2970321)? You may be able to define your subquery using `raw`, then still be able to use the query builder for the rest of your query after calling `mergeBindings`. — alexw, Mar 23 '16 at 17:59
@alexw I haven't looked into mergeBindings but in the end I decided to go the stored procedure route as I feel that will probably be more efficient. Just taking a look at mergeBindings now and that would have probably been the way I would have gone if I used Eloquent, so thanks for your comment. — Tim Kruger, Mar 25 '16 at 05:06

score 0 · Answer 1 · answered Mar 25 '16 at 05:12

In the end I decided to go the stored procedure route rather than use Eloquent and raw, so here is my final working code:

Packages and Specials model

public function count($is_holiday_type = false) {
    $result = DB::select("CALL package_count(?)", array($is_holiday_type));

    return $result;
}

MySQL stored procedure:

DELIMITER //
CREATE PROCEDURE package_count (IN is_holiday_type TINYINT(1))
BEGIN
    IF is_holiday_type = 1
    THEN
        SELECT pkgCnt.holiday_type_desc, COUNT(DISTINCT pkgCnt.package_heading) AS count
        FROM package pkg,
        (
            SELECT holiday_type_desc, package_heading
            FROM package pkg
            INNER JOIN holiday_type ht ON pkg.holiday_type_id = ht.holiday_type_id
            GROUP BY pkg.holiday_type_id, package_heading
        ) AS pkgCnt
        WHERE pkg.package_heading = pkgCnt.package_heading
        GROUP BY pkg.holiday_type_id
        ORDER BY pkg.holiday_type_id;
    ELSE 
        SELECT country_name, 0 AS count
        FROM country
        WHERE country_id NOT IN (
            SELECT country_id
            FROM package
        )
        UNION ALL
        SELECT pkgCnt.country_name, COUNT(DISTINCT pkgCnt.package_heading) AS count
        FROM package pkg,
        (
            SELECT country_name, package_heading
            FROM package pkg
            LEFT JOIN country cnty ON pkg.country_id = cnty.country_id
            GROUP BY pkg.country_id, package_heading
        ) AS pkgCnt
        WHERE pkg.package_heading = pkgCnt.package_heading
        GROUP BY pkg.country_id
        ORDER BY country_name;
    END IF;
END //
DELIMITER ;

Laravel 5.2 using sub queries in Eloquent

1 Answers1