eratech
Posts: 513
|
| Posted: 02/15/2016, 8:07 PM |
|
Something I ran into recently when attempting to change a Table query with a large number of WHERE options into a SQL type, which might be of use to others.
TL;DR
Added CustomCode into 'BeforeExecuteSelect' to replace the Table query 'SELECT' with 'SELECT DISTINCT':
$employees_organisations1->DataSource->SQL = str_replace("SELECT", "SELECT DISTINCT",$employees_organisations1->DataSource->SQL);
Long, detailed, boring version:
It is on an Advanced Search page and so can be searching a bunch of joined tables for stuff like names, bank account, addresses, email addresses, vehicle details etc.
It would return the Employee name and contact details (not all the other stuff) and you could click through to their details.
We had the situation where we got 2 rows whenever searching on fields like 'Vehicle Registration' and as you can guess,
the query was correct but also the culprit - the JOIN to Bank accounts was generous enough to return 2 records of the same ID as the employee had 2 bank accounts (or 3, or 4).
My usual solution when getting multiple records was to change the Query to 'SQL' type and change the 'SELECT' to 'SELECT DISTINCT' and move on.
SELECT DISTINCT first_name, last_name, date_of_birth, employees.suburb AS employees_suburb, email
, employees.id AS employees_id, end_date
, (case when (end_date < curdate()) then 'inactive' else 'active' end) AS class_active
FROM ((employees RIGHT JOIN organisations ON
employees.organisation_id = organisations.id) LEFT JOIN motor_vehicles ON
employees.id = motor_vehicles.employee_id) LEFT JOIN bank_accounts ON
employees.id = bank_accounts.employee_id
WHERE ( employees.first_name LIKE '{s_first_name}%'
AND employees.last_name LIKE '{s_last_name}%')
OR ( employees.suburb LIKE '%{s_employees_suburb}%'
OR organisations.suburb LIKE '%{s_employees_suburb}%' )
AND ( employees.email LIKE '%{s_email}%'
OR organisations.email_address LIKE '%{s_email}%' )
....
AND motor_vehicles.vehicle_registration LIKE '%{s_vehicle_rego}%'
This didn't work as the Query then started throwing a bunch of errors which didn't happen in Table mode.
After turning on the Debug mode (db_mysqli.php - around line 20) I found that Table mode leaves out any WHERE criteria without value parameters,
but the SQL version does all of them (as you would expect). This was the first time I had noticed this:
SELECT first_name, last_name, etcetc
...
FROM ((employees RIGHT JOIN organisations ON employees.organisation_id = organisations.id)
LEFT JOIN motor_vehicles ON employees.id = motor_vehicles.employee_id)
LEFT JOIN bank_accounts ON employees.id = bank_accounts.employee_id
WHERE motor_vehicles.vehicle_registration like '%123%' LIMIT 0,20
And as a quirk - the 'Count' query didn't like my fancy SQL query and be wrong AND would fail as well.
Solution Rather than edit (and break) the Prepare() statement, I added CustomCode into 'BeforeExecuteSelect' to replace the Table query 'SELECT' with 'SELECT DISTINCT':
$employees_organisations1->DataSource->SQL = str_replace("SELECT", "SELECT DISTINCT",$employees_organisations1->DataSource->SQL);
There you go - long story and simple solution.
Eric
_________________
CCS 3/4/5 ASP Classic, VB.NET, PHP
Melbourne, Victoria, Australia |
Like
