Where clause usage in setRelation and clause is ambiguous error

Grocery CRUD Enterprise
1

Hello @johnny,

Me again… sorry for the long post, it is important although!

I invite you so we can think together about the usage of the where clause in setRelation, take your seat… :slight_smile:

If the where statement in the setRelation method (4th parameter) corresponds to the joined table, why it does not belong to the join statement itself and is applied to the whole query, after the join operation is performed? Let me exemplify using your on GC example db:

        $crud->setTable('customers')
             ->setSubject('Customer', 'Customers')
             ->displayAs('customerName', 'Name')
             ->displayAs('contactLastName', 'Last Name')
             ->setRelation('salesRepEmployeeNumber', 'employees', 'lastName', 'officeCode > 0');

The query above produces the following SQL (MySQL DBMS), except that I’ve removed the pagination (LIMIT and OFFSET):

SELECT `customers`.`customerNumber` AS `customerNumber`,
       `customers`.`customerName` AS `customerName`,
       `customers`.`contactLastName` AS `contactLastName`,
       `customers`.`contactFirstName` AS `contactFirstName`,
       `customers`.`phone` AS `phone`,
       `customers`.`addressLine1` AS `addressLine1`,
       `customers`.`addressLine2` AS `addressLine2`,
       `customers`.`city` AS `city`,
       `customers`.`state` AS `state`,
       `customers`.`postalCode` AS `postalCode`,
       `customers`.`country` AS `country`,
       `customers`.`salesRepEmployeeNumber` AS `salesRepEmployeeNumber`,
       `customers`.`creditLimit` AS `creditLimit`,
       `customers`.`updatedBy` AS `updatedBy`
FROM `customers`
LEFT JOIN `employees` AS `e` ON `customers`.`salesRepEmployeeNumber` = `e`.`employeeNumber`
WHERE officeCode > 0;

Notice that the where clause in being added globally to the query, after the join. And, in this case, it even reduces the query results even-though all officeCode on table employees are positive and none of them is NULL. If used properly, this where clause should return the “correct” number of results (as if it was no where clause, please test check yourself the number of results returned in each of these 3 queries):

SELECT `customers`.`customerNumber` AS `customerNumber`,
       `customers`.`customerName` AS `customerName`,
       `customers`.`contactLastName` AS `contactLastName`,
       `customers`.`contactFirstName` AS `contactFirstName`,
       `customers`.`phone` AS `phone`,
       `customers`.`addressLine1` AS `addressLine1`,
       `customers`.`addressLine2` AS `addressLine2`,
       `customers`.`city` AS `city`,
       `customers`.`state` AS `state`,
       `customers`.`postalCode` AS `postalCode`,
       `customers`.`country` AS `country`,
       `customers`.`salesRepEmployeeNumber` AS `salesRepEmployeeNumber`,
       `customers`.`creditLimit` AS `creditLimit`,
       `customers`.`updatedBy` AS `updatedBy`
FROM `customers`
LEFT JOIN `employees` AS `e` ON `customers`.`salesRepEmployeeNumber` = `e`.`employeeNumber` AND officeCode > 0;

The result above is equal to the one below (without where):

SELECT `customers`.`customerNumber` AS `customerNumber`,
       `customers`.`customerName` AS `customerName`,
       `customers`.`contactLastName` AS `contactLastName`,
       `customers`.`contactFirstName` AS `contactFirstName`,
       `customers`.`phone` AS `phone`,
       `customers`.`addressLine1` AS `addressLine1`,
       `customers`.`addressLine2` AS `addressLine2`,
       `customers`.`city` AS `city`,
       `customers`.`state` AS `state`,
       `customers`.`postalCode` AS `postalCode`,
       `customers`.`country` AS `country`,
       `customers`.`salesRepEmployeeNumber` AS `salesRepEmployeeNumber`,
       `customers`.`creditLimit` AS `creditLimit`,
       `customers`.`updatedBy` AS `updatedBy`
FROM `customers`
LEFT JOIN `employees` AS `e` ON `customers`.`salesRepEmployeeNumber` = `e`.`employeeNumber` ;

Also, using the where clause like we are using right now imposes another issue: where clause is ambiguous. Mainly when using two where clauses on different setRelation calls. To exemplify I will add a virtual column to your customers table:

ALTER TABLE customers ADD COLUMN updatedBy INT GENERATED ALWAYS AS (salesRepEmployeeNumber) VIRTUAL;

        $crud->setTable('customers')
             ->setSubject('Customer', 'Customers')
             ->displayAs('customerName', 'Name')
             ->displayAs('contactLastName', 'Last Name')
             ->setRelation('salesRepEmployeeNumber', 'employees', 'lastName', 'officeCode > 0')
             ->setRelation('updatedBy', 'employees', 'firstName', 'officeCode > 0');

Unfortunately, the php code above will trigger a Internal Server Error because of SQL Error (1052): Column ‘officeCode’ in where clause is ambiguous . This is the resulting query:

SELECT `customers`.`customerNumber` AS `customerNumber`,
       `customers`.`customerName` AS `customerName`,
       `customers`.`contactLastName` AS `contactLastName`,
       `customers`.`contactFirstName` AS `contactFirstName`,
       `customers`.`phone` AS `phone`,
       `customers`.`addressLine1` AS `addressLine1`,
       `customers`.`addressLine2` AS `addressLine2`,
       `customers`.`city` AS `city`,
       `customers`.`state` AS `state`,
       `customers`.`postalCode` AS `postalCode`,
       `customers`.`country` AS `country`,
       `customers`.`salesRepEmployeeNumber` AS `salesRepEmployeeNumber`,
       `customers`.`creditLimit` AS `creditLimit`,
       `customers`.`updatedBy` AS `updatedBy`
FROM `customers`
LEFT JOIN `employees` AS `e` ON `customers`.`salesRepEmployeeNumber` = `e`.`employeeNumber`
LEFT JOIN `employees` AS `e__updatedBy` ON `customers`.`updatedBy` = `e__updatedBy`.`employeeNumber`
WHERE officeCode > 0
  AND officeCode > 0
LIMIT 10
OFFSET 0;

Notice the repeated where clause. I think the correct query should be:

SELECT `customers`.`customerNumber` AS `customerNumber`,
       `customers`.`customerName` AS `customerName`,
       `customers`.`contactLastName` AS `contactLastName`,
       `customers`.`contactFirstName` AS `contactFirstName`,
       `customers`.`phone` AS `phone`,
       `customers`.`addressLine1` AS `addressLine1`,
       `customers`.`addressLine2` AS `addressLine2`,
       `customers`.`city` AS `city`,
       `customers`.`state` AS `state`,
       `customers`.`postalCode` AS `postalCode`,
       `customers`.`country` AS `country`,
       `customers`.`salesRepEmployeeNumber` AS `salesRepEmployeeNumber`,
       `customers`.`creditLimit` AS `creditLimit`,
       `customers`.`updatedBy` AS `updatedBy`
FROM `customers`
LEFT JOIN `employees` AS `e` ON `customers`.`salesRepEmployeeNumber` = `e`.`employeeNumber` AND `e`.`officeCode` > 0
LEFT JOIN `employees` AS `e__updatedBy` ON `customers`.`updatedBy` = `e__updatedBy`.`employeeNumber` AND `e__updatedBy`.`officeCode` > 0
LIMIT 10
OFFSET 0;

Let me know what do you think about this…

Best regards!

2

Hello @bgeneto and thank you for the detailed description.

The reason that the code is applying the where statement after the join is on purpose. In short, this is in order to filter the results. It was an issue raised as a security concern that other fields were showing up which they shouldn’t. Maybe in the future I can have an implementation without it but what happens with the the datagrid columns that doesn’t have the data?

Also for the where statement as a string can be applied (and it suggested to do it this way) as also per where documentation where - Grocery CRUD - A PHP CRUD Generator Library (although not clear in the documentation). So in your case it would be something like this:

$crud->setRelation('salesRepEmployeeNumber', 'employees', 'lastName', 'e.officeCode > 0');

@bgeneto maybe what you are looking for is to have a query like this?

$crud->setRelation('country', 'countries', 'nicename', '(c.iso = "US" OR c.iso IS NULL)');

So it will include also the NULL entries?

@bgeneto also maybe to use the set-model for your case? setModel - Grocery CRUD - A PHP CRUD Generator Library.

For example in your case you can use a custom model that will look like this:

<?php

use GroceryCrud\Core\Model;

class CustomModel extends Model {
    /**
     * @param \Laminas\Db\Sql\Select $select
     * @return \Laminas\Db\Sql\Select
     */
    public function extraJoinStatements($select)
    {
        $select->join(
            ['e' => 'employees'], // Alias for employees table
            'customers.salesRepEmployeeNumber = e.employeeNumber AND e.officeCode > 0',
            [],
            $select::JOIN_LEFT
        );

        return $select;
    }

    /**
     * @param \Laminas\Db\Sql\Select $select
     * @return \Laminas\Db\Sql\Select
     */
    public function extraWhereStatements($select)
    {
        // No additional where conditions for this example
        return $select;
    }
}

What do you think?

3

My mainly concern was that using the WHERE clause globally will transform a LEFT JOIN into a
INNER JOIN, loosing valuable data in the grid. You can check for yourself, using your own database: there is no reason for a query (to the customers table) containing a line like this:

->setRelation('salesRepEmployeeNumber', 'employees', 'lastName', 'e.officeCode > 0');

returns less results than a query like this, without the where statement:

->setRelation('salesRepEmployeeNumber', 'employees', 'lastName');

At least in my understating… simply because I’ve ensured that all officeCode values are greater than zero (positive) and not NULL in the employees table. So it should return all rows from customers table, but that’s not the case (please test it).

Also, I can’t get rid of errors when setting two relations/joins using the same table (employees). I still couldn’t find a solution for this usage case:

        $crud->setTable('customers')
             ->setSubject('Customer', 'Customers')
             ->setRelation('salesRepEmployeeNumber', 'employees', 'lastName', 'e.officeCode > 0');
             ->setRelation('updatedBy', 'employees', 'firstName', 'e.officeCode > 0');

NOTE: To add the updatedBy column for replicating this error simply use:
ALTER TABLE customers ADD COLUMN updatedBy INT GENERATED ALWAYS AS
(salesRepEmployeeNumber) VIRTUAL;

Thanks one more time!