Over populated where clause on leftjoin

6 posts by 3 authors in: Forums > CMS Builder
Last Post: April 28, 2016   (RSS)

By kitsguru - April 16, 2016

Where $invoiceNum = 10

    list($transactionRecords, $transactionMetaData) = getRecords(array(
        'tableName'     => 'member_transactions',
        "where"         => "member_transactions.num = $invoiceNum",
        'leftJoin'      => array('members' => 'member_num'),
        'limit'         => 1,
        "debugSql"      => true,
    ));

Is generating the following SQL and error:

SELECT SQL_CALC_FOUND_ROWS `member_transactions`.*,
members.`num` as `members.num`,
members.`status` as `members.status`,
members.`membership_type` as `members.membership_type`,
members.`fullname` as `members.fullname`,
members.`email` as `members.email`,
members.`username` as `members.username`,
members.`password` as `members.password`,
members.`isAdmin` as `members.isAdmin`,
members.`isStaff` as `members.isStaff`,
members.`lastLoginDate` as `members.lastLoginDate`,
members.`expiresDate` as `members.expiresDate`,
members.`neverExpires` as `members.neverExpires`,
members.`disabled` as `members.disabled`,
members.`adult_2` as `members.adult_2`,
members.`children` as `members.children`,
members.`address` as `members.address`,
members.`city` as `members.city`,
members.`province` as `members.province`,
members.`postal_code` as `members.postal_code`,
members.`phone` as `members.phone`,
members.`newsletter` as `members.newsletter`
FROM `cms_member_transactions` as `member_transactions`
LEFT JOIN `cms_members` AS `members` ON member_transactions.`member_num` = members.num
WHERE ((member_transactions.num = 10)
AND ((`createdByUserNum` = '1')
AND (`createdDate` = '2016-03-21 12:35:04')
AND (`num` = '4') AND (`status` = '1')
AND (`updatedByUserNum` = '1')
AND (`updatedDate` = '2016-04-16 10:24:09')
AND (`members`.`num` = '1')
AND (`members`.`createdDate` = '2016-03-18 15:52:48')
AND (`members`.`createdByUserNum` = '0')
AND (`members`.`updatedDate` = '2016-03-20 14:41:36')
AND (`members`.`updatedByUserNum` = '1')
#(added all the columns from both table and their actual values to the where clause)
)

ORDER BY membership_year DESC,member_num LIMIT 1

MySQL Error: Column 'createdByUserNum' in where clause is ambiguous

I am using the membership addon (with its own account table called members). I  use similar code in another area but the where clause does not add all the columns from both tables. I do not see why the where clause is being over populated.

Anyone have any ideas what is going on here?

Jeff Shields

By Daryl - April 21, 2016

Hi Jeff,

It seems like the function doesn't add the table name to "createdByUserNum".
Ie:

AND ((`members`.`createdByUserNum` = '1')

I will look into this and let you know.

Thanks,

Daryl Maximo
PHP Programmer - interactivetools.com

By kitsguru - April 21, 2016 - edited: April 21, 2016

HI Daryl,

BUT none of the extra columns is needed or wanted. So the fact that the table name is missing is not really the issue.

Jeff

Jeff Shields

By Daryl - April 21, 2016

You're right, the 'createdByUserNum', and the other fields, shouldn't be included at all.

I will check this out and let you know ASAP.

Cheers,

Daryl Maximo
PHP Programmer - interactivetools.com

By Daryl - April 27, 2016

Hi Jeff,

I can't replicate the issue on my local test server.

I'd like to take a closer look if you don't mind. Could you send through a second level support request?

https://www.interactivetools.com/support/email_support_form.php

Thanks,

Daryl Maximo
PHP Programmer - interactivetools.com