CodeCharge Studio
search Register Login  

Visual PHP Web Development

Visually Create Internationalized Web Applications, Web Reports, Calendars, and more.
CodeCharge.com

YesSoftware Forums -> CodeCharge Studio -> Tips & Solutions

 Convert Table query to SQL query and fix DISTINCT problems

Print topic Send  topic

Author Message
eratech


Posts: 506
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
View profile  Send private message
scarvello

Posts: 62
Posted: 02/18/2016, 3:49 AM

:-D Like
View profile  Send private message

Add new topic Subscribe to topic   


These are Community Forums for users to exchange information.
If you would like to obtain technical product help please visit http://support.yessoftware.com.

Web Database

Join thousands of Web developers who build Web applications with minimal coding.
CodeCharge.com

Home   |    Search   |    Members   |    Register   |    Login


Powered by UltraApps Forum created with CodeCharge Studio
Copyright 2003-2004 by UltraApps.com  and YesSoftware, Inc.