column-sortable
column-sortable copied to clipboard
Duplicate joins issue when using filtering/sorting combined with sortable
I have a pretty complex index view that features filters, search as well as your sortable package(which is great BTW -- thank you!). When the user chooses to filter or search, and then try to sort a column that involves the same table, we end up with a duplicate join on that table. We've added the join due to the filter, then the subsequent call to sortable in the query builder adds a second join.
I include the error and some code from the controller below that I hope add to the understanding of what I'm trying to do. Is there something I missed that would enable this to work or should I be explicitly checking the joins in the query for duplicates?
Thanks
Got exception: SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias: 'session' (SQL: select count(*) as aggregate from `presentation` inner join `session` on `presentation`.`session_id` = `session`.`id` inner join `session` on `presentation`.`session_id` = `session`.`id` inner join `session_track` on `session`.`session_track_id` = `session_track`.`id` where `presentation`.`meeting_id` = 58 and `presentation`.`status_id` in (4, 5) and `presentation`.`active` = 1 and `session_id` = 6811 and `session`.`session_track_id` = 434)
->when(!empty(session('FILTER_IP_SESSION_TRACK_ID') || !empty(session('FILTER_IP_ADVISORY_GROUP_ID'))), function($query) use ($request){
return $query->join('session', 'presentation.session_id', '=', 'session.id');
})
->when(!empty(session('FILTER_IP_SESSION_TRACK_ID')), function($query) use ($request){
return $query->where('session.session_track_id', session('FILTER_IP_SESSION_TRACK_ID'));
})
->when(!empty(session('FILTER_IP_ADVISORY_GROUP_ID')), function($query) use ($request){
return $query->where('session.advisory_group_id', session('FILTER_IP_ADVISORY_GROUP_ID'));
})
->when(!empty(session('FILTER_IP_KEYWORD_ID')), function($query) use ($request){
return $query->join('keyword_association', 'presentation.id', 'keyword_association.presentation_id')
->where('keyword_association.keyword_id', session('FILTER_IP_KEYWORD_ID'));
})
->select('presentation.*')
->sortable(['assigned_id' => 'asc'])
Have you tried implementing the sorting logic yourself using the https://github.com/Kyslik/column-sortable#columnsortable-overriding-advanced?