Join where

shantiram

Hi, first of all I want to say I am really impressed by this product.

I was wondering if it's possible to create an Editor instance from a JOIN/WHERE query like this:

sql = 'SELECT first_name, last_name, email, phone, enrolment.enroldate, enrolment.price FROM students INNER JOIN enrolment on students.id = enrolment.student_id WHERE enrolment.course_id = " . '$q' . '"'

rf1234

sure, you could use ajax like in this example:
https://datatables.net/examples/data_sources/ajax.html

shantiram

From what I understand the example you posted does not create an editable table. I would like to edit the table.

rf1234

what are you using on the servers side? If you are using PHP or .NET I recommend using the respective Editor libraries. As far as I know Editor does not support an inner join but you should be able to get to the same result using a left join as well.
https://editor.datatables.net/examples/advanced/joinLinkTable.html

You can't have an editable table with just this one SQL-statement you provided. You would need INSERT, UPDATE and DELETE statements as well. That is what Editor automatically generates for you if you are using the PHP or .NET library.

shantiram

Thank you. I managed to modify the join.php and join.html to create a left join on the tables I wanted.

I can see there is an option to include WHERE clause. But I don't quite understand how to use it.

Would be super grateful for any hints or help.

This is the code I have so far:

<?php

// DataTables PHP library
include( "../../php/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;


/*
 * Example PHP implementation used for the join.html example
 */

Editor::inst( $db, 'student' )
    ->field( 
        Field::inst( 'student.first_name' ),
        Field::inst( 'student.last_name' ),
        Field::inst( 'student.phone' ),
        Field::inst( 'student.email' ),
        Field::inst( 'student.id' )
            ->options( Options::inst()
                ->table( 'sites' )
                ->value( 'id' )
                ->label( 'name' )
            )
            ->validator( 'Validate::dbValues' ),
        Field::inst( 'enrolment.enroldate' )
    )
    ->leftJoin( 'enrolment', 'enrolment.student_id', '=', 'student.id' )
    
    ->process($_POST)
    ->json();

rf1234

Did you see this? https://editor.datatables.net/manual/php/conditions

Here are a couple of examples from my own coding:

a very complex where clause:

Editor::inst( $db, 'user' )
->field(
    Field::inst( 'user.id' )->set( false ),
    Field::inst( 'user.title' )->validator( 'Validate::notEmpty', array('message' => $msg[0]) ),
    Field::inst( 'user.acad' ),
    Field::inst( 'user.firstname' )->validator( 'Validate::notEmpty', array('message' => $msg[0]) ),
    Field::inst( 'user.lastname' )->validator( 'Validate::notEmpty', array('message' => $msg[0]) ),
    Field::inst( 'user.language' )->validator( 'Validate::notEmpty', array('message' => $msg[0]) ),
    Field::inst( 'user.type' )->validator( 'Validate::notEmpty', array('message' => $msg[0]) ),
    Field::inst( 'user.email' )->validator( 'Validate::email', array(
                                            'required' => true,
                                            'message' => $msg[1]) ),
    Field::inst( 'user.updater_id' )->set(Field::SET_BOTH)
                                    ->setValue($currentUser),
    Field::inst( 'user.creator_id' )->set(Field::SET_CREATE)
                                    ->setValue($currentUser)    
)
//    show user roles        
->join(
    Mjoin::inst( 'govdept' )
        ->link( 'user.id', 'govdept_has_user.user_id' )
        ->link( 'govdept.id', 'govdept_has_user.govdept_id' )
        ->order( 'govdept.name asc' )
        ->fields(
            Field::inst( 'govdept.name AS deptName' )->set( false ),
            Field::inst( 'govdept_has_user.role AS userRole' )->set( false )
        )
    )
->join(
    Mjoin::inst( 'creditor' )
        ->link( 'user.id', 'creditor_has_user.user_id' )
        ->link( 'creditor.id', 'creditor_has_user.creditor_id' )
        ->order( 'creditor.name asc' )
        ->fields(
            Field::inst( 'creditor.name AS deptName' )->set( false ),
            Field::inst( 'creditor_has_user.role AS userRole' )->set( false )
        )
    )
->join(
    Mjoin::inst( 'lgf' )
        ->link( 'user.id', 'lgf.user_id' )
        ->fields(
            Field::inst( 'lgf.role AS userRole' )->set( false )
        )
    )
->where( function ( $q ) {
        if ( isset($_SESSION['settingsId']) ) {
            $idLower = $_SESSION['settingsId'];
            $idHigher = $_SESSION['settingsId'];
        } else {
            $idLower = 0;
            $idHigher = 999999999999999999999;
        }
        if (! isset($_SESSION['masterDataId']) ) {
            $q  ->where( 'user.id', $idLower, '>=' );
            $q  ->where( 'user.id', $idHigher, '<=' );
        } else {
            $q  ->where('user.id',  $_SESSION['masterDataId'], '!=' ); 
            $q  ->where( function ( $r ) { 
                $r  ->where( 'user.id',  
                    '( SELECT DISTINCT user.id  
                        FROM user, govdept_has_user  
                        WHERE govdept_has_user.govdept_id IN  
                        ( SELECT DISTINCT govdept_id FROM govdept_has_user     
                          WHERE user_id = :id AND role IN ("Administrator", "Principal" ) ) AND  
                        govdept_has_user.user_id = user.id  
                        ORDER BY user.id ASC  
                        )', 'IN', false);                
                $r  ->or_where( 'user.id',  
                    '( SELECT DISTINCT user.id  
                        FROM user, creditor_has_user  
                        WHERE creditor_has_user.creditor_id IN   
                        ( SELECT DISTINCT creditor_id FROM creditor_has_user     
                          WHERE user_id = :id AND role IN ("Administrator", "Principal" ) ) AND  
                        creditor_has_user.user_id = user.id  
                        ORDER BY user.id ASC  
                        )', 'IN', false);
                $r  ->or_where( 'user.id',  
                    '( SELECT DISTINCT user.id  
                        FROM user, lgf  
                        WHERE user.id IN   
                        ( SELECT DISTINCT user_id FROM lgf ) AND   
                        lgf.user_id = :id   AND    
                        lgf.role IN ("Administrator", "Principal" )  
                        ORDER BY user.id ASC  
                        )', 'IN', false);
                //you can also see users that you have created and/or updated last time
                $r  ->or_where( 'user.updater_id', $_SESSION['masterDataId'], '=' );
                $r  ->bind( ':id', $_SESSION['masterDataId'] );
            } );
        }
    } )
->on( 'postCreate', function ( $editor, $id, $values, $row ) {
    logChange( $editor->db(), 'create', $id, $row, 'user' );
} )
->on( 'postEdit', function ( $editor, $id, $values, $row ) {
    logChange( $editor->db(), 'edit', $id, $row, 'user' );
} )
->on( 'postRemove', function ( $editor, $id, $values ) {
    logChange( $editor->db(), 'delete', $id, $values, 'user' );
} )
->process($_POST)
->json();

a simple where clause:

Editor::inst( $db, 'govdept_has_user' )
->field(
    Field::inst( 'govdept_has_user.govdept_id' )->set( false ),
    Field::inst( 'govdept.gov_id' )->set( false ),
    Field::inst( 'govdept.name' )->set( false ),
    Field::inst( 'gov.name' )->set( false ),
    Field::inst( 'gov.regional_8' )->set( false ),
    Field::inst( 'gov.regional_12' )->set( false ),
    Field::inst( 'govdept_has_user.role' )->set( false ),
    Field::inst( 'govdept.approvals_rfp' )->set( false ),
    Field::inst( 'govdept.approvals_contract' )->set( false ),
    Field::inst( 'govdept.approvals_contract AS countUnread' )->set( false )
        ->getFormatter( function($val, $data, $opts)  {
            return 0;
        })
)
->leftJoin( 'govdept', 'govdept.id', '=', 'govdept_has_user.govdept_id' )
->leftJoin( 'gov', 'gov.id', '=', 'govdept.gov_id' )
->where( 'govdept_has_user.user_id', $_SESSION['id'] )
->process($_POST)
->json();

and one more ....

Editor::inst( $db, 'govdept_has_user' )
->field(
    Field::inst( 'govdept_has_user.govdept_id' )->set( false ),
    Field::inst( 'govdept.gov_id' )->set( false ),
    Field::inst( 'govdept.name' )->set( false ),
    Field::inst( 'gov.name' )->set( false ),
    Field::inst( 'gov.regional_8' )->set( false ),
    Field::inst( 'gov.regional_12' )->set( false ),
    Field::inst( 'govdept_has_user.role' )->set( false ),
    Field::inst( 'govdept.approvals_rfp' )->set( false ),
    Field::inst( 'govdept.approvals_contract' )->set( false ),
    Field::inst( 'govdept.approvals_contract AS countUnread' )->set( false )
        ->getFormatter( function($val, $data, $opts) use ( $countUnread ){
            switch ($countUnread) {
                case "noCounting":
                    return 0;
                    break;
                case "inboxOffer":
                    return countOfferGov
                        ( $data['govdept.gov_id'],
                          $data['govdept_has_user.govdept_id'] );
                    break;
                case "inboxProposal":
                    return countProposalGov
                        ( $data['govdept_has_user.govdept_id'] );
                    break;
                case "inboxContract":
                    return countContractGov
                        ( $data['govdept_has_user.govdept_id'] );
                    break;
                case "inboxExp":
                    return countExpGov
                        ( $data['govdept_has_user.govdept_id'] );
                    break;
            }
        })
)
->leftJoin( 'govdept', 'govdept.id', '=', 'govdept_has_user.govdept_id' )
->leftJoin( 'gov', 'gov.id', '=', 'govdept.gov_id' )
->where( function ( $q ) {
    $q->where( 'govdept_has_user.user_id', $_SESSION['id'] );
    $q->where( 'govdept_has_user.role', '("Administrator", "Principal", "Editor")', 'IN', false );
} )
//    ->debug(true)
->process($_POST)
->json();