Your usecase implies you are better of using a non relational db which would store the product as a document with all the information it has. If using relational db for each product - you would have to do subqueries / functions within your models to fetch the data in the format you have shared above
Nov 5, 2019
1
Level 5
Querying many to many with a pivot table with date column
I am trying to log stocks data per date using three tables, but can't find a way to query the data and get it in a nice way.
These are the tables:
# stores
id
name
address
# products
id
name
#stocks
store_id
product_id
quantity
date
Any hint on how I can query the database and get the response in a clean way containing all the stores and their stocks, date by date for one particular product, for example like this:
"product": {
"id": 1,
"name": "Funny hat",
"stores": [
{ "id": 1,
"name": "Lorem",
"address": "Ipsum 11",
"stocks": [
{ "date" : "2019-11-01", "quantity": 3 },
{ "date" : "2019-11-02", "quantity": 0 },
{ "date" : "2019-11-03", "quantity": 20 }
]},
...more stores...
]
}
Please or to participate in this conversation.