Welcome Guest, Not a member yet? Register   Sign In
Can the active record class do this with a LEFT JOIN?

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:

    client_details.client_id = client_packages.client_id,
    client_packages.package_id = packages.package_id
    client_details.client_status = '2'
    client_packages.client_package_status = '2'
    client_packages.client_package_cycle = '1'

And here's the ARC code I'm using:

$mb->select('packages.package_price, client_packages.client_package_cycle, client_packages.client_package_discount, client_packages.package_price');
$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.

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?

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.

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

I'm sure
// 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

Bingo! Can't believe I missed that. Thanks Seppo and champs Smile

Theme © iAndrew 2016 - Forum software by © MyBB