+- 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: Can the active record class do this with a LEFT JOIN? (/showthread.php?tid=7149)
Can the active record class do this with a LEFT JOIN? - El Forum - 03-26-2008
[eluser]Crucial[/eluser]
I have a query I'm trying to run using the ARC, and it's not producing any results. If I leave it as a full statement using just the query call, it works fine. I think the problem comes from the LEFT JOIN, because the user guide doesn't seem to allow what I'm trying to do.
Below is my query:
Code:
SELECT
packages.package_price,
client_packages.client_package_cycle,
client_packages.client_package_discount,
client_packages.package_price
FROM
client_packages
LEFT JOIN
client_details
ON
client_details.client_id = client_packages.client_id,
packages
WHERE
client_packages.package_id = packages.package_id
AND
client_details.client_status = '2'
AND
client_packages.client_package_status = '2'
AND
client_packages.client_package_cycle = '1'And here's the ARC code I'm using:
Code:
$mb->select('packages.package_price, client_packages.client_package_cycle, client_packages.client_package_discount, client_packages.package_price');
$mb->from('client_packages');
$mb->join('client_details', 'client_details.client_id = client_packages.client_id, packages', 'left');
$mb->where('client_package.pack_id', 'packages.package_id');
$mb->where('client_details.client_status', '2');
$mb->where('client_packages.client_package_status', '2');
$mb->where('client_packages.client_package_cycle', '1');I'm using multiple databases, which is why it isn't $this->db->blah. Any help would be appreciated.
Can the active record class do this with a LEFT JOIN? - El Forum - 03-26-2008
[eluser]champs[/eluser]
I'm guessing you abandoned your AR approach by now, but did you look at the output of $mb->last_query() to see that it matches up?
Can the active record class do this with a LEFT JOIN? - El Forum - 03-26-2008
[eluser]Crucial[/eluser]
Hm, not really sure what you mean. When I run it as $query = $mb->query(QUERY) it works, when I split up that giant query using the AR class, I get nothing back, the array is empty. It would seem to me that the LEFT JOIN is messing it up because of the , packages part. In the user guide, it doesn't mention anything about this, only the first part, LEFT JOIN client_details ON client_details.client_id = client_packages.client_id.
I'd prefer to use the AR class, as the rest of my application has been using this method. I guess it really doesn't matter either way, but would like to know if the AR class is limited when it comes to joins.
Edit: Ah, I see what you're saying. See what the SQL actually is when using the AR method. Nah, didn't try that, but will do it now. Thanks.
Can the active record class do this with a LEFT JOIN? - El Forum - 03-26-2008
[eluser]Crucial[/eluser]
Ok, there are only two differences:
Normal Query: FROM client_packages
Active Record: FROM (`client_packages`)
Normal Query: LEFT JOIN client_details ON client_details.client_id = client_packages.client_id, packages
Active Record: LEFT JOIN `client_details` ON client_details.client_id = client_packages.client_id, packages
So just the backticks and the parenthesis...
Can the active record class do this with a LEFT JOIN? - El Forum - 03-26-2008
[eluser]Seppo[/eluser]
I'm sure
Code:
// the problem is
$mb->where('client_package.pack_id', 'packages.package_id');
// try with
$mb->where('client_package.pack_id = packages.package_id');Right now it produces
Quote:client_packages.package_id = 'packages.package_id'
instead of
Quote:client_packages.package_id = packages.package_id
Can the active record class do this with a LEFT JOIN? - El Forum - 03-26-2008
[eluser]Crucial[/eluser]
Bingo! Can't believe I missed that. Thanks Seppo and champs
