MJoin alias and displaying results
MJoin alias and displaying results
peterbrowne
Posts: 314Questions: 54Answers: 0
in Editor
I have a number of Mjoins using aliases. How do I display the results in Datatables using the alias names? E,g.
->join(
Mjoin::inst( 'discipline_outcome' )
->name( 'emergency_medicine' )
->link( 'unit_outcome.unit_outcome_pk', 'unit_outcome_discipline_outcome_1_lookup.unit_outcome_fk' )
->link( 'emergency_medicine.discipline_outcome_pk', 'unit_outcome_discipline_outcome_1_lookup.discipline_outcome_fk' )
->order( 'discipline_outcome.discipline_outcome asc' )
->where('discipline_fk', '1' )
->fields(
Field::inst( 'discipline_outcome_pk' )
->options( Options::inst()
->table( 'discipline_outcome' )
->value( 'discipline_outcome_pk' )
->label( 'discipline_outcome' )
->where( function ($q) {
$q->where( 'discipline_fk', '1' );
}
),
Field::inst( 'discipline_outcome' )
)
)
)
and HTML:
<editor-field name="emergency_medicine[].discipline_outcome_pk"></editor-field>
and Editor field:
{
label: "Discipline Outcomes - Emergency Medicine:",
name: "emergency_medicine[].discipline_outcome_pk",
type: "select",
placeholder: 'No selection',
placeholderDisabled: false,
placeholderValue: 0,
multiple: true
}
and Datatables:
, {
data: "emergency_medicine",
render: "[, ].discipline_outcome"
}
This question has an accepted answers - jump to answer
This discussion has been closed.
Answers
I have no problem with the editor parts, just the display in the Datatable. The results are being returned OK as I can see the comma separators, but not the record names.
That looks like what I would expect to be used in terms of the configuration. Could you show me the JSON that is being returned please?
Thanks,
Allan
https://www.dropbox.com/s/jpm3uyxwsq4dg2m/json.txt?dl=0
You can see below from the JSON (link above) that there are results for unit_outcome_pk = 88 for 'paediatrics':
In Datatables in the browser, this just shows 2 commas, being the separators for the above 3 discipline_outcomes. The discpline_outcome name is not in the results, note that column is 'discipline_outcome'...
Likewise for unit_outcome_pk = 112. There are 4 discipline_outcomes related to that unit_outcome for "obstetrics_gynaecology" which is correct, but Datatables just shows 3 commas in the browser...i.e. the records are returned, but no names.
Yes, searching through the JSON I only see a single row which has an entry for the
emergency_medicine
data point:That would certainly explain why the
render: "[, ].discipline_outcome"
is not showing anything - there isn't adiscipline_outcome
value.What I'm not understanding is why
emergency_medicine.discipline_outcome_pk
isn't being used in the SQL. I presume at least that the code is what is used for this JSON output?Allan
Well, the 7 alias tables on the Mjoins must be using
.discipline_outcome_pk
to return the correct records as I mentioned in my last post. There are 2 results foremergency_medicine.discipline_outcome_pk
:and
The server code in
unit_outcome_data.php
is the following which clearly is expectingdiscipline_outcome
to be returned in the results. I would hazard a guess that the alias tables might be confusing your PHP library in returningdiscipline_outcome
in the results?```
use
DataTables\Editor,
DataTables\Editor\Field,
DataTables\Editor\Format,
DataTables\Editor\Mjoin,
DataTables\Editor\Options,
DataTables\Editor\Upload,
DataTables\Editor\Validate,
DataTables\Editor\ValidateOptions;
Editor::inst( $db_cm_md, 'unit_outcome', 'unit_outcome_pk' )
<?php > ``` ?>->field(
Field::inst( 'unit_outcome.unit_outcome' ),
Field::inst( 'unit_outcome.modified' ),
Field::inst( 'unit_outcome.modified_by' )->setValue( $user )
)
->join(
Mjoin::inst( 'unit' )
->link( 'unit_outcome.unit_outcome_pk', 'unit_unit_outcome_lookup.unit_outcome_fk' )
->link( 'unit.unit_pk', 'unit_unit_outcome_lookup.unit_fk' )
->order( 'unit.unit_name asc' )
->fields(
Field::inst( 'unit_pk' )
->options( Options::inst()
->table( 'unit' )
->value( 'unit_pk' )
->label( 'unit_name' )
),
Field::inst( 'unit_name' )
)
)
->join(
Mjoin::inst( 'program_outcome' )
->link( 'unit_outcome.unit_outcome_pk', 'program_outcome_unit_outcome_lookup.unit_outcome_fk' )
->link( 'program_outcome.program_outcome_pk', 'program_outcome_unit_outcome_lookup.program_outcome_fk' )
->order( 'program_outcome.program_outcome asc' )
->fields(
Field::inst( 'program_outcome_pk' )
->options( Options::inst()
->table( 'program_outcome' )
->value( 'program_outcome_pk' )
->label( 'program_outcome' )
->render( function ( $row ) {
return strip_tags($row['program_outcome']);
} )
->order( 'program_outcome' )
),
Field::inst( 'program_outcome' )
)
)
->join(
Mjoin::inst( 'strand' )
->link( 'unit_outcome.unit_outcome_pk', 'strand_unit_outcome_lookup.unit_outcome_fk' )
->link( 'strand.strand_pk', 'strand_unit_outcome_lookup.strand_fk' )
->order( 'strand.strand_name asc' )
->fields(
Field::inst( 'strand_pk' )
->options( Options::inst()
->table( 'strand' )
->value( 'strand_pk' )
->label( 'strand_name' )
->order( 'strand_name' )
),
Field::inst( 'strand_name' )
)
)
->join(
Mjoin::inst( 'discipline_outcome' )
->name( 'emergency_medicine' )
->link( 'unit_outcome.unit_outcome_pk', 'unit_outcome_discipline_outcome_1_lookup.unit_outcome_fk' )
->link( 'emergency_medicine.discipline_outcome_pk', 'unit_outcome_discipline_outcome_1_lookup.discipline_outcome_fk' )
->order( 'discipline_outcome.discipline_outcome asc' )
->where('discipline_fk', '1' )
->fields(
Field::inst( 'discipline_outcome_pk' )
->options( Options::inst()
->table( 'discipline_outcome' )
->value( 'discipline_outcome_pk' )
->label( 'discipline_outcome' )
->where( function ($q) {
$q->where( 'discipline_fk', '1' );
}
),
Field::inst( 'discipline_outcome' )
)
)
)
->join(
Mjoin::inst( 'discipline_outcome' )
->name( 'general_practice' )
->link( 'unit_outcome.unit_outcome_pk', 'unit_outcome_discipline_outcome_2_lookup.unit_outcome_fk' )
->link( 'general_practice.discipline_outcome_pk', 'unit_outcome_discipline_outcome_2_lookup.discipline_outcome_fk' )
->order( 'discipline_outcome.discipline_outcome asc' )
->where('discipline_fk', '2' )
->fields(
Field::inst( 'discipline_outcome_pk' )
->options( Options::inst()
->table( 'discipline_outcome' )
->value( 'discipline_outcome_pk' )
->label( 'discipline_outcome' )
->where( function ($q) {
$q->where( 'discipline_fk', '2' );
}
),
Field::inst( 'discipline_outcome' )
)
)
)
->join(
Mjoin::inst( 'discipline_outcome' )
->name( 'internal_medicine' )
->link( 'unit_outcome.unit_outcome_pk', 'unit_outcome_discipline_outcome_3_lookup.unit_outcome_fk' )
->link( 'internal_medicine.discipline_outcome_pk', 'unit_outcome_discipline_outcome_3_lookup.discipline_outcome_fk' )
->order( 'discipline_outcome.discipline_outcome asc' )
->where('discipline_fk', '3' )
->fields(
Field::inst( 'discipline_outcome_pk' )
->options( Options::inst()
->table( 'discipline_outcome' )
->value( 'discipline_outcome_pk' )
->label( 'discipline_outcome' )
->where( function ($q) {
$q->where( 'discipline_fk', '3' );
}
),
Field::inst( 'discipline_outcome' )
)
)
)
->join(
Mjoin::inst( 'discipline_outcome' )
->name( 'obstetrics_gynaecology' )
->link( 'unit_outcome.unit_outcome_pk', 'unit_outcome_discipline_outcome_4_lookup.unit_outcome_fk' )
->link( 'obstetrics_gynaecology.discipline_outcome_pk', 'unit_outcome_discipline_outcome_4_lookup.discipline_outcome_fk' )
->order( 'discipline_outcome.discipline_outcome asc' )
->where('discipline_fk', '4' )
->fields(
Field::inst( 'discipline_outcome_pk' )
->options( Options::inst()
->table( 'discipline_outcome' )
->value( 'discipline_outcome_pk' )
->label( 'discipline_outcome' )
->where( function ($q) {
$q->where( 'discipline_fk', '4' );
}
),
Field::inst( 'discipline_outcome' )
)
)
)
->join(
Mjoin::inst( 'discipline_outcome' )
->name( 'paediatrics' )
->link( 'unit_outcome.unit_outcome_pk', 'unit_outcome_discipline_outcome_5_lookup.unit_outcome_fk' )
->link( 'paediatrics.discipline_outcome_pk', 'unit_outcome_discipline_outcome_5_lookup.discipline_outcome_fk' )
->order( 'discipline_outcome.discipline_outcome asc' )
->where('discipline_fk', '5' )
->fields(
Field::inst( 'discipline_outcome_pk' )
->options( Options::inst()
->table( 'discipline_outcome' )
->value( 'discipline_outcome_pk' )
->label( 'discipline_outcome' )
->where( function ($q) {
$q->where( 'discipline_fk', '5' );
}
),
Field::inst( 'discipline_outcome' )
)
)
)
->join(
Mjoin::inst( 'discipline_outcome' )
->name( 'psychiatry' )
->link( 'unit_outcome.unit_outcome_pk', 'unit_outcome_discipline_outcome_6_lookup.unit_outcome_fk' )
->link( 'psychiatry.discipline_outcome_pk', 'unit_outcome_discipline_outcome_6_lookup.discipline_outcome_fk' )
->order( 'discipline_outcome.discipline_outcome asc' )
->where('discipline_fk', '6' )
->fields(
Field::inst( 'discipline_outcome_pk' )
->options( Options::inst()
->table( 'discipline_outcome' )
->value( 'discipline_outcome_pk' )
->label( 'discipline_outcome' )
->where( function ($q) {
$q->where( 'discipline_fk', '6' );
}
),
Field::inst( 'discipline_outcome' )
)
)
)
->join(
Mjoin::inst( 'discipline_outcome' )
->name( 'surgery' )
->link( 'unit_outcome.unit_outcome_pk', 'unit_outcome_discipline_outcome_7_lookup.unit_outcome_fk' )
->link( 'surgery.discipline_outcome_pk', 'unit_outcome_discipline_outcome_7_lookup.discipline_outcome_fk' )
->order( 'discipline_outcome.discipline_outcome asc' )
->where('discipline_fk', '7' )
->fields(
Field::inst( 'discipline_outcome_pk' )
->options( Options::inst()
->table( 'discipline_outcome' )
->value( 'discipline_outcome_pk' )
->label( 'discipline_outcome' )
->where( function ($q) {
$q->where( 'discipline_fk', '7' );
}
),
Field::inst( 'discipline_outcome' )
)
)
)
->join(
Mjoin::inst( 'discipline_outcome' )
->name( 'infection_immunity' )
->link( 'unit_outcome.unit_outcome_pk', 'unit_outcome_discipline_outcome_8_lookup.unit_outcome_fk' )
->link( 'infection_immunity.discipline_outcome_pk', 'unit_outcome_discipline_outcome_8_lookup.discipline_outcome_fk' )
->order( 'discipline_outcome.discipline_outcome asc' )
->where('discipline_fk', '8' )
->fields(
Field::inst( 'discipline_outcome_pk' )
->options( Options::inst()
->table( 'discipline_outcome' )
->value( 'discipline_outcome_pk' )
->label( 'discipline_outcome' )
->where( function ($q) {
$q->where( 'discipline_fk', '8' );
}
),
Field::inst( 'discipline_outcome' )
)
)
)
->debug(true)
->process($_POST)
->json();
The relevant tables with data at https://www.db-fiddle.com/f/7B8pRJKmyX321khhzydeBN/0
.
This:
I would have expected to result in a join something like:
in the SQL being executed. That it isn't there is what has been confused / concerned.
My apologies, I've had to rebuild my DataTables development VM, while will take a while on my current connection. I'll either try this at home this evening or get back to you tomorrow when I've had a chance to execute the exact code from above and see if I can debug what is going on.
Thanks,
Allan
OK thanks Allan. I need to have a resolution soon as a lot of work depends on this working one way or another.
As I said in my previous posts, there is data being returned as I can see the correct number of comma separators, just no names. See:
https://paste.pics/296516038de2cc192fa950e7e9b7380b
I will probably combine those columns later when this is working.
BTW, the screenshot in the link above is for
unit_outcome_pk
= 88 (unit_outcome_fk
= 88) and the 3 associateddiscipline_outcome
(pks 22, 50 and 2) for paediatics (unit_outcome_discipline_outcome_5_lookup
andpaediatrics[].discipline_outcome_pk
) as you can see in the db fiddle link above.Hi,
Been looking at the problem this morning and it is caused by:
because
discipline_outcome
is not a table that is mentioned in thelink
s that are defined:Looking at your SQL above I don't see
emergency_medicine
defined as a table anywhere. Is it? I wonder if that should actually be replaced withdiscipline_outcome
in the above?Thanks,
Allan
I'm using aliases for the table discipline_outcomes.
I though that was pretty clear from the start.
Did you look at the db fiddle with the tables and data? https://www.db-fiddle.com/f/7B8pRJKmyX321khhzydeBN/0
Am I using aliases in Mjoins incorrectly?
If I use the following code, nothing seems to change. No errors and still no names showing in the paediatrics column in datatables, just comma separators.
and
e.g. with the above code, I just get
<td class="sorting_1">, , , </td>
The second WHERE clause in the Mjoin is probably wrong as there is no
discipline_fk
in the tablediscipline_outcome
, just in the lookup table:So the Mjoin probably should be:
Still make no difference though as the same number of comma separators show with no field names...
Either I'm missing something, or I think you might have misunderstood how the
name
parameter works. Looking in the SQL file (which I did look through as I mentioned above) I couldn't see any mention ofemergency_medicine
(I'm picking on that one just for consistency). So I'm guessing you are looking for->name( 'emergency_medicine' )
to do the SQL alias?I'm afraid it doesn't - it is purely a PHP construct that. It will read the information from the database using the table name given for the Mjoin and the
link
data. Then thename
option is used to write that to the JSON that is sent tot he client-side.However, I think I've spotted the issue . The code inside
->fields()
is wrong. The way it is at the moment it is passing the secondField::inst
into->options()
as the second parameter. It is easier to see if the code is reformatted a little:It should be:
Sure do wish I'd spotted that earlier...! That should fix the missing
discipline_outcome
field though .Allan
Yep, that is what I thought it's purpose was, and how I'm using it.
Your spot on re the Mjoin though, and it's all working with that change, e.g:
and
Phew.!
Thanks Allan!
Peter
Looks like I didn't test this enough.
With that change, the 7 selects based on the 7 Mjoins no longer filter on the WHERE clauses on those joins.
The change fixed the showing of names in the datatables OK, but now the 7 selects are all populated with the exact same options.
Current code:
one select in the editor:
and datatables:
and in the server data
unit_outcome_data.php
and some images
https://paste.pics/92762506751b2d8ab1143c5167765f1e
and the selects with exact same populated options despite having WHERE clauses which work on the results in datatables
https://paste.pics/266e64be08d75f6f1ee5b29e99f574c4
will get all of the options from the
discipline_outcome_pk
table. If you want to limit it to just a specific set of options from that table, you need to add awhere
condition to it.Allan