Mjoin - restrict records based on foreign key in parent table?
Mjoin - restrict records based on foreign key in parent table?
peterbrowne
Posts: 314Questions: 54Answers: 0
I have the following containing an Mjoin on a lookup table, 'unit_outcome_assessment_lookup'.
I want to restrict the 'assessment' records from the Mjoin to those that only belong to a 'unit' record. Currently all assessment records are returned.
Editor::inst( $db_cm_md, 'unit_outcome', 'unit_outcome_pk' )
->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( 'assessment' )
->link( 'unit_outcome.unit_outcome_pk', 'unit_outcome_assessment_lookup.unit_outcome_fk' )
->link( 'assessment.assessment_pk', 'unit_outcome_assessment_lookup.assessment_fk' )
->order( 'assessment.assessment asc' )
->fields(
Field::inst( 'assessment_pk' )
->options( Options::inst()
->table( 'assessment' )
->value( 'assessment_pk' )
->label( 'assessment' )
),
Field::inst( 'assessment' )
)
)
->process($_POST)
->json();
Now, assessment records have assessment.unit_fk, which match unit.unit_pk
But I'm not sure what I should be doing to only show assessment records from the Mjoin where there is a match on assessment.unit_fk = unit.pk. An example would be great!
This discussion has been closed.
Answers
Perhaps using a sub-select on the Mjoin like WHERE (SELECT * FROM assessment WHERE unit_fk = $unit_pk) ??
I have seen sub-selects discussed at https://editor.datatables.net/manual/php/conditions#Sub-selects
However a few things:
.
If I'm understanding this, it seems to suggest that
assessment.unit_fkcan benull- is that correct? You already noted that you have:And I'm assuming that
unit.pkis a typo forunit.unit_pkgiving your db naming conventions.If this is all right, then you can add:
to your Mjoin condition.
If that isn't right, then apologies, and could you elaborate a little on the database schema and the goal please?
Thanks,
Allan
Allan, I have changed things around a bit, but I still have the same sort of question, so I will open a new thread and be a bit clearer hopefully.
New thread at https://datatables.net/forums/discussion/60910/dependant-selects#latest