Level 23
Assuming it is a Many to Many relationship which you have setup this might work
App\Product::where('col1', 'val_1')->where('col2', 'val_2')->whereHas('categories', function ($query) {
$query->where('id', 1);
})->paginate();
Jun 1, 2018
3
Level 17
Retrieving products by category with pagination
Hi,
I have 'products' and 'product_categories' tables in my database. Currently I have over 150,000 records in product_categories table. And I need to add 3 where criteria in the query at 'products' table. So my query is like that -
select * from `products`
left join `product_category` on `product_category`.`product_id` = `products`.`product_id`
where `products`.`col_1` = 'val_1'
and `products`.`col_2` = 'val_2'
and `product_category`.`category_id` = 1
I'm using Laravel's Eloquent ORM, the above query is generated by toSql() for debugging purpose.
As the result, I want to display all products by category with pagination in the view.
Can anyone suggest the best solution? Thanks!
Level 17
Hi @ksparkar, the query is working. But the problem is that query runtime is too slow. It took about 35 seconds.
Level 23
Does this query work any faster?
App\Category::where('id', 1)->with('products' => function ($query) {
$query->where('col1', 'val_1')
->where('col2', 'val_2');
})->paginate();
Check that you have the appropriate columns indexed on the DB?
Please or to participate in this conversation.