Level 75
If you have a working query like that I suggest use as is.
2 likes
Dec 1, 2022
7
Level 1
can someone tell how to translate theis sql query to laravel query builder
I want to write this sql query into laravel query builder
SELECT orders.invoice_number,u.first_name,u.last_name,orders.order_date,
(SELECT sum(products.price*order_items.qantity) FROM products INNER JOIN order_items on products.id=order_items.product_id
WHERE order_items.order_id=orders.id) as total,(SELECT users.first_name FROM users WHERE users.id=u.referred_by)as distributor_FirstName,
(SELECT users.last_name FROM users WHERE users.id=u.referred_by)as distributor_LastName,
(SELECT COUNT(users.id) FROM users WHERE users.referred_by=u.referred_by)as ReferredNum,
(SELECT name FROM categories INNER JOIN user_category on user_category.category_id=categories.id WHERE user_category.user_id=u.referred_by)as
category_name FROM orders LEFT JOIN users AS u on orders.purchaser_id=u.id;
i tried doing this way
$data= DB::select("SELECT orders.invoice_number,u.first_name,u.last_name,orders.order_date,
(SELECT sum(products.price*order_items.qantity) FROM products INNER JOIN order_items on products.id=order_items.product_id
WHERE order_items.order_id=orders.id) as total,(SELECT users.first_name FROM users WHERE users.id=u.referred_by)as distributor_FirstName,
(SELECT users.last_name FROM users WHERE users.id=u.referred_by)as distributor_LastName,
(SELECT COUNT(users.id) FROM users WHERE users.referred_by=u.referred_by)as ReferredNum,
(SELECT name FROM categories INNER JOIN user_category on user_category.category_id=categories.id WHERE user_category.user_id=u.referred_by)as
category_name FROM orders LEFT JOIN users AS u on orders.purchaser_id=u.id;");
but the page just kept loading with no results
i tried using DB:raw to execute the sub query but it didn't work
$t=DB::table('orders')->select('orders.invoice_number','u.first_name','u.last_name'
,'orders.order_date',DB::raw('(select sum(products.price*order_items.qantity)'.' as total from products INNER JOIN order_items on products.id=order_items.product_id WHERE order_items.order_id=orders.id)')
,DB::raw('(select users.first_name as distributor_FirstName from users where users.id=u.referred_by)')
,
DB::raw('(select users.last_name as distributor_LastName from users where users.id=u.referred_by)'),
DB::raw('(select count(users.id) as ReferredNum from users where users.referred_by=u.referred_by)'),
DB::raw('(select name as category_name from categories inner join user_category on user_category.category_id=categories.id where user_category.user_id=u.referred_by)')
)->leftJoin('users as u','orders.purchaser_id','=','u.id')->get();
Level 73
My suggestion is that you don't, I would make a database view out of the query and then query that.
Here is a guide on how to do that.
https://tray2.se/posts/use-a-view-instead-of-a-complex-eloquent-query-in-your-laravel-application
2 likes
Level 1
OK I found a way to convert it but it take way to long to execute (283 seconds) does any one have a suggestion to make it quicker and why does it take that long despite taking less than two second when i tried in MySQL console.
$t = DB::table('orders')
->selectRaw('orders.invoice_number,u.first_name,u.last_name,orders.order_date,
(SELECT sum(products.price*order_items.qantity) FROM products INNER JOIN order_items on products.id=order_items.product_id
WHERE order_items.order_id=orders.id) as total,(SELECT users.first_name FROM users WHERE users.id=u.referred_by)as distributor_FirstName,
(SELECT users.last_name FROM users WHERE users.id=u.referred_by)as distributor_LastName,
(SELECT COUNT(users.id) FROM users WHERE users.referred_by=u.referred_by)as ReferredNum,
(SELECT name FROM categories INNER JOIN user_category on user_category.category_id=categories.id WHERE user_category.user_id=u.referred_by)as
category_name')
->leftJoin('users as u', 'orders.purchaser_id', '=', 'u.id')
->get();
Level 75
@mohamadAbdelhady try the db facade or getPdo().
See https://laracasts.com/discuss/channels/laravel/sql-native-to-query-builder
Or as @tray2 suggested, give a database view a try.
1 like
Level 1
@jlrdw so i logged the time of excution using
DB::listen(function ($query) {
Log::info($query->sql); // the query being executed
Log::info($query->time); // query time in milliseconds
});
and that the output and i put paginTION BY 5 and it still slow takes(6.68 s)
[2022-12-02 09:46:03] local.INFO: select count(*) as aggregate from `orders` left join `users` as `u` on `orders`.`purchaser_id` = `u`.`id`
[2022-12-02 09:46:03] local.INFO: 6080.39
[2022-12-02 09:46:04] local.INFO: select orders.invoice_number,u.first_name,u.last_name,orders.order_date,
(SELECT sum(products.price*order_items.qantity) FROM products INNER JOIN order_items on products.id=order_items.product_id
WHERE order_items.order_id=orders.id) as total,(SELECT users.first_name FROM users WHERE users.id=u.referred_by)as distributor_FirstName,
(SELECT users.last_name FROM users WHERE users.id=u.referred_by)as distributor_LastName,
(SELECT COUNT(*) FROM users GROUP BY referred_by HAVING referred_by=u.referred_by)as ReferredNum,
(SELECT name FROM categories INNER JOIN user_category on user_category.category_id=categories.id WHERE user_category.user_id=u.referred_by)as
category_name from `orders` left join `users` as `u` on `orders`.`purchaser_id` = `u`.`id` limit 5 offset 0
[2022-12-02 09:46:04] local.INFO: 540.7
do you have any sugestion
Level 1
I solved using this code
public function getOrdersData(Request $request)
{
$Page=$request['page'];
$data = DB::table('orders')
->selectRaw('orders.invoice_number,u.first_name,u.last_name,orders.order_date,
(SELECT sum(products.price*order_items.qantity) FROM products INNER JOIN order_items on products.id=order_items.product_id
WHERE order_items.order_id=orders.id) as total,(SELECT users.first_name FROM users WHERE users.id=u.referred_by)as distributor_FirstName,
(SELECT users.last_name FROM users WHERE users.id=u.referred_by)as distributor_LastName,
(SELECT COUNT(*) FROM users GROUP BY referred_by HAVING referred_by=u.referred_by)as ReferredNum,
(SELECT name FROM categories INNER JOIN user_category on user_category.category_id=categories.id WHERE user_category.user_id=u.referred_by)as
category_name')
->join('users as u', 'orders.purchaser_id', '=', 'u.id')
->offset(($Page-1)*10)->limit(30)->get();
$dataChunk = new paginator($data,10,$Page);
return $dataChunk;
}
1 like
Level 75
@mohamadAbdelhady glad you got it working.
Please or to participate in this conversation.