Left join where FK is in the other table

Left join where FK is in the other table

peterbrownepeterbrowne Posts: 103Questions: 23Answers: 0

Trying to do a left join, which I have done in the past OK, but in this instance the fk is not in the parent table 'unit' but the other table 'unit_outcome'

Editor::inst( $db_cm_md, 'unit', 'unit_pk' )    
    ->field(
        Field::inst( 'unit.unit_code' ),
        Field::inst( 'unit.unit_name' ),
        Field::inst( 'unit.points' ),
        Field::inst( 'unit.modified' ),
        Field::inst( 'unit.modified_by' )->setValue( $user ),
        Field::inst( 'unit.year_fk' )
            ->options( Options::inst()
                ->table( 'year' )
                ->value( 'year_pk' )
                ->label( 'year_name' )
            ),
        Field::inst( 'year.year_name' ),
            Field::inst( 'unit_outcome.unit_fk' )
            ->options( Options::inst()
                ->table( 'unit_outcome' )
                ->value( 'unit_outcome_pk' )
                ->label( 'unit_outcome' )
            ),
        Field::inst( 'unit_outcome.unit_outcome' )
    )
    
    ->leftJoin( 'year', 'year.year_pk', '=', 'unit.year_fk' )
    ->leftJoin( 'unit_outcome', 'unit_outcome.unit_outcome_pk', '=', 'unit.unit_fk' )
    ->process($_POST)
    ->json();

This question has an accepted answers - jump to answer

Answers

  • allanallan Posts: 51,411Questions: 1Answers: 7,752 Site admin
    Answer ✓

    That looks like it should work. Could you add ->debug(true) just before the ->process(...) call please, then use the debugger to give me a trace please - click the Upload button and then let me know what the debug code is.

    Allan

  • peterbrownepeterbrowne Posts: 103Questions: 23Answers: 0

    Went with mjoin and a lookup table, works fine,

Sign In or Register to comment.