left join with codeigniter

[eluser]handoyo[/eluser]
Hi,i found a code like this from oscommerce.

Code:

$orders_query_raw = "select o.orders_id, o.customers_name, o.customers_id, o.payment_method, o.date_purchased, o.last_modified, o.currency, o.currency_value, s.orders_status_name, ot.text as order_total from ".TABLE_ORDERS." o left join ".TABLE_ORDERS_TOTAL." ot on (o.orders_id = ot.orders_id), ".TABLE_ORDERS_STATUS." s where o.customers_id = '".(int) $cID."' and o.orders_status = s.orders_status_id and s.language_id = '".(int) $languages_id."' and ot.class = 'ot_total' order by orders_id DESC";

I'm trying to change it to active record according to my table..

Code:

$Q=$this->db->from('orders');
$Q=$this->db->join('orders_total','orders_total.orders_id=orders.orders_id','left');
$Q=$this->db->join('orders_status','orders.orders_id=orders_status.orders_status_id');
$Q=$this->db->where('orders.orders_id',$id);
$Q=$this->db->where('orders_total.class','ot_total');
$Q=$this->db->where('orders.orders_status','orders_status.orders_status_id');
$Q=$this->db->get();

Are there anything wrong with the code?Also is it possible if i don't use ON orders.orders_id =orders_status.orders_status_id?Thanks

[eluser]Krzemo[/eluser]
Seems like you forgot about language_id in orders_status and that you have to use ON orders.orders_id =orders_status.orders_status_id if you want to get order status in language of choice.
Personally I would change the original query to whats below and then respectively to CI AC.

Code:

select o.orders_id
, o.customers_name
, o.customers_id
, o.payment_method
, o.date_purchased
, o.last_modified
, o.currency
, o.currency_value
, s.orders_status_name
, ot.text as order_total
from ".TABLE_ORDERS." o
left join ".TABLE_ORDERS_TOTAL." ot on o.orders_id = ot.orders_id and ot.class = 'ot_total'
join ".TABLE_ORDERS_STATUS." s on o.orders_status = s.orders_status_id and s.language_id = '".(int) $languages_id."'
where o.customers_id = '".(int) $cID."'
order by orders_id DESC

[eluser]handoyo[/eluser]
I'm not intending to use the language Cshamoh. Suppose i don't use the language..Does my AC code are correct? Thanks..

[eluser]markup2go[/eluser]
Just try it out and see the results....