+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived General Discussion (https://forum.codeigniter.com/forumdisplay.php?fid=21)
+--- Thread: Active record - same table multiple join? (/showthread.php?tid=29185)
Active record - same table multiple join? - El Forum - 04-01-2010
[eluser]Unknown[/eluser]
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:
Code:
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 = xWhat 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. :-)
Active record - same table multiple join? - El Forum - 04-01-2010
[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:
Quote: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.
Active record - same table multiple join? - El Forum - 04-06-2010
[eluser]Unknown[/eluser]
Thank you, learn something new everyday!
Active record - same table multiple join? - El Forum - 05-19-2010
[eluser]patwork[/eluser]
Hi,
I'm trying to do same thing, and I'm afraid it's not working properly.
CodeIgniter's activerecord creates query like this:
Code:
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:
Code:
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:
Code:
SELECT `st_id`, `table1`.`us_login` AS t1_login, `table2`.`us_login` AS t2_login
...Which in result gave me:
Code:
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.
Active record - same table multiple join? - El Forum - 05-19-2010
[eluser]danmontgomery[/eluser]
That's the way the fields are returned by MySQL, it has nothing to do with PHP or Codeigniter.
Code:
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.