Select from multiple tables - One to Many relation

Question

I have such tables:

Table Product

[ Id | Name ]

Table Images

[ Product_Id | Url | Ordernumber]

Table Prices

[ Product_Id | Combination | Currency | Price]

Table Quantites

[ Product_Id | Combination | Quantity]

---

Table Product is in relation one-to-many with other tables. I need to query the table and result something like this (pseudo-array):

[
    ProductId: 1,
    Name: 'Sample product',
    Images: [
        [url, 1],
        [url, 2]
    ],
    Prices: [
        [aaa, USD, 50.00],
        [aaa, EUR, 50.00],
        [bbb, USD, 59.00],
        [bbb, EUR, 59.00]
    ],
    Quantities: [
        [aaa, 5],
        [bbb, 3]
    ]
]

The way I'm doing it now is as follows: I query all the products, list their id's, and then query each table (images,prices,quantities) with WHERE IN clause. When I have all the data I start to parse the tables in php to get desired structure.

I wonder if there is some better way to extract those data, I have many different tables like this and creating configuration parser for each of them is a bit messy and problematic. Is there a possibility that mysql will take some burden from me?

thank you

Answer 1

This query will do the trick for you:

SELECT product.id, product.name, 
(SELECT group_concat(CONCAT('["',images.url, '",',  images.order_number,']')) FROM images WHERE images.product_id = product.id GROUP BY (product.id)) AS IMAGES_LIST,
(SELECT GROUP_CONCAT(CONCAT('["',prices.combination, '","', prices.currency, '",', prices.price,"]" )) FROM prices WHERE prices.product_id = product.id GROUP BY (product.id)) AS PRICE_LIST,
(SELECT GROUP_CONCAT(CONCAT('["',quantites.combination, '",',  quantites.quantity,"]")) FROM quantites WHERE quantites.product_id = product.id GROUP BY (product.id)) AS Quantity_LIST
FROM product WHERE product.id = 1

• First get the products
• for each one using a sub-query we get the related images, and using group concat we can get them in one field
• same thing for the prices and quantities