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

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

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

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

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

[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

client_packages.package_id = 'packages.package_id'

instead of

client_packages.package_id = packages.package_id

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