Welcome Guest, Not a member yet? Register   Sign In
Price database and confusing SQL query
#1

[eluser]MarcL[/eluser]
Hello.

I've been coding up a product database which takes a number of XML feeds and records the prices of the products over time. I'd like to be able to monitor the price of say an iPod and see what the best price is at a number of different retailers on a given day.

I'm pretty new to MySQL so I'd like some help on whether I'm representing my data correctly in the database tables and how to form the correct SQL query (using ActiveRecord but the whole query is fine too).

So my data is in this format:

product
- id
- name
- description
- etc

merchant
- id
- name
- description
- etc

prices
- id
- price
- product_id
- merchant_id
- created_on (DATETIME that this price was added to the database)

Have I represented my data in the prices table correctly or have I made it difficult for myself?! Smile

The query I'd like is something to get the latest prices from all merchants for a given product id.

Thanks in advance for any help.

Cheers,
Marc
#2

[eluser]umefarooq[/eluser]
hi
if you have one product with multiple merchants and price is different from each other or same than its ok
#3

[eluser]obiron2[/eluser]
looks good to me.

SQL to get latest prices

select p.merchant_id,p.price,max(p.created_on),m.name,m.description
from prices p
join merchant m on p.merchant_id = m.id
where p.product_id = $productID
group by p.merchant_id,m.name,m.description
order by m.name


SQL to get prices at a specific date

select p.merchant_id,p.price,max(p.created_on),m.name,m.description
from prices p
join merchant m on p.merchant_id = m.id
where p.product_id = $productID
and p.created_on <= $PriceDate
group by p.merchant_id,m.name,m.description
order by m.name

Obiron
#4

[eluser]MarcL[/eluser]
I read that the people on the Code Igniter website were helpful but I didn't expect replies in under an hour. Thanks guys!

@umefarooq

Yes, there will be products with multiple prices from multiple merchants. I'm trying to record the change in prices over time and find out which merchant is the cheapest. Like a price comparison website.

@obiron2

Thanks for the SQL. But the 'get latest prices' hasn't quite worked.
The data I've got in the prices array for a certain product id (435) is:

Code:
product_id  merchant_id  price     created_on
435         4            29.97     2009-02-16 13:01:48
435         3            11.99     2009-02-16 13:02:02
435         4            26.99     2009-02-16 21:07:10
435         5            23.75     2009-02-16 21:21:28

When I run the query I get

Code:
merchant_id price   max(p.created_on)       name    description
3           11.99   2009-02-16 13:02:02     Name1   desc1
4           29.97   2009-02-16 21:07:10     Name2   desc2
5           23.75   2009-02-16 21:21:28     Name3   desc3
So merchant_id 4 doesn't have the latest price. Instead it has a combination of the first price and the second (and latest) created_on date/time.

Any ideas?
#5

[eluser]umefarooq[/eluser]
try order by created_on either desc or asc not need to use max
#6

[eluser]MarcL[/eluser]
Thanks umefarooq.

I'll try it when I get home tonight (as I was working on it during my lunch hour) and I'll let you know if it works.
#7

[eluser]kgill[/eluser]
Using asc or desc to solve this is the wrong way to go about it, that will give you all the prices including old ones but just stick them at the start or end of the list. The reason the other query didn't work is that the price is different for each of the dates so you can't include it in the same query as the max() & group by statements.

Anytime you want to get the most recent set of values for a given group where one of the columns is dependent on the date you're looking at a subquery.

Code:
select prc.id, prc.price, prd.id, prd.name, mer.name
from prices prc, product prd, merchant mer,
     (// the subquery finds the highest date for each merchant for a given product
      select max(created_on) as created, product_id, merchant_id
      from prices
      where product_id = $product_id
      group by product_id, merchant_id
     ) max
// join the data from the subquery back to the prices table so that you select the max record
// and thus the most recent price
where prc.product_id = max.product_id
and   prc.merchant_id = max.merchant_id
and   prc.created_on = max.created
// then add the joins for the product and merchant tables
and   prd.id = prc.product_id
and   mer.id = prc.merchant_id

// You could do this using JOIN's but I find nesting that many gets ugly fast.

Now the disclaimer, I work with Oracle on a daily basis so you might have to alter that query a bit.
#8

[eluser]MarcL[/eluser]
Thanks for that query kgill. It worked great.
Looks like I picked the wrong project to work on as an introduction to SQL! Wink

Now, can I reproduce the sub-query using ActiveRecord?
Time to try!
#9

[eluser]MarcL[/eluser]
Sorry for my ignorance but I've been having a look at ActiveRecord and I'm not sure that I can do the inner query with it. Is that right?

Should I split this into 2 queries? If so, how do I do it with ActiveRecord?

Or should I just run this as a query using $this->db->query()?
#10

[eluser]TheFuzzy0ne[/eluser]
Code:
// Set up your query here


$subquery = $this->db->_compile_select(); // Get your query.
$this->db->_reset_select(); // Reset the class before reusing it.
The code above is untested

These are private functions, so this should work, but I would imagine there's a better way. I'm totally sure there is a much simpler way that I've overlooked, and another member is going to jump up and smack me round the head with it...

Also, it will break when CodeIgniter eventually supports only PHP version 5 and up (if this ever happens). So please, keep an eye on this post in case someone, does indeed, offer you a real solution.




Theme © iAndrew 2016 - Forum software by © MyBB