Editor Join within

mdesign

i try to admit sport-matches with editor. therefore i have 3 tables in the database. to keep it simple, i only describe the one we need within this example.

Or you see the live demo
http://diddyness.com/thri11ertour.000/-admin/editor-next

by editing the id's of player 1 and 2 are correctly saved to the field tbl_next.next_pl1 and tbl_next.next_pl2

but:
displaying the datatable gives me always only the name of the pl1

1) player infos

tbl_user
    user_vid (id)
    user_vnm (first name)
    user_nnm (last name)

2) tournaments infos

tbl_date
    date_idn (id)
    date_tit

3) matches

tbl_next
    date_idn
    next_dup (date of match)
    next_pl1 (user_vid of Player 1)
    next_pl2 (user_vid of Player 2)

Now I want to choose two opponents by dropdown, who will compete.

this is the ssp-file:

include_once(nicaABSTMPL . '/comp/-datatables/dt-extensions/editor/1.8.1/lib/DataTables.php');
use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate,
    DataTables\Editor\ValidateOptions;

/* -------------------------------------------------------------------------- *
   Variablen                                                       (optional)
 * -------------------------------------------------------------------------- */
$date_sql = 'Y-m-d H:i:s';  // 2019-12-31 23:59:00  siehe php.net/manual/de/function.date
$date_001 = 'd/m/Y - H:i';  // 31/12/2019 - 12:12
$date_003 = 'd/m/Y';        // 31/12/2019


$db->sql('set names utf8');
$editor = Editor::inst(
  $db, 'thri_next', 
       'thri_next.next_idn' /* Primary Key */
  )
    ->fields(
    Field::inst('thri_user.user_nnm'),
    Field::inst('thri_user.user_vnm'),
    Field::inst('thri_user.user_vid'),
    Field::inst('thri_next.webx_idn'),
    Field::inst('thri_next.next_idn'),
    Field::inst('thri_next.next_rnd'),
    Field::inst('thri_next.next_dup')
      ->validator(Validate::dateFormat($date_001))
      ->getFormatter(Format::datetime( $date_sql, $date_001))
      ->setFormatter(Format::datetime( $date_001, $date_sql)),

    Field::inst('thri_next.next_pl1')   // [join] thri_sign.user_idn <=> thri_user.user_nnm
            ->options(Options::inst()
                ->table('thri_user')
                ->value('user_vid')     // schreibt die user_vid des gewählten Namens in das Feld next_pl1
                ->label(array('user_nnm', 'user_vnm'))
            )
            ->validator( Validate::dbValues() ),

    Field::inst('thri_next.next_pl2')   // [join] thri_sign.user_idn <=> thri_user.user_nnm
            ->options(Options::inst()
                ->table('thri_user')
                ->value('user_vid')     // schreibt die user_vid des gewählten Namens in das Feld next_pl1
                ->label(array('user_nnm', 'user_vnm'))
            )
            ->validator( Validate::dbValues() ),

    Field::inst('thri_date.date_tit'),
    Field::inst('thri_next.date_idn')   // [join] thri_next.date_idn <=> thri_date.date_tit
        ->options(Options::inst()
            ->table('thri_date')
            ->value('date_idn')
            ->label('date_tit')
        )
        ->validator( Validate::dbValues() ),

    /* letztes Field Update mySql Timestamp */
    Field::inst('thri_next.next_upd')->setValue(date($date_sql, time())) // kein ',' am Ende
    )
    ->leftJoin( 'thri_date', 'thri_date.date_idn', '=', 'thri_next.date_idn' )
  ->leftJoin( 'thri_user', 'thri_user.user_vid', '=', 'thri_next.next_pl1' )
//->leftJoin( 'thri_user', 'thri_user.user_vid', '=', 'thri_next.next_pl2' )
//  ->leftJoin( 'thri_user as user_nnm', 'thri_user.user_vid','=', 'thri_next.next_pl1')
//  ->leftJoin( 'thri_user as user_nnm', 'thri_user.user_vid','=', 'thri_next.next_pl2')
    ->process( $_POST )
    ->json();

Edited by Allan - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

allan

You need to do:

->leftJoin( 'thri_user as user1', ...
->leftJoin( 'thri_user as user2',  ...

Then add a Field for each of user1.user_nnm' anduser2.user_nnm` (and any other fields you want).

Allan