How to change SQL statement to laravel query

Question

i have my sql query and i want to change into laravel Query

this is sql

SELECT 
products.id, 
 products.name, 
(select ifnull(sum(stocks.qty),0) from stocks where stocks.pid=products.id and DATE(stocks.created_at) 
= CURDATE()) as stock_in_today,

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

(select ifnull(sum(stocks.qty),0) from stocks where stocks.pid=products.id) as total_stock_till_date,

 (select ifnull(sum(loadings.qty),0) from loadings where loadings.pid=products.id) as 
total_loadings_till_date,

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

((select ifnull(sum(stocks.qty),0) from stocks where stocks.pid=products.id)-(select 
ifnull(sum(loadings.qty),0) from loadings where loadings.pid=products.id)) as closing_balance

  from products

someone can help on this

The way you currently word your question it might look like you expect other people to do you job. It would be better to reduce the SQL statement to the parts that give you trouble and indicate what you already tried to convert it. — roskakori
– roskakori, Commented Apr 9, 2020 at 11:07

Ohgodwhy · Accepted Answer · 2020-04-09 09:35:38Z

You can simply use the DB facade:

DB::select(DB::raw("SELECT 
products.id, 
 products.name, 
(select ifnull(sum(stocks.qty),0) from stocks where stocks.pid=products.id and DATE(stocks.created_at) 
= CURDATE()) as stock_in_today,

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

(select ifnull(sum(stocks.qty),0) from stocks where stocks.pid=products.id) as total_stock_till_date,

 (select ifnull(sum(loadings.qty),0) from loadings where loadings.pid=products.id) as 
total_loadings_till_date,

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

((select ifnull(sum(stocks.qty),0) from stocks where stocks.pid=products.id)-(select 
ifnull(sum(loadings.qty),0) from loadings where loadings.pid=products.id)) as closing_balance

  from products"));

Collectives™ on Stack Overflow

How to change SQL statement to laravel query

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