la-bas
2 weeks ago

Querying many to many with a pivot table with date column

Posted 2 weeks ago by la-bas

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 sign in or create an account to participate in this conversation.