Price database and confusing SQL query

[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

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

[eluser]MarcL[/eluser]
Thanks for the tips guys.

[quote author="Relexx" date="1234955649"]

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

[/quote]

Can I do this with ActiveRecord Relexx?

Or, in fact, should I do this with ActiveRecord or is a straight db->query() the way to go for more complex queries?

[eluser]Relexx[/eluser]
I would recommend that you use active record where ever possible. read ActiveRecord

The below is not tested

Code:

$this->db->select ('prices.id, product.id, product.name, merchant.name');
$this->db->from ('prices, product, merchant, last_prices');
$where_array = array('prices.product_id' => 'last_prices.product_id', 'prices.merchant_id' => 'last_prices.merchant_id', 'prices.created_on' => 'last_prices.created', 'product.id' => 'prices.product_id', 'merchant.id' => 'prices.merchant_id');
$this->db->where ($where_array);

You may be able to do this as well I am not sure. I would be interested to know if it does

Code:

$this->db->select ('prices.id');
$this->db->select ('product.id');
$this->db->select ('product.name');
$this->db->select ('merchant.name');
$this->db->from ('prices');
$this->db->from ('product');
$this->db->from ('merchant, last_prices');
$this->db->where ('prices.product_id',  'last_prices.product_id');
$this->db->where ('prices.merchant_id', 'last_prices.merchant_id');
$this->db->where ('prices.created_on', 'last_prices.created');
$this->db->where ('product.id', 'prices.product_id');
$this->db->where ('merchant.id', 'prices.merchant_id');
$this->db->where ($where_array);

Likewise the join clauses in the where could be replaced by using $this->db->join() but I am less familiar with this style of sql syntax.