different sql result sets

different sql result sets

stevencmonstevencmon 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.

Answers

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

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

    Allan

  • stevencmonstevencmon Posts: 25Questions: 9Answers: 2

    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
    
  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin
    edited July 2019

    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

  • stevencmonstevencmon Posts: 25Questions: 9Answers: 2

    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.

This discussion has been closed.