Active record - same table multiple join?

[eluser]cahva[/eluser]
What do you mean no way to alias joined tables?

Code:

$this->db->select('Orders.*, Billing.*, Delivery.*')
    ->from('Orders')
    ->join('CustomerAddresses Billing','Orders.BillingAddrID = Billing.ID','left')
    ->join('CustomerAddresses Delivery','Orders.DeliveryAddrID = Delivery.ID','left')
    ->where('Orders.ID',2);

..would produce:

SELECT `Orders`.*, `Billing`.*, `Delivery`.*
FROM (`Orders`)
LEFT JOIN `CustomerAddresses` Billing ON `Orders`.`BillingAddrID` = `Billing`.`ID`
LEFT JOIN `CustomerAddresses` Delivery ON `Orders`.`DeliveryAddrID` = `Delivery`.`ID`
WHERE `Orders`.`ID` = 2

Remember, to alias you dont need AS.