different sql result sets

stevencmon

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.

allan

Can you include a Field() for each table that you are joining please? I've got a feeling that is the issue.

Allan

stevencmon

Made the following changes with the same results.

Added Field() to include some data from the mapping tables:

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' ),
                Field::inst( 'crm_sites_accounts_1_c.crm_sites_accounts_1crm_sites_ida' ),
                Field::inst( 'accounts_cstm.flagship_c' )
        )
        ->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();

Query and Bindings:

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)', `crm_sites_accounts_1_c`.`crm_sites_accounts_1crm_sites_ida` as 'crm_sites_accounts_1_c.crm_sites_accounts_1crm_sites_ida', `accounts_cstm`.`flagship_c` as 'accounts_cstm.flagship_c' 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

allan

I'm honestly at a bit of a loss with this one.

So if you run:

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)',
    `crm_sites_accounts_1_c`.`crm_sites_accounts_1crm_sites_ida` as 'crm_sites_accounts_1_c.crm_sites_accounts_1crm_sites_ida',
    `accounts_cstm`.`flagship_c` as 'accounts_cstm.flagship_c'
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

how many records do you get?

Could you try dropping the two length functions? You could just use data.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

stevencmon

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.