shifoodew
2 years ago
2294
9
Laravel

SQL native to query builder

Posted 2 years ago by shifoodew

Hello, I need help on how to convert this SQL query with subquery inside inner join to Query builder. I'm new to this query builder. Hope you can help me convert this to query builder.

SELECT DISTINCT CONCAT(u.last_name, ", ", u.first_name ) AS full_name, ci.created_at AS date_purchase
FROM cart_items AS ci
LEFT JOIN products AS p ON ci.product_id = p.id
LEFT JOIN carts AS c ON ci.cart_id = c.id
LEFT JOIN `status` AS s ON ci.status_id = s.id

INNER JOIN users AS u ON c.user_id = u.id
INNER JOIN school_users AS su ON u.id = su.user_id

INNER JOIN
    (
    SELECT MAX(created_at) AS created_at1, cart_id
    FROM cart_items
    GROUP BY `cart_id`
    )p2 ON ci.cart_id = p2.cart_id AND ci.created_at = p2.created_at1

WHERE p.product_type_id = 1 AND p.service_sub_type_id = 2 AND su.school_id = 57 AND ci.status_id = 2 OR ci.status_id = 3
ORDER BY ci.created_at DESC

Please sign in or create an account to participate in this conversation.