Potential Bug in PHP Editor Classes.

nisof

Generated SQL is incorrect

I have recreated this using the sample code.

The PHP Editor classes are not adding the limit clause properly.
THe following SQL is generated. Note some of the queries are duplicated and others do not have the LIMIT clause.

84 Query START TRANSACTION
84 Query SELECT COUNT(id) as cnt FROM `users`
84 Query SELECT COUNT(id) as cnt FROM `users`
84 Query SELECT `id`, `first_name`, `last_name`, `email`, `active`, `password` FROM `users` ORDER BY `first_name` asc LIMIT 8
84 Query SELECT `users`.`id` as _dte_pkey, `dept`.`id`, `dept`.`name` FROM `users` as users JOIN `user_dept` ON `users`.`id` = `user_dept`.`user_id` JOIN `dept` ON `dept`.`id` = `user_dept`.`dept_id`
84 Query SELECT `users`.`id` as _dte_pkey, `access`.`id`, `access`.`name` FROM `users` as users JOIN `user_access` ON `users`.`id` = `user_access`.`user_id` JOIN `access` ON `access`.`id` = `user_access`.`access_id`
84 Query COMMIT
84 Query SELECT `id` as value, `name` as label FROM `dept`
84 Query SELECT `id` as value, `name` as label FROM `access`
84 Quit




Following is the Javascript snippet:
[code]
"bProcessing": true,
"sAjaxSource": '/users.php',
"bAutoWidth": true,
"bLengthChange": true,
"bDeferRender": true,
"bPaginate" : true,

"iDisplayLength": 8,
"bServerSide": true,
"sServerMethod" : "POST",
[/code]
And the PHP

[code]
$editor = Editor::inst( $db, 'users' )
->field(
Field::inst( 'first_name' )->validator( 'Validate::required' ),
Field::inst( 'last_name' )->validator( 'Validate::required' ),
Field::inst( 'email' )->validator( 'Validate::required' ),
Field::inst( 'active' ),
Field::inst( 'password' )->validator( 'Validate::required' )
)
->join(
Join::inst( 'dept', 'object' )
->join(
array( 'id', 'user_id' ),
array( 'id', 'dept_id' ),
'user_dept'
)
->field(
Field::inst( 'id' )->validator( 'Validate::required' ),
Field::inst( 'name' )->validator( 'Validate::required' )
)
)->join(
Join::inst( 'access', 'array' )
->join(
array( 'id', 'user_id' ),
array( 'id', 'access_id' ),
'user_access'
)
->field(
Field::inst( 'id' )->validator( 'Validate::required' ),
Field::inst( 'name' )
)
);

$out = $editor
->process($_POST)
->data();



if ( !isset($_POST['action']) ) {
$out['dept'] = $db
->select( 'dept', 'id as value, name as label' )
->fetchAll();

$out['access'] = $db
->select( 'access', 'id as value, name as label' )
->fetchAll();
}

// Send it back to the client
echo json_encode( $out );
[/code]

allan

Actually this is operating as expected at the moment. It is not optimal - the Join is actually partly done in PHP at this time (hence no limit on some queries), as that is the best way for some cases - but not all! MySQL doesn't support an Array type, which is the main thing holding back a major optimisation here, but having said that, I am doing work for the 1.3 update which will improve performance here.

Allan

nisof

okay - going to have to do these 'manually' then - for some uses its okay, for other datasets in the same app there are 50k-75k rows - it wont work.

allan

What you can do is have DataTables get data from a particular server-side processing source, and have Editor talk to a different one for updating etc. They don't have to use the same script - which might save you from needing to reimplement the editing aspect...

Allan

nisof

yup that is exactly what I am doing - I am reworking the zend classes I have from jQGrid for the grid, and using your editor classes for forms &CRUD (separate to zend for now) and hopefully one way or the other integrated