Welcome Guest, Not a member yet? Register   Sign In
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




Theme © iAndrew 2016 - Forum software by © MyBB