Mjoin - restrict records based on foreign key in parent table?

peterbrowne

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!

peterbrowne

Perhaps using a sub-select on the Mjoin like WHERE (SELECT * FROM assessment WHERE unit_fk = $unit_pk) ??

peterbrowne

I have seen sub-selects discussed at https://editor.datatables.net/manual/php/conditions#Sub-selects

However a few things:

1. I am unsure of how exactly to do this in my code above
2. How do I pass the unit_pk to the sub-select query?

peterbrowne

.

allan

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.

If I'm understanding this, it seems to suggest that assessment.unit_fk can be null - is that correct? You already noted that you have:

Now, assessment records have assessment.unit_fk, which match unit.unit_pk

And I'm assuming that unit.pk is a typo for unit.unit_pk giving your db naming conventions.

If this is all right, then you can add:

->where('assessment.unit_fk', null, '!=')

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

peterbrowne

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.

peterbrowne

New thread at https://datatables.net/forums/discussion/60910/dependant-selects#latest