SQL Syntax Error using "$q->limit(15)"

rf1234

I tried to limit the number of rows retrieved for the options to be displayed in selectize at the front end and found this in the documenation:
https://editor.datatables.net/docs/1.5.6/php/class-DataTables.Database.Query.html
This is my PHP Editor instance:

Editor::inst( $db, 'user' )
    ->field(
        Field::inst( 'user.id' )->set( false ),
        Field::inst( 'user.title' )->validator( 'Validate::notEmpty', array('message' => $msg[0]) ),
        Field::inst( 'user.acad' ),
        Field::inst( 'user.firstname' )->validator( 'Validate::notEmpty', array('message' => $msg[0]) ),
        Field::inst( 'user.lastname' )->validator( 'Validate::notEmpty', array('message' => $msg[0]) ),
        Field::inst( 'user.language' )->validator( 'Validate::notEmpty', array('message' => $msg[0]) ),
        Field::inst( 'user.type' )->validator( 'Validate::notEmpty', array('message' => $msg[0]) ),
        Field::inst( 'user.role' )->validator( 'Validate::notEmpty', array('message' => $msg[0]) ),
        Field::inst( 'user.email' )->validator( 'Validate::email', array(
                                                'required' => true,
                                                'message' => $msg[1]) ),
        // fourth parameter of options could be a where clause https://editor.datatables.net/manual/php/joins, null or limit
        //https://editor.datatables.net/docs/1.5.6/php/class-DataTables.Database.Query.html
        Field::inst( 'user.creditor_id' )->options('creditor', 'id', array('name', 'BIC'), 
                                                        function ($q) {
                                                                $q->limit(15);
                                                        },
                                                        function ($row) {
                                                                return $row['name'].' ('.$row['BIC'].')';
                                                        } ),
        Field::inst( 'creditor.name' )->validator( 'Validate::notEmpty', array('message' => $msg[0]) ),
        Field::inst( 'creditor.BIC' ),
        Field::inst( 'user.updater_id' )-> setValue($currentUser),
        Field::inst( 'user.creator_id' )-> set(Field::SET_CREATE)
    )
    ->leftJoin( 'creditor', 'creditor.id', '=', 'user.creditor_id' )
    ->on('preCreate', function ( $editor, $values ) {
        // no acces to $id inside the function - need to access global array again
        $currentUser = filter_var($_SESSION['id']);
        $editor            
            ->field('user.creator_id')
            ->setValue($currentUser);            
    })
   ->on('preCreate', function ( $editor, $values ) {
            // no acces to $id inside the function - need to access global array again
            $userUser = filter_var($_POST['user']);
            $editor            
                ->field('phone.user_id')
                ->setValue($userUser);         
        })
    ->on( 'postCreate', function ( $editor, $id, $values, $row ) {
        logChange( $editor->db(), 'create', $id, $values );
    } )
    ->on( 'postEdit', function ( $editor, $id, $values, $row ) {
        logChange( $editor->db(), 'edit', $id, $values );
    } )
    ->on( 'postRemove', function ( $editor, $id, $values ) {
        logChange( $editor->db(), 'delete', $id, $values );
    } )
    ->process($_POST)
    ->json();

When I run it I get this error:

{"error":"SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') LIMIT 15' at line 1","data":[]}

Looks like there might be something wrong with the SQL generated from the Editor instance because in normal SQL there are no parentheses near LIMIT. Unfortunately I can't see the actual SQL-statement passed to the database. Please help.

I have another question as well. When I start writing something into the Editor Selectize search field does Editor poll the server for each letter I type? Or does editor get all the results at one time and only filters the values that had already been retrieved from the database based on the typing? (This would make a big difference regarding performance. Currently I use Typeahead with over 12,000 database records. The server is polled for each letter typed, but only 15 records are retrieved from the database each time through AJAX. This is very fast.)

allan

This is being caused by using the where parameter for something other than a condition. Its a bug in the PHP libraries that will be fixed in the 1.6 release, but it also wasn't intended to be used that way!

1.6 introduces a new Options class which makes new options such as a limit much easier to make available with a sensible API. In 1.6 you'll be able to do this:

            ->options( Options::inst()
                ->table( 'sites' )
                ->value( 'id' )
                ->label( 'name' )
                ->limit( 3 )
            )

Allan

rf1234

Thanks, Allan. How about my second question?
How does Editor do the search? Does it get all records immediately from the database and then filters those records based on the typing? Or does it poll the server each time a letter is typed into the search field?
With Typeahead I implemented this. (Poll the server each time a letter is typed and retrieve 15 records based on a simple LIKE statement)

$orgLike = "%" . $organization . "%";  
    
    switch ($orgTypeGovOrBank) {
        case "g":
            //For coops we will use the 12digit regional key
            //otherwise the old 8digit key is sufficient
            if ($orgTypeHierarchy == 4) {
                $dbh->query('SELECT name, regional_12  FROM gov
                            WHERE type = :type AND name LIKE :name LIMIT 15');
            } else {
                $dbh->query('SELECT name, regional_8  FROM gov
                            WHERE type = :type AND name LIKE :name LIMIT 15');
            }
            $dbh->bind(':type', $orgTypeHierarchy);
            $dbh->bind(':name', $orgLike);
            $row = $dbh->resultset();

allan

Editor doesn't do any filtering on the options. The Selectize aspect is a plug-in and that I think is configurable using the options that Selectize presents.

Allan

rf1234

ok, Selectize offers to limit the amount of records shown on the dropdown. Select2 unfortunately doesn't. That probably explains why I had such a poor performance with Select2. Many thanks!