Editor: Problem with leftJoin
Editor: Problem with leftJoin
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
I got this from your java script:
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).
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):
It's called a self referencing join: https://editor.datatables.net/examples/advanced/joinSelf
Javascript:
As your commented out code suggests, you need an
asstatement. These two lines look okay, minus that you are aliasing them to the same name:You just need to add
user_1.{fieldName}to your list of fields, and likewise foruser_2.Allan
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.
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.
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 ?
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.
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
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)
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.)
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
selectalways visible in every row of the table? That actually sounds a bit harder!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).
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
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:
and thisDataTable columns
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.
I don't understand what these two lines are for:
Isn't that what the joins for the aliased table to
player1andplayer2are for?Allan
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)
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 ...
@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 ...
Can you try this and let me know what the response from the server is please?
Allan
@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
@allan
when insert this part it says:
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'
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
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
this is the current editor-next-ssp
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.
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
Part 2/2: ssp-Class