Level 104
Triple backticks (```) wrapping the code please
1 like
Apr 11, 2022
8
Level 3
Laravel Eloquent Query, is this too complex?
Ok so, here is a little background of my db: - Products Table (price, discount, category_id ...) - Categories Table (image_path) - Products Languages Table (description, title, language_id) - Categories Languages Table (category name, language_id) I've done it in this way because this web app supports multiple languages. In the snippet down below I am getting the product information, and the category in which this product is in information, in the language in which the locale is set in the moment of the query. I am getting the results I want, but do you think there could be a more optimized way of doing it?
$products_sale = ProductDifferentLang::whereHas('language',function ($q){
$q->where('slug', app()->getLocale());
})
->whereHas('product', function($q){
$q->where('product_discount', '>', '0');
})
->with('product.images')
->with('product.category.CategoryDifferentLang', function($q){
$q->whereHas('language', function ($q){
$q->where('slug', app()->getLocale());
});
})->latest()->take(10)->get();
MORE INFO: CategoryDifferentLang is the model for 'Products Languages Table' and ProductDifferentLang is the model for ' Categories Languages Table'
Level 3
@tykus I was wondering how is it done. Thanks.
1 like
Level 104
I would consider joining the language tables so the Product and Category models are the "main" models rather that the derived _language ones. The JOIN could either completely replace the title, description in the model; or append extra properties like local_title and local_description to the Product/Category instance
1 like
Level 3
@tykus The fact that the 'main' model in this is the derived _languages one is the reason that I asked this question, it seemed a little bit strange. But since I have to get the product in that certain language I didn't think of any other way. When you say joining the 'language' tables so that the product/category are the 'main' what exactly do you mean? How would I do that? I am not exactly understending
Level 104
@Kris01 I don't mean the languages table; rather the product_language and category_language table (or whatever they are named). Their reason for existence is only to provide locale-specific versions of a Product, right?
Level 3
@tykus Yes. The only reasong those two table exist is to avoid, for example, adding fields like 'title_en' 'description_en' 'title_it' 'description_it' and so on to the product table. So I decided to completely remove title and description from the products table, and create one 'products_languages' in which i have the title, description and the language_id.
Level 104
@Kris01 okay, so IMHO a JOIN would make sense - you are going to make a Product instance using columns from both tables. Let's start simply:
$products_sale = Product::query()
->selectRaw('products.*, local_products.title, local_products.description')
->leftJoinSub(
ProductLanguage::join('languages', 'languages.id', '=', 'product_languages.language_id')->where('languages.slug', app()->getLocale()),
'local_products',
fn (JoinClause $join) => $join->on('products.id', '=', 'local_products.product_id');
)
->where('product_discount', '>', '0')
->latest()
->take(10)
->get();
Now you have a Collection of 10 Product instances each with a title and description attribute in the specific locale.
Level 27
A join does get what you want in a more sensible way than your original solution, but readability-wise, it’s not really better than what you started out with.
It would be more readable and simple to just use regular relationships on the models, though it would add a couple of extra database calls; depending on your application logic and how important it is to cut down on database transactions, that may or may not be an issue. Since the descriptions are (or can be made to be) roughly the same for products and categories, I’d probably just keep them in the same table and use polymorphic relations.
I would probably structure the tables like so:
# table products
- id
- category_id
- discount // etc.
# table categories
- id
- image
# table metadata
- id
- title
- description
- language
- model_id
- model_type
Then I’d have my product and category models set up with a polymorphic relation for the metadata, like so:
public function metadata() {
return $this->morphOne(Metadata::class, 'model')->where('language', app()->getLocale());
}
And of course regular relations between products and categories:
# class Product
public function category() {
return $this->belongsTo(Category::class);
}
# class Category
public function products() {
return $this->hasMany(Product::class);
}
You’d need to make a Metadata model as well, of course, but you wouldn’t really need to add anything other than a bare skeleton to that class, it just needs to be there (and some relations, if you ever plan on needing to fetch categories or products based on their metadata).
To retrieve everything, you’d then do this in your controller (or wherever you’re fetching the objects):
$products_sale = Product::query()
->with('category', 'metadata')
->where('discount', '>', 0)
->latest()
->take(10)
->get()
;
That would result in three database queries, rather than one as in @tykus’ answer, but with the benefit of what I would consider much more readable code.
1 like
Please or to participate in this conversation.