Applying where condition to both parent and joined table - One-to-many joins

Capamania

I have a One-to-many joins table which works great. Yet, if I apply a where condition to the joined table, the filtering is only applied to the JOIN Table, not both parent and joined at the same time (and vice versa).

Editor::inst( $db, 'accounts', 'id' )
    ->fields(
        Field::inst( 'accounts.name' ),
                Field::inst( 'accounts.id' ),
...
    ->join(
        Mjoin::inst( 'country', 'iso_01' )
            ->link( 'accounts.id', 'accounts_mm.id' )
            ->link( 'country.iso_01', 'accounts_mm.iso_01' )
            ->fields(
                Field::inst( 'iso_01' )
                    ->validator( 'Validate::required' )
                    ->options( 'country', 'iso_01', 'name' ),
                Field::inst( 'name' )
            )
            
    ->where( function ($k) {
            if ( $_POST["selectCountry"] ) {
                $k->where( 'country.iso_01', $_POST["selectCountry"], '=' );
                    }
    } )                      
    )
...

How can I apply it to both that the parent and joined table?

allan

Sorry, there is no option to do that at the moment. You can do that with a left join (and perhaps that would be an option for you - left join from accounts to accounts_mm to country which would limit the results to just those that match.

Allan

Capamania

Thanks Allan. I tried this as well and it kind of works. It does the filtering and the result is correct, yet the unfiltered table now shows multiple rows (DT_RowId) of the same record depending on the number of records in account_mm. How can this be avoided?

Editor::inst( $db, 'accounts', 'id' )
    ->fields(
        Field::inst( 'accounts.name' ),
        Field::inst( 'accounts.id' ),
...
    ->join(
        Mjoin::inst( 'country', 'iso_01' )
            ->link( 'accounts.id', 'accounts_mm.id' )
            ->link( 'country.iso_01', 'accounts_mm.iso_01' )
            ->fields(
                Field::inst( 'iso_01' )
                    ->validator( 'Validate::required' )
                    ->options( 'country', 'iso_01', 'name' ),
                Field::inst( 'name' )
            )                               
    )

    ->leftJoin( 'account_mm', 'accounts.id',  '=', 'account_mm.id' )  
    
    ->where( function ($k) {
            if ( $_POST["selectCountry"] ) {
                $k->where( 'country.iso_01', $_POST["selectCountry"], '=' );
                    }
    } )      
... 

account_mm:

id iso_01
1 DE
2 DE
3 GB
2 US
4 US
3 US

What I want at the end is that ... to each account multiple countries can be allocated to. In the table cell itself I'm displaying it like (which works): DE, US

Capamania

Hi Allan ... by any chance ... how can I avoid multiple records in the above situation? Many thanks

allan

Remove the leftJoin. It isn't being used (although I can't see the compelete code).

Allan

Capamania

Hi Allan ... but then the filter doesn't work ... I'm getting ..

DataTables warning: table id=table - SQLSTATE[42S22]: Column not found: 1054 Unknown column 'account_mm.iso_01' in 'where clause'

The where condition above was actually on 'account_mm.iso_01' :

->where( function ($k) {
        if ( $_POST["selectCountry"] ) {
            $k->where( 'account_mm.iso_01', $_POST["selectCountry"], '=' );
                }
} )    

allan

Are you selecting any columns from that table? It doesn't look like it from the above code. If you need to include it, then just add a field, even if you don't display it in the DataTable.

Allan