Parent/Child where child table has a joined table. How to get this data?
Parent/Child where child table has a joined table. How to get this data?
Hello,
I have a page with parent/child like https://datatables.net/blog/2016-03-25, but I want in the child table a File upload table. So what I have is a parent table with a linked table and the child table is the mcfiles table. The thing I'm missing is how I get the "files" table joined to the whole thing, because the "files" table is joined to mcfiles.So I should have a mjoin with the linked table (mcfiles) and that should be linked to the files table. I hope I am a bit clear with my problem and hope someone can help me how to do this.
This question has an accepted answers - jump to answer
Answers
So would there be one image per row in the child table? If so, the setup shown in the blog post should work with that I think - just use
upload
for the upload field in the child table.Allan
Hello Allen,
I tried that already, but the problem is the POST field I have to use. I need the field mcf_id from de linked table in the post variable which I don't have because I get the id of the mcplaylist table, because that is the parent table.
So I have a mcplaylist table and the mcfiles and files tables, (these 2 are the file upload) and a linked table to join the mcplaylist table and the mcfiles table. Besides that there is the joined table files for the upload.
MCPLAYLIST
id name active startdate enddate lyid clgrid mandatory
1 juli 1 2017-07-01 00:00:00 2017-07-31 00:00:00 1 14 1
2 juni 0 2017-06-01 00:00:00 2017-06-30 00:00:00 1 14 1
3 aug 0 2018-08-01 00:00:00 2018-08-31 00:00:00 1 14 1
MCFILES
id image duration clId active ma di wo do vi za zo tijdvan tijdtot
12 18 10 14 0 1 1 1 1 1 0 0 10:13:00 19:14:00
13 19 10 14 0 1 0 1 0 1 0 0 12:38:00 16:38:00
14 20 10 14 0 1 1 0 0 0 0 1 11:16:00 19:16:00
15 21 10 14 0 1 1 0 0 0 0 0 17:00:00 23:04:00
FILES
imageid filename filesize web_path system_path
18 logo.png 7184 /uploads/logo.png C:/www/uploads/logo.png
19 rol.mp4 3080785 /uploads/rol.mp4 C:/www/uploads/rol.mp4
20 icon.png 562 /uploads/icon.png C:/www/uploads/icon.png
21 2017.png 193929 /uploads/2017.png C:/www/uploads/2017.png
MCPLAYLIST_MCFILES (LINKED TABLE)
id mcpl_id mcf_id
12 2 14
18 1 12
19 1 14
20 1 15
21 3 12
22 3 13
23 3 14
24 3 15
I hope you see my problem now?
Thanks in advance.
It sounds like this is the problem we need to address. Can you show me the code you are using please?
Allan
Previous file was mcplaylist_mcfilesgen.php
This is de mcfilesgenj.php:
Thanks. In the files editor Ajax configuration you have:
Are you able to add the
mcf_id
field to thed
object as well? Or have I misunderstood.Allan
Hello Allan,
I tried adding d.mcfid = selected.data().mcplaylist_mcfiles.mcf_id;
but this doesn't work.
Besides that I get an error: DataTables warning: table id=files - Requested unknown parameter 'mcfiles.duration' for row 0, column 3, despite the field is defined in the mcfilesgenj.php page.
Hello Allan,
in my mcplaylist_mcfilesgen.php I now get all the data that I need in the json response. Can I use this file in de javascript for the child table too or does it need to be a different file? I only don't get it to use all the data yet. Have to figure out how I need to address the fields I need in the child table and how to use the "if" statement with the POST variable still.
Above the json response, but I don't know how to get the fields in the mcfiles table. F.e.: mcfiles_duration or mcfiles_image. I get an error stating Unknown field every time.
Hello Allan,
I managed to get it to run the way I want. Just trying and looking for errors everytime. The problem was the sql command was not the right one and some trying on different joins and an error in the js with editor.file instead of fileseditor.file maneged to solve the problem. Pffff at last. Thank you very much for thinking with me!
When you add that line, does it correctly send the "mcfid" parameter, or when you say that it doesn't work do you mean that that variable is undefined?
Allan
After all I didn't need the mcf_id in the post, only the playlist_id. I managed to get the where clause to let it all happen: ->where( function ( $q ) {
$q->where( 'mcfiles.id', '(SELECT mcf_id FROM mcplaylist_mcfiles WHERE mcpl_id = :P_playlist )', 'IN', false );
$q->bind( ':P_playlist', $_POST['playlist']);
Only problem I still have is that it now shows the files that are connected to the playlist but when I add a file in the child table or delete a file from the child table, it adds the file to the media library but it is not connected to the playlist. and when I delete the file, it is deleted from the medialibrary, and that is not what it is soposed to be. It must be deleted only from the playlist. So the linked table should be updated and not the mcfiles table. I have to figure this out yet.
And when I select a playlist in the parent, I cannot edit or delete it. The buttons don't work anymore. Strange.
Sorry - I completely lost track of this thread!
When the buttons don't work any more, has an error been reported either in the Javascript console of your browser, or on the server-side (usually in the Ajax response)?
Allan