Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.
how to write this query in 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()) 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 from products
Check out official docs: https://laravel.com/docs/5.8/queries
If you know that everything in the query is safe, you could use raw query by doing this: DB::raw('your query here'), but it probably is a good idea for you to modify the query and use query builder to get back eloquent models (check out the link for more on that)
A good example with binding parameters: https://laracasts.com/discuss/channels/laravel/sql-native-to-query-builder
@jlrdw i checked but not understand
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
It might looks something like this:
DB::table('products')
->select(['products.id', 'products.name'])
->addSelect([
'total_stock' => DB::table('stocks')
->selectRaw('sum(ifnull(`stocks`.`qty`, 0))')
->whereColumn('stocks.pid', '=', 'products.id')
->whereDate('stocks.created_at', '=', Carbon::today()),
'total_loadings' => DB::table('loadings')
->selectRaw('sum(ifnull(`loadings`.`qty`, 0))')
->where('loadings.pid', '=', 'products.id')
->whereDate('loadings.created_at', '=', Carbon::today())
])
->get();
i got this error
SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax;
check the manual that corresponds to your MariaDB server version for the right syntax to use near
'from `stocks` where `stocks`.`pid` = `products`.`id` and date(`stocks`.`created_' at line 1 (SQL:
select `products`.`id`, `products`.`name`, (select sum(ifnull(`stocks`.`qty`, 0) from `stocks` where
`stocks`.`pid` = `products`.`id` and date(`stocks`.`created_at`) = 2020-03-20) as `total_stock`, (select
sum(ifnull(`loadings`.`qty`, 0)) from `loadings` where `loadings`.`pid` = products.id and
date(`loadings`.`created_at`) = 2020-03-20) as `total_loadings` from `products`)
I was missing a ) in the first selectRaw, I've updated my reply, try it again.
@cliffordatcaveodotnl , perfect, thank you its works,
but i updated another code below need to change
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
With a query that complicated I would just execute the raw query or might consider a database view.
DB::select('
SELECT
`products`.`id`,
`products`.`name`,
(
SELECT SUM(IFNULL(`stocks`.`qty`, 0))
FROM `stocks`
WHERE `stocks`.`pid` = `products`.`id`
AND (
DATE(`stocks`.`created_at`) = CURDATE()
OR DATE(`stocks`.`created_at`) = CURDATE() - 1
)
) AS `total_stock`,
(
SELECT SUM(IFNULL(`loadings`.`qty`, 0))
FROM `loadings`
WHERE `loadings`.`pid` = `products`.`id`
AND DATE(`loadings`.`created_at`) = CURDATE()
) AS `total_loadings`,
(
(
SELECT SUM(IFNULL(`stocks`.`qty`, 0))
FROM `stocks`
WHERE `stocks`.`pid` = `products`.`id`
AND DATE(`stocks`.`created_at`) = CURDATE() - 1
)
+ (
SELECT SUM(IFNULL(`loadings`.`qty`, 0))
FROM `loadings`
WHERE `loadings`.`pid` = `products`.`id`
AND DATE(`loadings`.`created_at`) = CURDATE()
)
) AS `close_balance`,
(
(
SELECT SUM(IFNULL(`stocks`.`qty`, 0))
FROM `stocks`
WHERE `stocks`.`pid` = `products`.`id`
AND DATE(`stocks`.`created_at`) = CURDATE() - 1
)
- (
SELECT SUM(IFNULL(`loadings`.`qty`, 0))
FROM `loadings`
WHERE `loadings`.`pid` = `products`.`id`
AND DATE(`loadings`.`created_at`) = CURDATE() - 1
)
) AS `open_balance`
FROM `products`
');
how can i add get(); here?
You don't have to: https://laravel.com/docs/7.x/database#running-queries
thank you master it works fine, take cheers in emma4082@gmail.com
if you check the last line to calculate open_balance is not function i get null while there is data
There is also data on the right date? Maybe the CURDATE() - 1 is not correct, try:
DB::select('
SELECT
`products`.`id`,
`products`.`name`,
(
SELECT SUM(IFNULL(`stocks`.`qty`, 0))
FROM `stocks`
WHERE `stocks`.`pid` = `products`.`id`
AND (
DATE(`stocks`.`created_at`) = :today
OR DATE(`stocks`.`created_at`) = :yesterday
)
) AS `total_stock`,
(
SELECT SUM(IFNULL(`loadings`.`qty`, 0))
FROM `loadings`
WHERE `loadings`.`pid` = `products`.`id`
AND DATE(`loadings`.`created_at`) = :today
) AS `total_loadings`,
(
(
SELECT SUM(IFNULL(`stocks`.`qty`, 0))
FROM `stocks`
WHERE `stocks`.`pid` = `products`.`id`
AND DATE(`stocks`.`created_at`) = :yesterday
)
+ (
SELECT SUM(IFNULL(`loadings`.`qty`, 0))
FROM `loadings`
WHERE `loadings`.`pid` = `products`.`id`
AND DATE(`loadings`.`created_at`) = :today
)
) AS `close_balance`,
(
(
SELECT SUM(IFNULL(`stocks`.`qty`, 0))
FROM `stocks`
WHERE `stocks`.`pid` = `products`.`id`
AND DATE(`stocks`.`created_at`) = :yesterday
)
- (
SELECT SUM(IFNULL(`loadings`.`qty`, 0))
FROM `loadings`
WHERE `loadings`.`pid` = `products`.`id`
AND DATE(`loadings`.`created_at`) = :yesterday
)
) AS `open_balance`
FROM `products`
', ['today' => Carbon::today(), 'yesterday' => Carbon::yesterday()]);
can you recheck i got this error
SQLSTATE[HY093]: Invalid parameter number
This error means that you are trying to access more (or less) parameters than there are in your table. Check your tables, if they have all the columns that you are using in your query.
i have all columns
Problem is with the named parameters, even when they are named the count should still be the same...
DB::select('
SELECT
`products`.`id`,
`products`.`name`,
(
SELECT SUM(IFNULL(`stocks`.`qty`, 0))
FROM `stocks`
WHERE `stocks`.`pid` = `products`.`id`
AND (
DATE(`stocks`.`created_at`) = ?
OR DATE(`stocks`.`created_at`) = ?
)
) AS `total_stock`,
(
SELECT SUM(IFNULL(`loadings`.`qty`, 0))
FROM `loadings`
WHERE `loadings`.`pid` = `products`.`id`
AND DATE(`loadings`.`created_at`) = ?
) AS `total_loadings`,
(
(
SELECT SUM(IFNULL(`stocks`.`qty`, 0))
FROM `stocks`
WHERE `stocks`.`pid` = `products`.`id`
AND DATE(`stocks`.`created_at`) = ?
)
+ (
SELECT SUM(IFNULL(`loadings`.`qty`, 0))
FROM `loadings`
WHERE `loadings`.`pid` = `products`.`id`
AND DATE(`loadings`.`created_at`) = ?
)
) AS `close_balance`,
(
(
SELECT SUM(IFNULL(`stocks`.`qty`, 0))
FROM `stocks`
WHERE `stocks`.`pid` = `products`.`id`
AND DATE(`stocks`.`created_at`) = ?
)
- (
SELECT SUM(IFNULL(`loadings`.`qty`, 0))
FROM `loadings`
WHERE `loadings`.`pid` = `products`.`id`
AND DATE(`loadings`.`created_at`) = ?
)
) AS `open_balance`
FROM `products`
', [Carbon::today(), Carbon::yesterday(), Carbon::today(), Carbon::yesterday(), Carbon::today(), Carbon::yesterday(), Carbon::yesterday()]);
still open balance not works
array:7 [▼
0 => {#304 ▼ +"id": 1 +"name": "Kleensoft" +"total_stock": null +"total_loadings": null +"close_balance": null +"open_balance": null } 1 => {#306 ▼ +"id": 3 +"name": "Ukwaju" +"total_stock": null +"total_loadings": null +"close_balance": null +"open_balance": null } 2 => {#307 ▼ +"id": 4 +"name": "Embe" +"total_stock": "60" +"total_loadings": null +"close_balance": null +"open_balance": null } 3 => {#308 ▼ +"id": 5 +"name": "Ngano" +"total_stock": "200" +"total_loadings": 50.0 +"close_balance": 150.0 +"open_balance": null } 4 => {#309 ▼ +"id": 6 +"name": "Maji" +"total_stock": null +"total_loadings": null +"close_balance": null +"open_balance": null } 5 => {#310 ▼ +"id": 7 +"name": "item1" +"total_stock": null +"total_loadings": null +"close_balance": null +"open_balance": null } 6 => {#311 ▼ +"id": 8 +"name": "COLA" +"total_stock": "100" +"total_loadings": 50.0 +"close_balance": null +"open_balance": null } ]
The only way to verify this is if we can look into the actual data in the database.
i managed to resolve by using your first answer above
$stockSummary=DB::table('products')
->select(['products.id','products.name'])
->addSelect([
'total_stockIn' => DB::table('stocks')
->selectRaw('sum(ifnull(`stocks`.`qty`,0))')
->whereColumn('stocks.pid', '=', 'products.id')
->whereDate('stocks.created_at', '=', Carbon::today()),
'total_loadings' => DB::table('loadings')
->selectRaw('sum(ifnull(`loadings`.`qty`,0))')
->whereColumn('loadings.pid', '=', 'products.id')
->whereDate('loadings.created_at', '=', Carbon::today()),
'total_stock_jana' => DB::table('stocks')
-> selectRaw('sum(ifnull(`stocks`.`qty`,0))')
->whereColumn('stocks.pid', '=', 'products.id')
->whereDate('stocks.created_at', '=', Carbon::yesterday()),
'total_loadings_jana' => DB::table('loadings')
->selectRaw('sum(ifnull(`loadings`.`qty`,0))')
->whereColumn('loadings.pid', '=', 'products.id')
->whereDate('loadings.created_at', '=', Carbon::yesterday()),
])->get()
->map(function ($item) {
$item->total_stockIn = $item->total_stockIn ?? 0;
$item->total_stock_jana = $item->total_stock_jana ?? 0;
$item->total_loading_jana = $item->total_loading_jana ?? 0;
$item->total_loadings = $item->total_loadings ?? 0;
$item->CB = abs($item->total_stock_jana + $item->total_loadings);
$item->OB = abs($item->total_stock_jana - $item->total_loading_jana);
$item->Totalstock = abs($item->total_stock_jana + $item->total_stockIn);
return $item;
});
// dd($stockSummary);
return view('stock.summary',compact('stockSummary'));
}
Please or to participate in this conversation.