Applying where condition to both parent and joined table - One-to-many joins
Applying where condition to both parent and joined table - One-to-many joins
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?
This discussion has been closed.
Answers
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
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?
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
Hi Allan ... by any chance ... how can I avoid multiple records in the above situation? Many thanks
Remove the
leftJoin. It isn't being used (although I can't see the compelete code).Allan
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' :
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