Laravel 5.2在Eloquent中使用子查询

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;

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 ;