shifoodew's avatar

SQL native to query builder

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
0 likes
14 replies
Cronix's avatar
Cronix
7 years ago
Best Answer
Level 67

I wouldn't bother. It will take a lot longer to come up with something that works, you'd be using a lot of RAW statements anyway since the query builder doesn't have a lot of that built in, and it would be a lot more code. I'd just use straight queries for the complex stuff with pdo bindings (always use bindings).

$bindings = [
    'product_type_id' => 1,
    'service_sub_type_id' => 2,
    'school_id' => 57,
    'status_id1' => 2,
    'status_id2' => 3
];

$users = DB::select('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 = :product_type_id AND p.service_sub_type_id = :service_sub_type_id AND su.school_id = :school_id AND ci.status_id = :status_id1 OR ci.status_id = :status_id2
ORDER BY ci.created_at DESC', $bindings);
1 like
komangbp's avatar

@Cronix

How convert codeigniter to laravel

public function get_pos($userID) { $this->db->where("USERID", $userID); $query = $this->db->get("WEB_POS"); $result = $query->result(); $pos = array(); foreach ($result as $key) { array_push($pos, trim($key->POSID)); } return $pos; }

Cronix's avatar

You're welcome! If your question is answered, please mark the post as solved.

melx's avatar

i have the same, problems, i want to change sql query to laravel

select products.id, products.name, ((select ifnull(sum(stocks.qty),0) from stocks where stocks.pid=products.id and DATE(stocks.created_at) = CURDATE())+ (select ifnull(sum(stocks.qty),0) from stocks where stocks.pid=products.id and DATE(stocks.created_at) = CURDATE()-1)) as total_stock , (select ifnull(sum(loadings.qty),0) from loadings where loadings.pid=products.id and DATE(loadings.created_at) = CURDATE()) as total_loadings,

((select ifnull(sum(stocks.qty),0) from stocks where stocks.pid=products.id and DATE(stocks.created_at) = CURDATE()-1) + (select ifnull(sum(loadings.qty),0) from loadings where loadings.pid=products.id and DATE(loadings.created_at) = CURDATE())) as close_balance,

((select ifnull(sum(stocks.qty),0) from stocks where stocks.pid=products.id and DATE(stocks.created_at) = CURDATE()-1) - (select ifnull(sum(loadings.qty),0) from loadings where loadings.pid=products.id and DATE(loadings.created_at) = CURDATE()-1)) as open_balance from products

Tray2's avatar

@emfinanga Don't reopen old solved threads. Create a new thread instead. Also you should wrap your code in three backticks to make it more readable.

//Code goes here
melx's avatar

@tray2

this is noted, but kindly can you assist me on this

i have my question there

Please or to participate in this conversation.