Mysql query

I have tables called categories fields(id, name), category_product fields(id, product_id, category_id), and products fields(id, name, etc). Assume, that product 1 has 2 categories. I want to query categories related to product 1 or 2 and so on. How can I write the join query?

Thank you

tykus

3 years ago

Level 104

Assuming you have defined a belongsToMany relation between Product and `Category. If you already have a Product instance, you can get the related Categories using:

$categories = $product->categories

If you are fetching a Collection of Product instances; you can eager-load the Categories using:

$products = Product::with('categories')

If you just want a distinct list of Categories related to a given Collection of Products

Category::whereHas('products', fn ($builder) => $builder->whereIn('id', $products->pluck('id'))->get();

feralam

3 years ago

Level 4

@tykus I forgot to write. I am not using Laravel, I am developing a PHP, MySQL, and MVC-based project.

Tray2

3 years ago

Best Answer

Level 73

@feralam then you use joins. https://tray2.se/posts/database-design-part-2

feralam

3 years ago

Level 4

@Tray2 Thank you and got it. And made a video for future use to whoever will search in the future. https://youtu.be/N_Jj0wUDicU

Please or to participate in this conversation.