Use data from method with() in models for using self-written sql query Laravel

I'm using Laravel 6.16.

I wanna use data what I receive from Order and OrderDetail models in self-written SQL query in "prod" function, data like product_id from order_detail table.

Have request like this

$orders = Order::where('store_id', $store_id)
               ->where('company_id', $company_id)
               ->with('order_details.prod')
               ->get();

Order Model

protected $fillable = [
	'store_id','company_id', 'site_id', 'status_id'
];

public function order_details($language = null)
{
	return $this->hasMany('App\Models\OrderDetail','order_id','id');
}

OrderDetail Model (where I wanna use data)

protected $fillable = [
	'order_id','product_id','user_id', 'site_id', 'status_id','quantity','price'
];

public  function prod()
{
   /*Some self-written SQL queries example*/
$sql = "SELECT
p.`id`,
p.`manufacturer_id`,
p.`store_id`,
p.`code`,
p.`alternative_code`,
p.`count`,
p.`price`,
p.`old_price`,
pt.`name`,
m.`name` manufacturer_name
	FROM `products` p
LEFT JOIN `product_translations` pt
	ON p.`id` = pt.`product_id`
LEFT JOIN `manufacturers` m
	ON p.`manufacturer_id` = m.`id`
WHERE pt.`language_id` = ?  AND p.`id` = ?";
$queryParams = [$langId];
$queryParams[] = **; 
$products = DB::select($sql, $queryParams);
	return $products;
}

How I can receive this data if I use ->with() method? This is possible to use a function like this? Can someone help, please?

aleahy

5 years ago

Best Answer

Level 25

I think ->with() is supposed to work with relationships. But you can use closures to constrain the information you receive from with, as described here: https://laravel.com/docs/6.x/eloquent-relationships#constraining-eager-loads

The code that you have there definitely looks like it could be rewritten with relationships. Looking at the Product model, I assume it has a "HasMany" relationship to ProductTranslations and a "BelongsTo" relationship to manufacturers?

So that function could be replaced with:

->with(['products' => function($query) use ($langId) {
	$query->with(['manufacturer', 'product_translations' => function($query) {
		$query->where('language_id', $langId);
	});
}])

Here I have said that the products have to eager load the manufacturer and product translations relationships. But I have put a further constraint on the product_translation relationship relating to the language id.

1 like

hik200

5 years ago

Level 1

You are right. All work but I have a view that prepared for SQL format this is because I use this SQL before (use raw SQL because when I use relation this took too much time to receive result when raw SQL was x5 faster) how I understand I should write new raw SQL to receive all data what I want. I think ->with() method more agile.

My result

$orders=Order::where('store_id',$store_id)->where('company_id',$company_id)
->with(['order_details.product' => function($query) {
	   $query->with(['manufacturer', 'translation']);
	}])->get();

aleahy

5 years ago

Level 25

If you don't use eager loading, you could be making lots of queries for each order which drags out the response time massively.

It's a good idea to use a tool like Telescope or Debugbar that can tell you exactly what queries your requests are making so you can check you are getting exactly what you want.

Please or to participate in this conversation.