DataTables - Getting duplicate records when using LEFT JOIN and mm table

Capamania

I'm using https://editor.datatables.net/ and when doing a left join like this:

accounts.php:

    Editor::inst( $db, 'accounts', 'id' )
    ->fields(
        Field::inst( 'accounts.id' ),
        Field::inst( 'accounts.abc' ),  
        Field::inst( 'accounts.name' ),
        Field::inst( 'accounts_mm.user_id' )                            
    )           

    ->leftJoin( 'accounts_mm', 'accounts_mm.abc', '=', 'accounts.abc' )
    
    ->process( $_POST )
    ->json();

I'm getting duplicates in my main table which I call like that

js:

    var table = $('#my_table').DataTable({
        dom: "Blrtip",
        ajax: {
              url: "/php/accounts.php",
              type: "POST"
        },
        serverSide: true,
        processing: true,
        columns: [
        ...
        ]                        
    }) ;

... when the joined table has multiple relational records:

Table: accounts_mm

    (`id`, `user_id`, `abc`, `name`)
    (1, '4', 'ABC01', 'Company 01'),
    (2, '4', 'ABC02', 'Company 02'),
    (3, '4', 'ABC03', 'Company 03'),
    (4, '4', 'ABC04', 'Company 04'),
    (5, '1', 'ABC01', 'Company 02'),
    (6, '1', 'ABC02', 'Company 03'),
    (7, '1', 'ABC03', 'Company 01'),
    (8, '1', 'ABC04', 'Company 04');

The main table looks like this:

Table accounts:

    (`id`, `abc`, `name`)
    (1, 'ABC01', 'Company 01'),
    (2, 'ABC02', 'Company 02'),
    (3, 'ABC03', 'Company 03'),
    (4, 'ABC04', 'Company 04');

... and the result in the frontend should be ...

    (`id`, `abc`, `name`)
    (1, 'ABC01', 'Company 01'),
    (2, 'ABC02', 'Company 02'),
    (3, 'ABC03', 'Company 03'),
    (4, 'ABC04', 'Company 04');

... but I'm getting:

    (`id`, `abc`, `name`)
    (1, 'ABC01', 'Company 01'),
    (2, 'ABC02', 'Company 02'),
    (3, 'ABC03', 'Company 03'),
    (4, 'ABC04', 'Company 04'),
    (5, 'ABC01', 'Company 02'),
    (6, 'ABC02', 'Company 03'),
    (7, 'ABC03', 'Company 01'),
    (8, 'ABC04', 'Company 04'); 

I'm using the accounts_mm to multiselect the main table accounts.

Any idea how I can avoid the duplicates when the main table is unfiltered?

Capamania

What I want to achieve is:

Starting from the main table 'accounts' ... a registered user ('user_id') can add a record to their busket 'accounts_mm'. A single record can be added to the busket once by each user. But consequently, a specific record can be muliple times in 'accounts_mm' with different 'user_id'. In addition the main table 'accounts' has a filter capability ... where the registered user can filter only the records in their busket (which works fine and gets displayed correctly) ... but also 'unfilter' and see the whole universe of the main table. But then the unfiltered table shows multiple records depending on the count of this record in 'accounts_mm'. How should my set up look like to achieve my scenario?

Capamania

Is maybe this post about the same issue? https://datatables.net/forums/discussion/38214/select-distinct-for-editor

Capamania

Hi Allan ... adding ->distinct(true) as described https://datatables.net/forums/discussion/38214/select-distinct-for-editor solved the issue. Could you implement this in Editor? Many thanks!

Capamania

An issue that remains is that the 'total number of records' info of the main table 'accounts' increases by the number of duplicates in accounts_mm ...

allan

The join and data you describe above is one-to-many, so you would need to use an Mjoin to have that correctly displayed. Using distinct here would not be the correct solution (although it might appear to give the required results initially).

Another option would be to restrict the join to be one-to-one using a more complex join expression - specifically including the abc field above if I've understood the data above. This thread discusses how more complex joins can be done.

Allan

Capamania

But when using a Mjoin like this:

    ->join(
        Mjoin::inst( 'accounts_mm' )
            ->link( 'accounts_mm.lei', 'accounts.lei' )
            ->fields(
                Field::inst( 'user_id' )
            )         
    )

JSON returned:

data    […]
    0   {…}
        DT_RowId    row_328741
        accounts    {…}
        accounts_mm […]
            0   {…}
                user_id 4
            1   {…}
                user_id 1   

The filter is not working:

error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'accounts_mm.user_id' in 'where clause'

Debug:

debugSql    […]
    0   {…}
        query   SELECT COUNT(id) as 'cnt' FROM `accounts` WHERE (`accounts_mm`.`user_id` = :where_1 )
        bindings    […]
            0   
                name    :where_1
                value   1
                type    null

How do I filter an Mjoin table?

allan

What we need to clarify first is if this is a one-to-one relationship, or a one-to-many. If one-to-one (which I think it is from your original post) then you would need to use a leftJoin with a more complex join condition, which is explained in the thread I linked to above.

Allan

Capamania

What seems to work is when I do this:

    ->leftJoin( 'accounts_mm',
        'accounts_mm.abc', '=',
        'accounts.abc AND (accounts_mm.user_id = 1)'
    )

But if I want to pass in a variable like

    $user_id = 1;       

    ->leftJoin( 'accounts_mm',
        'accounts_mm.abc', '=',
        'accounts.abc AND (accounts_mm.user_id = '.$user_id.')'
    )

I get a DataTable warning ...
What also doesn't work is when I do this:

    ->leftJoin( 'accounts_mm',
        'accounts_mm.abc', '=',
        'accounts.abc AND (accounts_mm.user_id = accounts_mm.user_id)'
    )

How should this complex expression look like? Can you provide a solution ?

allan

I get a DataTable warning

What warning please? What is the response from the server?

Capamania

All I get is window pop up saying 'DataTables warning: table id=my_table - Ajax error. For more information about this error, please see http://datatables.net/tn/7'.

Response in the console is empty ... all I get is ...

[HTTP/1.1 500 Internal Server Error 42ms]

... although I have ->debug( true )

What else can I do?

allan

Have a look at the server's error log if there is a 500 internal error.

Allan

Capamania

Unfortunately I can't find and don't see anything relevant there ...

Capamania

Ok ... what works is if do it like this (like in production):

->leftJoin( 'accounts_mm', 'accounts_mm.abc', '=', 'accounts.lei AND (accounts_mm.abc = '.$_POST["inputUserID"].')')

... what is not working is if I do it like this (like when testing):

$user_id = 1;      
->leftJoin( 'accounts_mm', 'accounts_mm.abc', '=', 'accounts.abc AND (accounts_mm.user_id = '.$user_id.')')

allan

Try adding:

error_reporting(E_ALL);
ini_set('display_errors', '1');

at the top of the script (just inside the <?php tag).

Allan