CODEWITHSUNDEEP
mysqldatabasedatabase-design
elwood
elwood

Reputation: 346

MySql retrieve products and prices

I would like to retrieve a list of all the products, with their associated prices for a given period.

The Product table:

Id
Name
Description

The Price table:

Id 
Product_id
Name
Amount
Start
End
Duration

The most important thing to not here, is that a Product can have mutliple prices, even over the same period, but not with the same duration. For example, a price from "2013-06-01 -> 2013-06-08" and another from "2013-06-01 -> 2013-06-05"

So my aim is to retrieve, for a given period, the lists of all products, paginated by 10 product for example, joined to the prices existant over the period.

The basic way to do so would be:

SElECT *
FROM product
LEFT JOIN prices ON ...
WHERE prices.start >= XXX And prices.end <= YYY
LIMIT 0,10

The problem while using this simple solution, is that I can't retrieve only 10 Products, but 10 Products*Prices, which is not acceptable in my case.

So the solution would be:

SElECT *
FROM product
LEFT JOIN prices ON ...
WHERE prices.start >= XXX And prices.end <= YYY
GROUP BY product.id
LIMIT 0,10

But the problem here is, i'll only retrieve "1" price for each product.

So I wonder what would be the best way to handle this. I could for example use a group function, like "group_concat", and retrieve in a field all the prices in a string, like "200/300/100" and so on. That seem weird, and would need work on server-language side to transform to a readable information, but it could work.

Another solution would be to use different column for each prices, depending on duration:

SELECT 
   IF( NOT ISNULL(price.start) AND price.duration = 1, price.amount , NULL) AS price_1_day
   ---- same here for all possible durations ---
From ...

Thta would work too i guess (i'm not really sure if this is possible however), but I may need to create about 250 columns to cover all possibilities. Is that a safe option ?

Any help will be much appreciated

Upvotes: 2

Views: 710

Answers (2)

rollcona
rollcona

Reputation: 163

I believe that a group_concat would be the best way forward on this, as its very purpose is to aggregate multiple pieces of data relating to a particular column.

However, adapting on peterm's SQL fiddle, this is possible to do in 1 query if using user defined variables. (If one ignores the initial query for setting the vars)

http://dev.mysql.com/doc/refman/5.7/en/user-variables.html

SET @productTemp := '', @increment := 0;

SElECT
  @increment := if(@productTemp != Product_id, @increment + 1, @increment) AS limiter,
  @productTemp :=Product_id as Product_id,
  Product.name,
  Price.id as Price_id,
  Price.start,
  Price.end
FROM 
  Product
LEFT JOIN
  Price ON Product.Id=Price.Product_id
WHERE 
  `start` >= '2013-05-01' AND `end` <= '2013-05-15'
GROUP BY
 Price_id
HAVING 
  limiter <=2

What we're doing here is only incrementing the user defined var "incrementer" only when the product id is not the same as the last one that was encountered.

As aliases cannot be used in the WHERE condition, we must GROUP by the unique ID (in this case price ID) so that we can reduce the result using HAVING. In this case, I have a full result set that should include 3 Product IDs, reduced to only showing 2.

Please note: This is not a solution I would recommend on large data sets, or in a production enviornment. Even the mysql manual makes a point of highlighting that user defined vars can behave somewhat erratically depending on what paths the optimizer takes. However, I have used them to great effect for some internal statistics in the past.

Fiddle: http://sqlfiddle.com/#!2/96c92/3

Upvotes: 1

peterm
peterm

Reputation: 92785

It's hard to tell without sample data and desired output but you can try something like this

SElECT p.*, q2.*
  FROM 
(
  SElECT Product_id
    FROM Price
   WHERE `start` >= '2013-05-01' AND `end` <= '2013-05-15'
   GROUP BY Product_id
  LIMIT 0,10
) q1 JOIN 
(
    SELECT *
      FROM Price
     WHERE `start` >= '2013-05-01' AND `end` <= '2013-05-15'
) q2 ON q1.Product_id = q2.Product_id JOIN product p
     ON q1.Product_id = p.Id

Here is SQLFiddle demo

Upvotes: 0

Related Questions