Editor: Problem with leftJoin

Editor: Problem with leftJoin

mdesignmdesign Posts: 70Questions: 16Answers: 0
edited July 2019 in Editor

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

Answers

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406
    edited July 2019

    I got this from your java script:

    // Spieler 1
            { data: null, 
              render: function (data, type, row) { 
                        return '<b class="-text-uppercase">' + data.thri_user.user_nnm + '</b><br>' + data.thri_user.user_vnm + '<br>' + data.thri_next.next_pl1; },
              editField: 'thri_next.next_pl1',
            },
    
            // Spieler 2
            { data: null, 
              render: function (data, type, row) { 
                        return '<b class="-text-uppercase">' + data.thri_user.user_nnm + '</b><br>' + data.thri_user.user_vnm + '<br>' + data.thri_next.next_pl2; },
              editField: 'thri_next.next_pl2', // [join] thri_sign.user_idn <=> thri_user.user_nnm 
            },
    

    you are using identical fields for player 1 and player 2 except for their id. Hence the same information is displayed in those data table fields except for the id ...

    I tried to rearrange your PHP code to make it legible. Please use markdown as explained below (three ticks).

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

    instead of just reading first and last name once you will need to read it twice by joining with the correct table.

    Try this (PHP):

    $editor = Editor::inst(
            $db, 'thri_next',
            'thri_next.next_idn' /* Primary Key */
        )
    ->fields(
        Field::inst('player1.user_nnm'),
        Field::inst('player1.user_vnm'),
        Field::inst('player2.user_nnm'),
        Field::inst('player2.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 as player1', 'player1.user_vid', '=', 'thri_next.next_pl1' )
    ->leftJoin( 'thri_user as player2', 'player2.user_vid', '=', 'thri_next.next_pl2' )
    ->process( $_POST )
    ->json();
    

    It's called a self referencing join: https://editor.datatables.net/examples/advanced/joinSelf

    Javascript:

    // Spieler 1
            { data: null, 
              render: function (data, type, row) { 
                        return '<b class="-text-uppercase">' + data.player1.user_nnm + '</b><br>' + data.player1.user_vnm + '<br>' + data.thri_next.next_pl1; },
              editField: 'thri_next.next_pl1',
            },
    
            // Spieler 2
            { data: null, 
              render: function (data, type, row) { 
                        return '<b class="-text-uppercase">' + data.thri_user.user_nnm + '</b><br>' + data.thri_user.user_vnm + '<br>' + data.thri_next.next_pl2; },
              editField: 'thri_next.next_pl2', // [join] thri_sign.user_idn <=> thri_user.user_nnm 
            },
    
  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    As your commented out code suggests, you need an as statement. These two lines look okay, minus that you are aliasing them to the same name:

    // ->leftJoin( 'thri_user as user_1', 'user_1.user_vid','=', 'thri_next.next_pl1')
    // ->leftJoin( 'thri_user as user_2', 'user_2.user_vid','=', 'thri_next.next_pl2')
    

    You just need to add user_1.{fieldName} to your list of fields, and likewise for user_2.

    Allan

  • mdesignmdesign Posts: 70Questions: 16Answers: 0

    thx a lot to you both. i copied the code from you rf1234 but get now: "Unknown column 'thri_user.user_vid' in 'field list'"

    http://diddyness.com/thri11ertour.000/-admin/editor-next

    ps: how can i do markdown ? i don't get it.

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406
    edited July 2019

    you get the error because you are no longer joining to table thri_user but you are joining to the aliases player1 and player2. I don't think it makes sense to have it in the field list anyway because you are already selecting the details of player1 and player2. So if you can remove "thri_user.user_vid" from the field list of the Editor instance and check your Java Script accordingly this would be best.

    If you don't find the back ticks on the keyboard just copy & paste them from below.

  • mdesignmdesign Posts: 70Questions: 16Answers: 0

    thx rf1234 - we are so close but i can get the final result.

    by removing Field::inst('thri_user.user_vid') the datatable does endless processing, without any error and result. i need the user_vid by editing here: how can i achive this ?

          Field::inst('thri_next.next_pl1') // => leftJoin
              ->options(Options::inst()
                          ->table('thri_user')
                          ->value('user_vid') // writes user_vid of this user in next_pl1
                          ->label(array('user_nnm', 'user_vnm'))
              )
              ->validator( Validate::dbValues() ),
    
  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406
    edited July 2019

    I guess you would need to do 2 more left joins. Almost identical to the ones to player1 and 2 but without the alias, i.e. no AS. Also rename the column prefix accordingly.

    This is so complicated due to your unusual data model that requires self referencing joins. It would have been easier to have a link table between user and match which could be called user_has_match. It would only consist of the two foreign keys user id and match id. This would also allow for a match to have less or more than 2 players. There are a couple of examples on this in the docs. You would use an MJoin with an options instance. The MJoin is ideal to work with two column link tables.

  • mdesignmdesign Posts: 70Questions: 16Answers: 0

    you are so kind to help me out of this mess. it's such a pleasure and super fast to work with the editor plugin, much better than reloading each page again and again.

    but this is getting to complicated for me. can we try an easier approach, say would it be possible to place insteat the plaintext of the names the dropdown (select) within the datatable, which already works perfect (click the pencil-icon).

    http://diddyness.com/thri11ertour.000/-admin/editor-next

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406
    edited July 2019

    sorry, but I don't understand your new approach.

    Did you try this at all? (I added the two additional left joins as discussed above)

    ->leftJoin( 'thri_date', 'thri_date.date_idn', '=', 'thri_next.date_idn' )
    ->leftJoin( 'thri_user as player1', 'player1.user_vid', '=', 'thri_next.next_pl1' )
    ->leftJoin( 'thri_user as player2', 'player2.user_vid', '=', 'thri_next.next_pl2' )
    ->leftJoin( 'thri_user', 'thri_user.user_vid', '=', 'thri_next.next_pl1' )
    ->leftJoin( 'thri_user', 'thri_user.user_vid', '=', 'thri_next.next_pl2' )
    

    In my opinion the complexity stems from the unusual data model. This is not the normal way of modelling something like this. I recommend you use a link table.

    Here is more on joins and link tables: https://editor.datatables.net/manual/php/mjoin

    Maybe you want to check out this post that is going a bit more into the details about when to use an MJoin with an options instance ...
    https://datatables.net/forums/discussion/comment/155299#Comment_155299

    I am just a user of Editor and Data Tables which I believe is truly great software. It is easier to use Editor if you adhere to some principles of relational data base design e.g. using link tables in most situations and using auto-increment ids etc.. You might want to ask @allan or @colin for their opinion. Allan is actually the guy who wrote all or most of this. And Colin is working for him mainly in testing and user support. (Hope I got that right, guys. If not feel free to comment.)

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    I'm getting an "admin only" error on that link. I don't quite get the new approach either - are you saying that there is a select always visible in every row of the table? That actually sounds a bit harder!

    the datatable does endless processing, without any error and result

    Very likely there was a Javascript error. That will be reported on your browser's console. I think that approach still sounds like the correct way to go (assuming we can address that error).

    Allan is actually the guy who wrote all or most of this. And Colin is working for him mainly in testing and user support

    Spot on! Colin is doing some sys admin as well ('cos he loves it...) and we've got Sandy for the summer who is working on various bits of software (more details about which in the blog soon).

    Allan

  • mdesignmdesign Posts: 70Questions: 16Answers: 0

    after copy your update i got:
    "Syntax error or access violation: 1066 Not unique table/alias: 'thri_user'". maybe we are very close to the solution.

    live demo:
    http://diddyness.com/thri11ertour.000/-admin/editor-next

    that is what we have now:

    $editor = Editor::inst(
      $db, 'thri_next', 
           'thri_next.next_idn' /* Primary Key */
      )
        ->fields(
        Field::inst('player1.user_vnm'),
        Field::inst('player2.user_vnm'),
        Field::inst('player1.user_nnm'),
        Field::inst('player2.user_nnm'),
        Field::inst('thri_user.user_vid'),
        Field::inst('thri_next.webx_idn'),
        Field::inst('thri_next.next_idn'),
        Field::inst('thri_next.next_sta'),
        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('player1.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 as player1', 'player1.user_vid', '=', 'thri_next.next_pl1' )
      ->leftJoin( 'thri_user as player2', 'player2.user_vid', '=', 'thri_next.next_pl2' )
      ->leftJoin( 'thri_user', 'thri_user.user_vid', '=', 'thri_next.next_pl1' )
      ->leftJoin( 'thri_user', 'thri_user.user_vid', '=', 'thri_next.next_pl2' )
        ->process( $_POST )
        ->json();
    

    and thisDataTable columns

      // Player 1
      { data: null,
        render: function (data, type, row) {
                  return '<b class="-text-uppercase">' + data.player1.user_nnm + '</b><br>' + data.player1.user_vnm + '<br>' + data.thri_next.next_pl1; },
        editField: 'thri_next.next_pl1',
      },
    
      // Player 2
      { data: null,
        render: function (data, type, row) {
                  return '<b class="-text-uppercase">' + data.player2.user_nnm + '</b><br>' + data.player2.user_vnm + '<br>' + data.thri_next.next_pl1; },
        editField: 'thri_next.next_pl2',
      },
    
  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406

    This all results from your unusual database design: you have two foreign keys in your child table that relate to the same single column in your parent table. Such a thing causes problems that you would never have if you modeled this in a more conventional way using a link table.

    Based on you current database design I am unable to help you any further. Sorry.

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    I don't understand what these two lines are for:

     ->leftJoin( 'thri_user', 'thri_user.user_vid', '=', 'thri_next.next_pl1' )
     ->leftJoin( 'thri_user', 'thri_user.user_vid', '=', 'thri_next.next_pl2' )
    

    Isn't that what the joins for the aliased table to player1 and player2 are for?

    Allan

  • mdesignmdesign Posts: 70Questions: 16Answers: 0

    thx again for your patience. maybe what's confusing you is that i use user_vid (this is an unique id for parsing results from another website). instead we can take user_idn (primary key). could you help me out, now ?

    so we have 3 tables involved:

    1) thri_user (player infos)
    user_idn (primary key)
    user_vnm (first name)
    user_nnm (last name)

    2) thri_date (tournaments infos)
    date_idn (primary key
    date_tit (we only need the title of the date)

    3) thri_next (matches player1 versus player2)
    date_idn (title of tournament)
    next_dup (date of match)
    next_pl1 (user_idn of Player 1)
    next_pl2 (user_idn of Player 2)

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406
    edited July 2019

    I suggest you change the model like this:

    1) thri_user (player infos)
    user_idn (primary key, auto-increment)
    user_vnm (first name)
    user_nnm (last name)

    2) thri_user_has_thri_next
    user_idn (foreign key from thri_user)
    next_idn (foreign key from thri_next)

    3) thri_next (matches player1 versus player2)
    next_idn (primary key, auto-increment)
    date_idn (foreign key from thri_date)
    next_dup (date of match)

    4) thri_date (tournaments infos)
    date_idn (primary key, auto-increment)
    date_tit (we only need the title of the date)

    what is a "title of a date" by the way?

    If you changed it like this you could use an MJoin with an options instance and it would certainly work based on the examples in the docs and the example I posted a link to above. But that's up to you ...

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406
    edited July 2019

    @allan I don't really understand this either. There is a logical problem: Since there are the two foreign keys in the model (player1 and player2) that both relate to the same column in the parent table it doesn't really make sense to use "Field::inst('thri_user.user_vid')" but it is in the field list and causes an error ... therefore I suggested to remove it from the field list which apparently led to another error ...

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    Can you try this and let me know what the response from the server is please?

    <?php
    $editor = Editor::inst(
      $db, 'thri_next',
           'thri_next.next_idn' /* Primary Key */
      )
        ->fields(
        Field::inst('player1.user_vnm'),
        Field::inst('player2.user_vnm'),
        Field::inst('player1.user_nnm'),
        Field::inst('player2.user_nnm'),
        Field::inst('thri_next.webx_idn'),
        Field::inst('thri_next.next_idn'),
        Field::inst('thri_next.next_sta'),
        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('player1.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 as player1', 'player1.user_vid', '=', 'thri_next.next_pl1' )
      ->leftJoin( 'thri_user as player2', 'player2.user_vid', '=', 'thri_next.next_pl2' )
      ->process( $_POST )
      ->json();
    

    Allan

  • mdesignmdesign Posts: 70Questions: 16Answers: 0

    @allan
    DataTables warning: table id=DataTables_Table_0 - An SQL error occurred: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'player1.user_vid' in 'field list'

    You can test it live here:
    http://diddyness.com/thri11ertour.000/-admin/editor-next

  • mdesignmdesign Posts: 70Questions: 16Answers: 0

    @allan

    when insert this part it says:

    // Player 1
    { data: null,
      render: function (data, type, row) {
                return '<b class="-text-uppercase">' + data.player1.user_nnm + '</b><br>' + data.player1.user_vnm + '<br>' + data.thri_next.next_pl1; },
      editField: 'thri_next.next_pl1',
    },
     
    // Player 2
    { data: null,
      render: function (data, type, row) {
                return '<b class="-text-uppercase">' + data.player2.user_nnm + '</b><br>' + data.player2.user_vnm + '<br>' + data.thri_next.next_pl1; },
      editField: 'thri_next.next_pl2',
    },
    

    DataTables warning: table id=DataTables_Table_0 - An SQL error occurred: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'player1.user_vid' in 'field list'

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    I'm getting "Admin only" on that page.

    Could you add ->debug(true) immediately before ->process( $_POST ) please? Then tell me how to get past the "Admin only" error? (Or remind me if I've forgotten, and apologies!).

    Thanks,
    Allan

  • mdesignmdesign Posts: 70Questions: 16Answers: 0

    oh i'm sorry, allan - my fault.
    could you try it again. i opened the site for you and added ->debug(true)

    http://diddyness.com/thri11ertour.000/-admin/editor-next-debug

  • mdesignmdesign Posts: 70Questions: 16Answers: 0
    edited August 2019

    this is the current editor-next-ssp

    /* -------------------------------------------------------------------------- *
       conf-site & Admin Check
     * -------------------------------------------------------------------------- */
    include_once('../_conf-site.php');
    include_once('_conf-admin.php');
    if(!isset($_SESSION[nicaCOKNAME . '_admi'])) { print '<p><center><code>admin only!</code></center></p>'; exit; } 
    
    
    /* -------------------------------------------------------------------------- *
       DataTables Editor PHP library
     * -------------------------------------------------------------------------- */
    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('player1.user_vnm'),
        Field::inst('player2.user_vnm'),
        Field::inst('player1.user_nnm'),
        Field::inst('player2.user_nnm'),
        Field::inst('thri_next.webx_idn'),
        Field::inst('thri_next.next_idn'),
        Field::inst('thri_next.next_sta'),
        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') 
                ->options(Options::inst()
                    ->table('thri_user')
                    ->value('player1.user_vid')
                    ->label(array('user_nnm', 'user_vnm'))
                )
                ->validator( Validate::dbValues() ),
      
        Field::inst('thri_next.next_pl2') 
                ->options(Options::inst()
                    ->table('thri_user')
                    ->value('user_vid')
                    ->label(array('user_nnm', 'user_vnm'))
                )
                ->validator( Validate::dbValues() ),
      
        Field::inst('thri_date.date_tit'),
        Field::inst('thri_next.date_idn')
            ->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 as player1', 'player1.user_vid', '=', 'thri_next.next_pl1' )
      ->leftJoin( 'thri_user as player2', 'player2.user_vid', '=', 'thri_next.next_pl2' )
      ->debug(true)
      ->process( $_POST )
      ->json();
    
  • mdesignmdesign Posts: 70Questions: 16Answers: 0

    maybe it's getting a little bit clearer for you when we use user_idn instead of user_vid.

    user_idn is the primary key in table thri_user
    user_vid is a unique player id, which we need to parse results from another website.

  • mdesignmdesign Posts: 70Questions: 16Answers: 0

    STOP Allan - I think i got it.

    i replaced user_vid with user_idn and now its working like expected. here ist the working solution. thx so much for your patience and help. i love the editor plugin.

    Part 1/2: thisDataTable columns

    // Player 1
    { data: null,
      render: function (data, type, row) {
                return '<b class="-text-uppercase">' + data.player1.user_nnm + '</b><br>' + data.player1.user_vnm + '<br>' + data.thri_next.next_pl1; },
      editField: 'thri_next.next_pl1',
    },
      
    // Player 2
    { data: null,
      render: function (data, type, row) {
                return '<b class="-text-uppercase">' + data.player2.user_nnm + '</b><br>' + data.player2.user_vnm + '<br>' + data.thri_next.next_pl2; },
      editField: 'thri_next.next_pl2',
    },
    

    Part 2/2: ssp-Class

    $editor = Editor::inst(
      $db, 'thri_next', 
           'thri_next.next_idn' /* Primary Key */
      )
        ->fields(
        Field::inst('player1.user_vnm'),
        Field::inst('player2.user_vnm'),
        Field::inst('player1.user_nnm'),
        Field::inst('player2.user_nnm'),
        Field::inst('thri_next.webx_idn'),
        Field::inst('thri_next.next_idn'),
        Field::inst('thri_next.next_sta'),
        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_idn')     // schreibt die user_idn 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_idn')     // schreibt die user_idn 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 as player1', 'player1.user_idn', '=', 'thri_next.next_pl1' )
      ->leftJoin( 'thri_user as player2', 'player2.user_idn', '=', 'thri_next.next_pl2' )
      ->process( $_POST )
      ->json();
    
This discussion has been closed.