Add where clause to editor field option for select

mikepfly2

I used this to solve my initial issue of limiting the options in a drop down so only users who are assigned to a specific project (session variable) are listed. https://datatables.net/forums/discussion/44019/can-we-add-left-join-in-options

The solution seems overly complicated and took me a while to figure out.

I'd like to understand why I can't just use a normal ->where clause in the option closure. I don't understand the purpose of the nested ->where with function() calls. Also, is my use of the session variable appropriate? It's the only way I could get it to work.

$pid = $_SESSION['project']['pid'];
Editor::inst( $db, 'insp_sites' )
    ->fields(
        Field::inst( 'insp_sites.project_id' )
            ->get( false )
            ->setValue( $pid ),
        Field::inst( 'insp_sites.description' )
            ->validator( Validate::notEmpty( ValidateOptions::inst()
                ->message( 'A description is required' )    
            ) )  ,
        Field::inst( 'insp_sites.pointperson_user_id' ) 
            -> options( Options:: inst()
                ->table('users')
                ->value('user_id')
                ->label('fullname')    
                ->order( 'fullname asc' )

                // Why can't I just have this, doesn't work:
                // ->where('users.project_id', $pid);

                // This works, but don't understand why:
                ->where( function($q) {
                    $q ->where( function($r) {
                        $pid = $_SESSION['project']['pid'];
                        $r ->where('users.project_id', $pid); 
                    });
                } )    
            ),
        Field::inst( 'users_fullname_email.fullname' ),
        Field::inst( 'insp_sites.inapp' )
    )
    ->leftJoin ('users','users.user_id', '=','insp_sites.pointperson_user_id')
    ->where('insp_sites.project_id',$pid)                
    ->process( $_POST )
    ->json();

mikepfly2

Found this thread on SO which seems to have the same question/result:
https://stackoverflow.com/questions/51870260/datatables-editor-field-options-where-syntax