Thursday, 9 January 2020

What is the better way of getting concatenated rows from a child table in a query?

For my college project I am doing a model e-commerce website for learning. I have searched for similar questions, however I am unable to get deeper ideas.

My current tables structure:

  1. Products - sku(PK), name, price, other_columns
  2. ProductMedia - path(UNIQUE), product_sku (from products table).

The path contains the filename of the media.

When I retrieve a product data, I select the columns from table: products and LEFT JOIN productMedia and GROUP_CONCAT and get the paths.

Query:

SELECT
p.*, // for simplicity mentioned as *
(GROUP_CONCAT(path SEPARATOR ',') FROM productMedia WHERE product_sku = p.sku) as product_images
FROM products p
WHERE SOME_CONDITION

To display, I explode the product_images and loop to display or use it for other purposes.

Now my questions are:

  1. Is this efficient approach? Or any better approach which I am unaware of?
  2. When multiple columns need to be concatenated from the child table (here productMedia), I am writing GROUP_CONCAT (column SEPARATOR) as concatenatedColumn multiple times, will this not affect the performance?


from What is the better way of getting concatenated rows from a child table in a query?

No comments:

Post a Comment