Can the active record class do this with a LEFT JOIN?

#1
[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.

#2
[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?

#3
[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.

#4
[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...

#5
[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

#6
[eluser]Crucial[/eluser]
Bingo! Can't believe I missed that. Thanks Seppo and champs Smile


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2019 MyBB Group.