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

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

peterbrownepeterbrowne Posts: 103Questions: 23Answers: 0
edited March 6 in Editor

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!

Answers

  • peterbrownepeterbrowne Posts: 103Questions: 23Answers: 0

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

  • peterbrownepeterbrowne Posts: 103Questions: 23Answers: 0
    edited March 6

    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?
  • peterbrownepeterbrowne Posts: 103Questions: 23Answers: 0

    .

  • allanallan Posts: 51,422Questions: 1Answers: 7,754 Site admin

    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

  • peterbrownepeterbrowne Posts: 103Questions: 23Answers: 0

    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.

Sign In or Register to comment.