EllisLab text mark
Advanced Search
     
Active record - same table multiple join?
 
jonno
Posted: 01 April 2010 01:17 PM   [ Ignore ]
Joined: 2007-11-12
2 posts

Hello Everyone

Long time user, first poster.  Codeigniter is has really changed the way I code (for the better), but I’m struggling with an SQL concept that is sometimes needed.

I’m using the active record class and I need to join on the same table twice. 

To explain I have an Orders table for an online shop which stores the ID’s for a billing address and delivery address, both which relate to the same table CustomerAddresses.

This would usually be achieved in normal MySQL syntax something similar to the following:

SELECT Orders.*, Billing.*, Delivery.* FROM Orders
LEFT JOIN CustomerAddresses AS Billing
ON Orders.BillingAddrID = Billing.ID
LEFT JOIN CustomerAddresses AS Delivery
ON Orders.DeliveryAddrID = Delivery.ID
WHERE Orders.ID = x 

What is the best way to tackle this problem with the active record class?, or is it even possible as there seems no way to alias joined tables.

Thanks for any help in advance.  grin
 
cahva
Posted: 01 April 2010 10:01 PM   [ Ignore ]   [ # 1 ]   [ Rating: 0 ]
Avatar
Joined: 2008-07-16
664 posts

What do you mean no way to alias joined tables?

$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.
 
jonno
Posted: 06 April 2010 03:44 AM   [ Ignore ]   [ # 2 ]   [ Rating: 0 ]
Joined: 2007-11-12
2 posts

Thank you, learn something new everyday!
 
patwork
Posted: 19 May 2010 03:13 PM   [ Ignore ]   [ # 3 ]   [ Rating: 0 ]
Avatar
Joined: 2009-04-14
23 posts

Hi,

I’m trying to do same thing, and I’m afraid it’s not working properly.

CodeIgniter’s activerecord creates query like this:

SELECT `st_id`, `table1`.`us_login`, `table2`.`us_login`
FROM (`stream`)
LEFT JOIN `users` AS table1 ON `table1`.`us_id` = `stream`.`st_id_user_from`
LEFT JOIN `users` AS table2 ON `table2`.`us_id` = `stream`.`st_id_user_to` 

That way, you will loose fields with duplicated names:

object(stdClass)
      public 'st_id' => string '1'
      public 'us_login' => string 'somelogin' 

I have managed to fix this, by adding “AS name” to every field:

SELECT `st_id`, `table1`.`us_login` AS t1_login, `table2`.`us_login` AS t2_login
... 

Which in result gave me:

object(stdClass)
      public 'st_id' => string '1'
      public 't1_login' => string 'somelogin'
      public 't2_login' => string 'someotherlogin' 

Frankly, I can’t imagine how to create such a query for fields with wildcards, like “SELECT st_id, table1.*, table2.*”, because names of keys in results arrays have only fields names, without tables.

 Signature 

http://bitbucket.org/patwork/codeigniter-reactor-polish-language
http://bitbucket.org/patwork/codeigniter-reactor-my-modifications
 
danmontgomery
Posted: 19 May 2010 05:26 PM   [ Ignore ]   [ # 4 ]   [ Rating: 0 ]
Avatar
Joined: 2009-12-08
1804 posts

That’s the way the fields are returned by MySQL, it has nothing to do with PHP or Codeigniter.

mysql> select my_status.title from my_status;
+----------+
| title    |
+----------+
| Status A |
| Status B |
| Status C |
| Status D |
+----------+
4 rows in set (0.00 sec) 

You would have to alias any identically named fields, regardless of the implementation.

 Signature 

@basdflasjk | BitAuth: Authentication and Role-based Permissions | Session Library Replacement


Please read the User Guide! (Upgrading from a previous version?)