Where clause in search panes

Khalid Teli

Hi ,
I am trying to write a where clause for searchPane option like this

         Field::inst('returns.member','member')
                                ->searchPaneOptions( SearchPaneOptions::inst() 
                                ->table( 'returns' )
                ->value( 'id' )
                ->label( 'member' )
                ->where( function ($q) {
            $q->where( 'member', 'ab foods' );
                })
            )

it works fine but it returns all the instances of member from table (SELECT member from returns where member = 'ab foods'). However, in the ideal case it should only return one instance (sql should be like SELECT DISTINCT member from returns where member = 'ab foods' )

Thank you
KT

rf1234

You can use a closure function for the options as well. That supports executing your own SQL-statement if you like:
https://editor.datatables.net/manual/php/joins#Closure---custom-function

Something like this could work (not tested). It uses the built-in Editor db-handler which is typically called $db. If you called it differently you would need to change that in the code below.

Field::inst('returns.member','member')
    ->searchPaneOptions( function() use ($db) {
        $stmt = ('SELECT DISTINCT id AS `value`, member AS `label` 
                    FROM returns WHERE member = :ab_foods LIMIT 1');  
        $result = $db ->raw()
                      ->bind(':ab_foods','ab foods')
                      ->exec($stmt);`
        return $result->fetchAll(PDO::FETCH_ASSOC);
    }),