"Left Join" is associated with a table where there is a "WHERE"

ostmal

Hello there. I would be very grateful for your help.
I need to make a "Left Join" with a filtered table, i.e. with a table where there is a "WHERE":

SELECT xxx_aaa_plan.date_create, REPORT.ocr FROM xxx_aaa_plan
LEFT JOIN ( SELECT * FROM xxx_aaa_report WHERE okr = 14) AS REPORT
ON REPORT.id_plan = xxx_aaa_plan.id
The result is:
https://prnt.sc/sjjd0t

Trying to implement this in Datatables: Test example

It doesn't work! Obviously, the second column should display either "14" or empty.
I don't understand what I'm doing wrong.

HTML:

<thead>
                    <tr>
                        <th>date_create</th>
                        <th>okr</th>
                    </tr>
                </thead>

JS:

(function($){

$(document).ready(function() {
    var editor = new $.fn.dataTable.Editor( {
        ajax: {
            url: '/abc/dt/my/plan_user/plan_user.php',
            type: 'POST',
            data: {
                okr : 14
            }
        },
        table: '#id_plan_user',
        fields: [
            // {
            // ....
            // }
        ]
    } );

    var table = $('#id_plan_user').DataTable( {
        dom: 'fBrltip',
        paging: false,
        ajax: {
            url: '/abc/dt/my/plan_user/plan_user.php',
            type: 'POST',
            data: {
                okr : 14
            }
        },
        columns: [
            { data: "xxx_aaa_plan.date_create" },
            { data: "xxx_aaa_report.okr" }
        ],

        select: true,   // Выделять строки по одиночному клику
        lengthChange: false,
        buttons: [
        ],

    } );
} );
}(jQuery));

PHP:

<?php
$okr = $_POST['okr'];

// DataTables PHP library and database connection
include( "php/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, 'xxx_aaa_plan', 'id' )
    ->fields(
        Field::inst( 'xxx_aaa_plan.date_create' ),
        Field::inst( 'xxx_aaa_plan.id' )
            ->options( Options::inst()
                ->table( 'xxx_aaa_report' )
                ->value( 'id_plan' )
                ->label( 'okr' )
                ->where( function ($q) use ( $okr ) {
                    $q->where( 'okr', $okr, '=' );
                })

            ),
        Field::inst( 'xxx_aaa_report.okr' )
            -> setValue($okr)
    )
    ->leftJoin( 'xxx_aaa_report', 'xxx_aaa_report.id_plan', '=', 'xxx_aaa_plan.id' )
    ->process( $_POST )
    ->json();

ostmal

Sorry, access to the test example was blocked. I have now opened access.

ostmal

Find it:
https://datatables.net//forums/discussion/comment/81166/#Comment_81166

->leftJoin( 'xxx_aaa_report', 'xxx_aaa_report.id_plan', '=', 'xxx_aaa_plan.id AND xxx_aaa_report.okr = 14' )

rf1234

This:

SELECT xxx_aaa_plan.date_create, 
       REPORT.okr FROM xxx_aaa_plan
LEFT JOIN ( SELECT * FROM xxx_aaa_report WHERE okr = 14) AS REPORT
    ON REPORT.id_plan = xxx_aaa_plan.id;

could be written like this:

SELECT a.date_create,
       b.okr 
  FROM xxx_aaa_plan a
LEFT JOIN xxx_aaa_report b ON a.plan_id = b.id_plan
WHERE b.okr = 14

Don't know what you need the options instance for server side. I don't see you using it at the front end. The "->setValue" also looks wrong. Sorry. I just ignore both. They are both irrelevant to make the leftJoin work anyway. Please note: The "WHERE" clause of an options instance is irrelevant for your query; it only applies to the options instance, not to anythings else.
To give you an advice on these I would need to understand what you are trying to achieve in business terms and what your data model looks like in more detail (E/R diagram).

So this is the code to make your leftJoin work. If you want an INNER JOIN instead take a look at the commented lines in the WHERE clause.

// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'xxx_aaa_plan', 'id' )
    ->fields(
        Field::inst( 'xxx_aaa_plan.date_create' ),
        Field::inst( 'xxx_aaa_report.okr' )
    )
    ->leftJoin( 'xxx_aaa_report', 'xxx_aaa_report.id_plan', '=', 'xxx_aaa_plan.id' )
    ->where( function ( $q ) {        
          $q ->where( 'xxx_aaa_report.okr', $_POST['okr'] );
 // if you want to do an INNER JOIN: also check for NOT IS NULL:
          // $q ->where( 'xxx_aaa_report.okr', null, '!=' );
      } )
    ->process( $_POST )
    ->json();

allan

Yup - that's a good workaround. Thanks for posting back and @rf1234 for the details!

Allan