Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

la-bas's avatar

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...
    ]
}
0 likes
1 reply
douglasakula's avatar

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

Please or to participate in this conversation.