left join to mjoin

ccantey

I have a many to many situation: commissions, members_commissions, members.

After I make the mjoin, I would like a 1:many join on the results. In SQL i use multiple left joins. Similar to these questions: error with mjoin, joins-with-multiple-related-tables. However, using multilpe left joins returns duplicate rows.

I recieve the following error:

An SQL error occurred: SQLSTATE[42P01]: Undefined table: 7 ERROR: missing FROM-clause entry for table \"members

Editor::inst( $db, 'commissions', 'commid' )
        ->debug( true )
        ->fields(
            Field::inst( 'commissions.commid' )->set(false)
            // Field::inst( 'mi.party' ),
            // Field::inst( 'mct.session' ),
            // Field::inst( 'mct.term_start' ),
            // Field::inst( 'mct.term_end' ),
            // Field::inst( 'mct.officer' ),
            // Field::inst( 'mct.representing' ),
            // Field::inst( 'mct.appt_by' )
            
        )

         ->join(
                Mjoin::inst( 'members' )
                    ->link( 'commissions.commid', 'members_commissions.commission_id' )
                    ->link( 'members.memid', 'members_commissions.member_id' )
                 
                    ->fields(
                        Field::inst( 'memid' ),
                     
                        Field::inst( 'fname' ),
                        Field::inst( 'lname' )
                    )
            )


        ->leftJoin( 'member_info AS mi', 'mi.memid', '=', 'members.memid' )
        //->leftJoin( 'member_comm_term AS mct', 'mct.memid', '=', 'members.memid' )
        //->where('members.memid','mct.memid') //not allowed in dataTables

        // ->where('commissions.commid', $_POST['commissionid'])
        //->distinct(true) //uncomment and uncomment all 'distinct' mentions in editor.php
        ->process( $_POST )
        ->json();

allan

If I'm understanding correctly - you are looking to do a left join on the records found by the Mjoin. Is that correct?

Unfortunately, that is not something that the Mjoin class currently has the ability to do, but it is something I'm planning to add soon.

In the meantime, what to do is create a VIEW that does the left join and point the Mjoin at that rather than the members table.

Allan

ccantey

That is correct. Thank you.