Can't seem to translate the MySQL query to Laravel Query Builder

Question

I have been trying to translate the following MySQL query into Laravel Query Builder. Could anyone suggest how to get this working?

SELECT
orders.id AS order_id,
COUNT(products.id) AS count
FROM
order_product
LEFT JOIN orders ON orders.id = order_product.order_id
LEFT JOIN products ON order_product.product_id = products.id
WHERE
orders.user_id = 2
GROUP BY
orders.id

Here is my current code:

public static function getProductsCount($userId = null)
{
    if (!is_numeric($userId)) {
        return false;
    }
    DB::table('order_product')
        ->join('orders', 'orders.id', '=', 'order_product.order_id')
        ->join('products', 'order_product.product_id', '=', 'products.id')
        #->select('orders.id AS orders_id')
        ->where('orders.user_id', '=', $userId)
        ->distinct('products.id')
        ->groupBy('orders.id')
        ->count('products.id');
}

In contrast to the query I want to execute, I get the following:

select count(distinct `products`.`id`) as aggregate from `order_product` inner join `orders` on `orders`.`id` = `order_product`.`order_id` inner join `products` on `order_product`.`product_id` = `products`.`id` where `orders`.`user_id` = ? group by `orders`.`id`

Any ideas?

If all else fails, you can just use a raw query. Nothing states that you need to use the query builder. — Deniz Zoeteman
– Deniz Zoeteman, Commented Jan 17, 2015 at 17:48
True but I prefer to use Query Builder (for it's SQL Injection protection and because I want to understand). — atwright147
– atwright147, Commented Jan 17, 2015 at 18:02
you are using ->join instead of ->leftJoin in your illuminate query — baao
– baao, Commented Jan 17, 2015 at 18:03

Bogdan · Accepted Answer · 2015-01-17 20:57:18Z

1

The count method overrides the specified select columns temporarily, because it runs an aggregate function on the database. To avoid that you can just use the select as it is defined in your query. Also as @michael pointed out in the comment you should be using leftJoin instead of join. The following will generate the exact query you posted:

DB::table('order_product')
        ->leftJoin('orders', 'orders.id', '=', 'order_product.order_id')
        ->leftJoin('products', 'order_product.product_id', '=', 'products.id')
        ->select('orders.id AS orders_id', 'COUNT(products.id) AS count')
        ->where('orders.user_id', '=', $userId)
        ->groupBy('orders.id')
        ->get();

edited Jan 17, 2015 at 20:57

answered Jan 17, 2015 at 20:51

Bogdan

44.7k12 gold badges130 silver badges130 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Can't seem to translate the MySQL query to Laravel Query Builder

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related