02-17-2009, 11:14 PM
[eluser]Relexx[/eluser]
[quote author="MarcL" date="1234927082"]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?[/quote]
you could always create a view for this instead
Then join the view
[quote author="MarcL" date="1234927082"]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?[/quote]
you could always create a view for this instead
Code:
create view last_prices as select max(created_on) as created, product_id, merchant_id from prices
Then join the view
Code:
select prc.id, prc.price, prd.id, prd.name, mer.name
from prices prc, product prd, merchant mer, last_prices lp
// 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 = lp.product_id
and prc.merchant_id = lp.merchant_id
and prc.created_on = lp.created
// then add the joins for the product and merchant tables
and prd.id = prc.product_id
and mer.id = prc.merchant_id