Editor: pageLength: 25 but returns 200k records...
Editor: pageLength: 25 but returns 200k records...
DNSinSC
Posts: 19Questions: 3Answers: 0
Link to test case: It's a development server behind a firewall with no internet access.
Debugger code (debug.datatables.net): I don't want to accidentally upload a list of our subscribers so I didn't choose to upload... but it shows no errors and this information:
emailsubscribers
Data source: Ajax
Processing mode: Server-side
Draws: 1
Columns: 6
Rows - total: 194527
Rows - after search: 194527
Display start: 0
Display length: 25
Error messages shown: No error messages are shown.
Description of problem: I am trying to retrieve 25 rows and it returns the entire table and takes minutes to load.
I have been using Editor for a while on several pages / tables on our site, and it works great, a truly excellent product. Then I went to set up our email subscriber table, which has about 200k records, and it returns ALL the records in the table no matter what I do... I have worked on it today until my eyes are blurry and I am so deep in the woods I can't see the trees anymore. Perhaps a fresh set of eyes can help me see the problem.
The page works but loads and displays 200K records instead of 25. This is the javascript:
<script>
var table = $('#emailsubscribers').DataTable({
processing: true,
serverSide: true,
pageLength: 25,
ajax: "<?= $myPostUrl; ?>",
lengthMenu: [
[10, 25, 50],
[10, 25, 50]
],
columns: [
{
data: "listid",
},
{
data: "emailaddress"
},
{
data: "firstname"
},
{
data: "lastname"
},
{
data: "confirmed",
render: function(data, type, row) {
return type === 'display' ? data ? 'Confirmed' : 'Not Confirmed' : data;
}
},
{
data: "subscribed",
render: function(data, type, row) {
return type === 'display' ? data ? 'Subscribed' : 'Unsubscribed' : data;
}
}
],
select: true
});
} );
}(jQuery));
</script>
This is my php backend, very simple:
```
<?php
use
DataTables\Editor,
DataTables\Editor\Field,
DataTables\Editor\Format,
DataTables\Editor\Mjoin,
DataTables\Editor\Options,
DataTables\Editor\Upload,
DataTables\Editor\Validate,
DataTables\Editor\ValidateOptions;
// Build our Editor instance and process the data coming from _POST
Editor::inst($db, 'emailsubscribers', 'id')
->fields(
Field::inst('listid'),
Field::inst('emailaddress'),
Field::inst('firstname'),
Field::inst('lastname'),
Field::inst('confirmed')
->setFormatter( function ( $val, $data, $field ) {
return $val === 'Confirmed' ? 1 : 0;
}),
Field::inst('subscribed')
->setFormatter( function ( $val, $data, $field ) {
return $val === 'Subscribed' ? 1 : 0;
})
)
->process($_POST)
->json();
It returns the entire table. What am I missing?
This question has an accepted answers - jump to answer
Answers
I'm not familiar with the PHP scripts provided by Datatables but it doesn't look like you are using a server side processing script that restricts the number of rows returned to the page length. See this blog for how to use the Datatables server side processing scripts.
Kevin
If it would help, here is the HTML table:
Again, I'm not familiar with the Datatables supplied libraries but it appears you might be missing this in your PHP script:
See the Writing the controller section of the blog I linked to.
Kevin
I am using the paid version of Editor and not Datatables, and it works fine, albeit very slow with a page displaying 200k records. Most of this functionality just works out of the box in Editor, but not in this particular case. I also read that blog post you linked but I don't see where it mentions restricting the number of rows. The _POST has the start, length, draw fields but the PHP back end returns all rows instead of 0-25 like I think it should...
When I said restricting the number of rows I meant the server script needs to use the start and length parameters to limit the number of rows returned.
Actually you are using two components. Datatables is used to fetch the table data, sending the draw, start, length, etc parameters, The Editor is used for updating the row data in the DB and returning the updated row results to the client. Again I may be mistaken but I think you will need the Datatables portion to get the server side processing piece. See the Editor PHP getting started docs.
Having never used these libraries I could be way off base
Someone else more familiar with the Datatables/Editor supplied server scripts may have a better answer.
Kevin
In the DataTables configuration change:
to be:
Reason: The server-side PHP you show is looking for
->process($_POST), but DataTables' Ajax request defaults to GET. You need to tell DataTables to usePOSTinstead, so the server-side script can see the parameter it being passed.This example demonstrates that in action.
Allan
Bingo! That was it. Thank you so much, Allan.