join tables with two columns with id and one table -sql

saee2838

hi i want use join datatables
like this >>>> https://editor.datatables.net/examples/simple/join.html
but i have two column with diferent site ID
and want get site name from sites like attache image

HOW I CAN DO THIS WITH DATA TABLES?

colin

Hi @saee2838 ,

There'll be two components that you'll need to do - the Editor config in the Javascript and the server script. If you look at the example you linked to, you'll see both in the tabs below the table - it would just be a case of duplicating "users.site" to have a "users.site1" and a "users.site2" (if those are the table's column names that you're using).

Hope that helps,

Cheers,

Colin

saee2838

hi @colin
thanks for answer
i user this code but show error

server side

// 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;
 
 
$db->sql( "SET NAMES 'utf8'" );//very important for utf8 very important


$level_event=$_POST['level'];


Editor::inst( $db, 'sport_calendar_event_round_duration_dual' )
    ->where( 'event_id', $eventid )
    ->where( 'level_id', $level_event )
    ->field(
        Field::inst( 'sport_calendar_event_round_duration_dual.athlete1' )
            ->options( Options::inst()
                ->table( 'users' )
                ->value( 'id' )
                ->label( 'name' )
            )
            ->validator( Validate::dbValues() ),
        Field::inst( 'users.name' ),

        Field::inst( 'sport_calendar_event_round_duration_dual.athlete2' )
            ->options( Options::inst()
                ->table( 'users' )
                ->value( 'id' )
                ->label( 'name' )
            )
            ->validator( Validate::dbValues() ),
        Field::inst( 'users.name' ),
        Field::inst( 'sport_calendar_event_round_duration_dual.round1-1' ),
        Field::inst( 'sport_calendar_event_round_duration_dual.round2-1' ),
        Field::inst( 'sport_calendar_event_round_duration_dual.round3-1' ),
        Field::inst( 'sport_calendar_event_round_duration_dual.point1' ),
        Field::inst( 'sport_calendar_event_round_duration_dual.point2' ),
        Field::inst( 'sport_calendar_event_round_duration_dual.round1-2' ),
        Field::inst( 'sport_calendar_event_round_duration_dual.round2-2' ),
        Field::inst( 'sport_calendar_event_round_duration_dual.round3-2' )
    )
    ->leftJoin( 'users', 'users.id', '=', 'sport_calendar_event_round_duration_dual.athlete1' )
    ->leftJoin( 'users', 'users.id', '=', 'sport_calendar_event_round_duration_dual.athlete2' )
    ->process($_POST)
    ->json();

javascript

    


        var t=$('#example').DataTable( {
        dom: "Bflrtip",
        "lengthMenu": [[25, 50, -1], [25, 50, "همه"]],
        ajax: {
            url: "../remotefiles/event_users_duration_round_duel.php",
            type: "POST",
            "deferRender": true,
            dataType: "json",
            "data": {
                "eventid": {/literal}{$eventid}{literal},
                "level": {/literal}{$level}{literal}
            }
        },
        language: {
            "url": "//cdn.datatables.net/plug-ins/1.10.16/i18n/Persian.json",
            buttons: {
                copyTitle: 'اطلاعات در حافظه موقت کپی شد!',
                copyKeys: 'Use your keyboard or menu to select the copy command',
                copySuccess: {
                    1: "یک ردیف در حافظه موقت ذخیره شد",
                    _: "%d ردیف در حافظه موقت ذخیره شد"
                }
            }
        },
        columns: [
            { data: "users.name" },
            { data: "users.name" },
            { data: "sport_calendar_event_round_duration_dual.round1-1" ,orderable: false, className: 'center'},
            { data: "sport_calendar_event_round_duration_dual.round2-1" ,orderable: false, className: 'center'},
            { data: "sport_calendar_event_round_duration_dual.round3-1" ,orderable: false, className: 'center'},
            { data: "sport_calendar_event_round_duration_dual.point1" ,orderable: false, className: 'center'},
            { data: "sport_calendar_event_round_duration_dual.round1-2" ,orderable: false, className: 'center'},
            { data: "sport_calendar_event_round_duration_dual.round2-2" ,orderable: false, className: 'center'},
            { data: "sport_calendar_event_round_duration_dual.round3-2" ,orderable: false, className: 'center'},
            { data: "sport_calendar_event_round_duration_dual.point2" ,orderable: false, className: 'center'}
        ]
    } );
    


    
    
    

saee2838

this error
DataTables warning: table id=example - An SQL error occurred: SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias: 'users'

allan

Hi,

You need to use an alias. This example shows how to do that (although its not exactly the same setup as you have, its similar). So you might use:

>leftJoin( 'users as u1',

and update the relevant users to u1 and likewise for u2.

Allan

saee2838

thanks a lot @allan