Issue with leftJoin complex expression

TechCoder

****Debugger code uwuwex**:

Error messages shown:
An SQL error occurred: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE drivers.driverid = '29'' at line 1

This is due to the sql having two "WHERE"s when doing the Editor Update (there is no error on table load)

SELECT  `drivers`.`driverid` as 'drivers.driverid', `drivers`.`driverRN` as 'drivers.driverRN', `drivers`.`driverSurname` as 'drivers.driverSurname', `drivers`.`driverFirstName` as 'drivers.driverFirstName', `drivers`.`driverMiddleName` as 'drivers.driverMiddleName', `drivers`.`driverLicenseCountry` as 'drivers.driverLicenseCountry', `drivers`.`driverLicenseDateIssued` as 'drivers.driverLicenseDateIssued', `drivers`.`driverLicenseDateExpires` as 'drivers.driverLicenseDateExpires', `drivers`.`driverLicenseNumber` as 'drivers.driverLicenseNumber', `drivers`.`driverHGV` as 'drivers.driverHGV', `drivers`.`driverADR` as 'drivers.driverADR', `drivers`.`driverMobilePhone` as 'drivers.driverMobilePhone', `drivers`.`driverIsAgency` as 'drivers.driverIsAgency', `drivers`.`driverStatus` as 'drivers.driverStatus', `customerdrivers`.`drivercustomerstatus` as 'customerdrivers.drivercustomerstatus', `drivers`.`driverFaceScan` as 'drivers.driverFaceScan', `drivers`.`driverLicenseScan` as 'drivers.driverLicenseScan', `drivers`.`password` as 'drivers.password', `drivers`.`salt` as 'drivers.salt' FROM  `drivers` 
LEFT JOIN `customerdrivers` ON drivers.driverRN = customerdrivers.driverRN  
LEFT JOIN `customers` ON customerdrivers.customerRN = customers.customerRN where customers.customerRN = 'vU2o9LjP' 
WHERE `drivers`.`driverid` = :where_0 ","bindings":[{"name":":where_0","value":"29","type":null}]}]} 

Description of problem:

Without the leftJoins, all functions work as expected (i.e., add/edit/delete, table views, select, etc....) - so all that code is not included and no test case needed.

With the leftJoin, this code provides the list of 'drivers' that we expect (limited only to those that match the customerRN, etc.) in the table load

Editor::inst($db, 'drivers', 'driverid')
    ->debug(true)
        ->leftJoin(
            'customerdrivers',
            'drivers.driverRN = customerdrivers.driverRN'
        )
        ->leftJoin(
            'customers',
            "customerdrivers.customerRN = customers.customerRN where customers.customerRN = '" . $_GET['CRN'] . "'"
        )

However, when we go to Edit the entry and Update, we get the above SQL error (with the double 'where').

I have tried various ways to change the complex expression in the second leftJoin, even using the same format as in https://editor.datatables.net/manual/php/joins

Editor::inst($db, 'drivers', 'driverid')
    ->debug(true)
    ->leftJoin(
        'customerdrivers',
        'drivers.driverRN = customerdrivers.driverRN'
    )
    ->leftJoin(
        'customers',
        "customerdrivers.customerRN = customers.customerRN and customers.customerRN IN (select customerRN from `customers` where customerRN = '" . $_GET['CRN'] . "')"
    )

though it always returns all the 'drivers' within the system (i.e., even the same format as the example does not limit the data).

The desired result is to get ONLY the selected drivers and then be able to edit them, etc.

Also, any new driver that is added from this should be placed into the 'customerdrivers' table (I believe that I will need to do that directly in the 'writeCreate' {??}, though I haven't gotten that far in testing yet..)

What is the correct complex expression to use in the leftJoin to get both view and edit to work?

allan

For your description, I don't think you actually want the join condition to have that extra WHERE statement, but rather that it should be applied to the overall condition, thus reducing the overall record set - e.g.:

select *
from drivers
left join customers on customerdrivers.customerRN = customers.customerRN
where customers.customerRN where customers.customerRN = ?

That would limit the list of drivers to just those where the customerRN matches. Or have I misunderstood?

To do that you would use:

Editor::inst($db, 'drivers', 'driverid')
    ->debug(true)
        ->leftJoin(
            'customerdrivers',
            'drivers.driverRN = customerdrivers.driverRN'
        )
        ->leftJoin(
            'customers',
            "customerdrivers.customerRN = customers.customerRN"
        )
        ->where('customers.customerRN', $_GET['CRN'])

Allan