Editor Join within

Editor Join within

mdesignmdesign Posts: 27Questions: 7Answers: 0

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();

Sign In or Register to comment.