SearchPanes: server-side - having panes contain ALL options
SearchPanes: server-side - having panes contain ALL options
bliksempie
Posts: 17Questions: 4Answers: 0
Hi,
I got searchpanes plugin to work with server-side and my own custom server-side script 9not using Editor) with three caveats, which I hope someone has some thoughts for me.
- The search panes contain only the items that are found in the first page of the listing. I adjusted my query to bring back results from the entire table, however that defeats the purpose of having pagination, and can be very resource intensive on large datasets. Is this expected?
- When I use the entire dataset for the search panes, the matching table do not correspond, as it is paginated, so it is moot, really, if the first item can't be changed in behaviour.
- When selecting an item in my search panes without the two above options (i.e., search panes showing only the current table), my filter "clears" the other items from the pane the moment I select one, until unselect that one item again.
Are there any thoughts I can try? I do not know how to use your live tool with my custom code, but here are the code blocks doing the work:
$(document).ready(function() {
$('#shoppers').DataTable({
processing: true,
serverSide: true,
searching: true,
select: true,
stateSave: true,
stateDuration: 0,
lengthMenu: [[10, 15, 25, 50], [10, 15, 25, 50]],
pageLength: 25,
ajax: {
cache: false,
url: "<?php echo base_url('shopper/ajax_get_shoppers'); ?>",
type: 'POST'
},
columns: [
{ data: 'first_name' },
{ data: 'last_name' },
{ data: 'mobile_number' },
{ data: 'createdate' },
{ data: 'moddate' }
],
searchPanes: {
columns: [0, 1, 2, 3, 4],
layout: 'columns-5',
cascadePanes: true,
initCollapsed: false,
controls: false,
viewCount: true,
viewTotal: true
},
columnDefs: [{
searchPanes: {
show: true,
combiner: 'and'
},
targets: [0, 1, 2, 3, 4]
}],
language: {
searchPanes: {
count: '{total} found',
countFiltered: '{shown} / {total}'
}
},
dom: 'Plfrtip'
});
});
And my un-optmized server side code is like this:
public function ajax_get_shoppers()
{
$draw = $this->input->post('draw');
$row = $this->input->post('start');
$rowperpage = $this->input->post('length');
$columnIndex = $this->input->post('order')[0]['column'];
$columnName = $this->input->post('columns')[$columnIndex]['data'];
$columnSortOrder = $this->input->post('order')[0]['dir'];
$searchValue = $this->input->post('search')['value'];
$searchArray = [];
$panes = $this->input->post('searchPanes');
if (!empty($panes)) {
foreach ($panes as $k => $v) {
if (count($v) == 1) {
$this->db->where($k, $v[0]);
} else {
$this->db->where_in($k, $v);
}
}
}
$result_full = $this->shopper_model->get_shopper_list($row, null, '', $columnName . ' ' . $columnSortOrder);
$result = $this->shopper_model->get_shopper_list($row, $rowperpage, $searchValue, $columnName . ' ' . $columnSortOrder);
if (!empty($panes)) {
foreach ($panes as $k => $v) {
if (count($v) == 1) {
$this->db->where($k, $v[0]);
} else {
$this->db->where_in($k, $v);
}
}
}
$totalRecords = $this->shopper_model->get_shopper_count();
if (!empty($panes)) {
foreach ($panes as $k => $v) {
if (count($v) == 1) {
$this->db->where($k, $v[0]);
} else {
$this->db->where_in($k, $v);
}
}
}
$totalRecordsWithFilter = $this->shopper_model->get_shopper_count($searchValue);
$first_names = [];
$last_names = [];
$mobile_numbers = [];
$createdates = [];
$moddates = [];
if (!empty($result_full)) {
foreach ($result_full as $k => $v) {
foreach ($v as $kk => $vv) {
if ($kk == 'first_name') {
if (!isset($first_names[$vv])) {
$first_names[$vv]['DT_RowId'] = 'row_' . $v['id'];
$first_names[$vv]['DT_RowData'] = ['pkey' => $v['id']];
$first_names[$vv]['label'] = $vv;
$first_names[$vv]['value'] = $vv;
$first_names[$vv]['total'] = 1;
$first_names[$vv]['count'] = 1;
} else {
$first_names[$vv]['total']++;
$first_names[$vv]['count']++;
}
} elseif ($kk == 'last_name') {
if (!isset($last_names[$vv])) {
$last_names[$vv]['DT_RowId'] = 'row_' . $v['id'];
$last_names[$vv]['DT_RowData'] = ['pkey' => $v['id']];
$last_names[$vv]['label'] = $vv;
$last_names[$vv]['value'] = $vv;
$last_names[$vv]['total'] = 1;
$last_names[$vv]['count'] = 1;
} else {
$last_names[$vv]['total']++;
$last_names[$vv]['count']++;
}
} elseif ($kk == 'mobile_number') {
if (!isset($mobile_numbers[$vv])) {
$mobile_numbers[$vv]['DT_RowId'] = 'row_' . $v['id'];
$mobile_numbers[$vv]['DT_RowData'] = ['pkey' => $v['id']];
$mobile_numbers[$vv]['label'] = $vv;
$mobile_numbers[$vv]['value'] = $vv;
$mobile_numbers[$vv]['total'] = 1;
$mobile_numbers[$vv]['count'] = 1;
} else {
$mobile_numbers[$vv]['total']++;
$mobile_numbers[$vv]['count']++;
}
} elseif ($kk == 'createdate') {
if (!isset($createdates[$vv])) {
$createdates[$vv]['DT_RowId'] = 'row_' . $v['id'];
$createdates[$vv]['DT_RowData'] = ['pkey' => $v['id']];
$createdates[$vv]['label'] = $vv;
$createdates[$vv]['value'] = $vv;
$createdates[$vv]['total'] = 1;
$createdates[$vv]['count'] = 1;
} else {
$createdates[$vv]['total']++;
$createdates[$vv]['count']++;
}
}
if ($kk == 'moddate') {
if (!isset($moddates[$vv])) {
$moddates[$vv]['DT_RowId'] = 'row_' . $v['id'];
$moddates[$vv]['DT_RowData'] = ['pkey' => $v['id']];
$moddates[$vv]['label'] = $vv;
$moddates[$vv]['value'] = $vv;
$moddates[$vv]['total'] = 1;
$moddates[$vv]['count'] = 1;
} else {
$moddates[$vv]['total']++;
$moddates[$vv]['count']++;
}
}
}
}
}
$searchPanes = [
'options' => [
'first_name' => array_values($first_names),
'last_name' => array_values($last_names),
'mobile_number' => array_values($mobile_numbers),
'createdate' => array_values($createdates),
'moddate' => array_values($moddates)
]
];
$response = [
'draw' => intval($draw),
'recordsTotal' => $totalRecords['count'],
'recordsFiltered' => $totalRecordsWithFilter['count'],
'data' => $result,
'searchPanes' => $searchPanes
];
echo json_encode($response);
}
I am using $result_full as the population for the panels to simulate points 1 and 2, but the foreach loop of the $result is the vanilla option (3).
I hope I am making sense.
Thoughts please?
Kobus
This question has an accepted answers - jump to answer
Answers
I noted my example above missed one of the DB search "where in" parts missing - the problem remains though.
You are building the
$searchPanesvariable from the data from the page requested only. So it could only ever include the data from that page. What you need to do instead is run additional queries against the database to get the data without pagination. This function is how we do it in Editor's libraries - one query (possibly two if you have counts enabled) per pane. No way to avoid that I'm afraid.Sounds related to the first. Let's sort that one first.
If I've understood correctly, that is the default behaviour. It is controlled by the Select extension in its
osmode - click an item and it will deselect any other. Shift click to range select, ctrl click to individual toggle.You can use the
multiselect mode if you prefer that, which is shown in this example.Allan
Hi Allan,
Thanks for your response.
As for #1, I tried to use $result_full to get "all" the data for additional queries, and that updated the panes, with the variable $result_full. So the panes are then populated correctly, however, as I said, having to retrieve the entire dataset would defeat the purpose of having pagination in the first place, as large datasets could crash my page - for example, having to load 100k records to get the search panes populated.
As for #2, for a test, I loaded all the data, and found a bug in my code after posting the question, and solved it. This now works - Page 1 of the table contains names "Brandon" and "James" and page 2 of the table contains names "Jack" and "Jill". The search panes contain "Brandon", "James", "Jack" and "Jill" as expected with pulling all the data, Clicking on "Jack" or "Jill" then updates the table accordingly. But this still has the problem of #3, which I think is really only with my server-side implementation, as client-side does not exhibit this problem for me.
So - to clarify #3 for you:
Let's say pane contains names like above: "Brandon", "James", "Jack" and "Jill". I now click on "Brandon" and the table updates accordingly, which is good, however, now the pane is "losing" the names "James", "Jack" and "Jill" which prevents me from doing a multiple select in the first place. I really think my implementation is botched, but I can't see how to fix it. If I have to guess what is happening here is that my post is going through to my processing function, does the processing, updates the pane, before I get a chance to actually select another name. Perhaps this can be implemented on my side so that the pane does not "remove" the previous names. I do not know how to state it otherwise.
Thanks in advance and I much appreciate your time so far!
Kobus
Oh - and a further artifact I found, which I still need to look into is that the panes are empty again when I paginate. No worries for you to try and figure that out - I have not yet done my due diligence here.
Edit: Which I have done now, and the issue is solved for me :-)
Yup - do a
SELECT DISTINCTon the column in question for each pane. Don't attempt to do the "distinct" part in PHP - no if you have enough data to make server-side processing worth it at least (which is sounds like you do).Do the normal query to get the current page of data, and then do separate queries to get the data for each pane.
I'm with you now. That's the exact same root issue as the initial part. The options are only based on the data being shown on the current page, including the filter, hence why only one option is shown.
As mentioned, use a SELECT DISTINCT on each column for the panes to resolve this.
Allan
Thank you, Allan. I will attempt and revert. Thanks for all your help so far!
Sorry I have not gotten back to you yet. It's been hectic. I will a.s.a.p.
Ah - it works perfectly now, thank you very much! :-)
Great to hear - thanks for the update
Allan