different sql result sets
different sql result sets
stevencmon
Posts: 25Questions: 9Answers: 2
When I run this server script (php) I get a result set having a size of 2,970 rows.
<?php
/*
* Editor server script for DB table accounts
* Created by http://editor.datatables.net/generator
*/
// DataTables PHP library and database connection
include( "lib/DataTables.php" );
// Alias Editor classes so they are easy to use
use
DataTables\Editor,
DataTables\Editor\Field,
DataTables\Editor\Format,
DataTables\Editor\Mjoin,
DataTables\Editor\Options,
DataTables\Editor\Upload,
DataTables\Editor\Validate,
DataTables\Editor\ValidateOptions;
// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'accounts', 'id' )
->fields(
Field::inst( 'accounts.name' ),
Field::inst( 'LENGTH( accounts.name) AS accounts.name_length' ),
Field::inst( 'crm_sites.name' ),
Field::inst( 'LENGTH( crm_sites.name) AS crm_sites.name_length' )
)
->leftJoin( 'accounts_cstm', 'accounts.id', '=', 'accounts_cstm.id_c' )
->leftJoin( 'crm_sites_accounts_1_c', 'accounts.id', '=', 'crm_sites_accounts_1_c.crm_sites_accounts_1accounts_idb' )
->leftJoin( 'crm_sites', 'crm_sites.id', '=', 'crm_sites_accounts_1_c.crm_sites_accounts_1crm_sites_ida' )
->where( 'accounts.name', 'crm_sites.name', '!=', false )
->where( 'accounts.deleted', 0, '=' )
->where( 'crm_sites.deleted', 0, '=' )
->where( 'crm_sites_accounts_1_c.deleted', 0, '=' )
->debug( true )
->process( $_POST )
->json();
The sql query and bindings generated by the Editor are:
0 {…}
query SELECT `accounts`.`id` as 'accounts.id', `accounts`.`name` as 'accounts.name', LENGTH( accounts.name) as 'LENGTH( accounts.name)', `crm_sites`.`name` as 'crm_sites.name', LENGTH( crm_sites.name) as 'LENGTH( crm_sites.name)' FROM `accounts` LEFT JOIN `accounts_cstm` ON `accounts`.`id` = `accounts_cstm`.`id_c` LEFT JOIN `crm_sites_accounts_1_c` ON `accounts`.`id` = `crm_sites_accounts_1_c`.`crm_sites_accounts_1accounts_idb` LEFT JOIN `crm_sites` ON `crm_sites`.`id` = `crm_sites_accounts_1_c`.`crm_sites_accounts_1crm_sites_ida` WHERE `accounts`.`name` != :where_0 AND `accounts`.`deleted` = :where_1 AND `crm_sites`.`deleted` = :where_2 AND `crm_sites_accounts_1_c`.`deleted` = :where_3
bindings […]
0
name :where_0
value crm_sites.name
type null
1 {…}
name :where_1
value 0
type null
2 {…}
name :where_2
value 0
type null
3 {…}
name :where_3
value 0
type null
When I copy the query and replace the :where_[0-3] with the appropriate values I get the following query:
SELECT `accounts`.`id` as 'accounts.id', `accounts`.`name` as 'accounts.name', LENGTH( accounts.name) as 'LENGTH( accounts.name)', `crm_sites`.`name` as 'crm_sites.name', LENGTH( crm_sites.name) as 'LENGTH( crm_sites.name)' FROM `accounts` LEFT JOIN `accounts_cstm` ON `accounts`.`id` = `accounts_cstm`.`id_c` LEFT JOIN `crm_sites_accounts_1_c` ON `accounts`.`id` = `crm_sites_accounts_1_c`.`crm_sites_accounts_1accounts_idb` LEFT JOIN `crm_sites` ON `crm_sites`.`id` = `crm_sites_accounts_1_c`.`crm_sites_accounts_1crm_sites_ida` WHERE `accounts`.`name` != `crm_sites`.`name` AND `accounts`.`deleted` = 0 AND `crm_sites`.`deleted` = 0 AND `crm_sites_accounts_1_c`.`deleted` = 0 ;
When I run this I get a result set of 89 rows (what I was expecting).
What am I missing.
The query is joining two tables with a map table and is comparing the 'name' fields and should return only those that do not match.
This discussion has been closed.
Answers
Can you include a
Field()
for each table that you are joining please? I've got a feeling that is the issue.Allan
Made the following changes with the same results.
Added Field() to include some data from the mapping tables:
Query and Bindings:
I'm honestly at a bit of a loss with this one.
So if you run:
how many records do you get?
Could you try dropping the two
length
functions? You could just usedata.length
in a renderer in Javascript if you need to display that in the table (I doubt that is the issue, but let's try it anyway!).Allan
I get 92 rows.
I tried dropping the LENGTH() fields and also all of the references to the accounts_cstm table as I wasn't really using any of that data (sort of debug out on the LENGTH as I wanted to see about leading/trailing whitespace.)
I've ended up putting the query inside a view (I don't really want them to edit her but I 'href'ed to the system specific page to edit the data.)
It's interesting because I've done MANY joins of this variety, in this database, of this type. Weird. Will post a follow up if I do have time to pursue.