Possible to limit data based on specific link table field value?

Possible to limit data based on specific link table field value?

icefieldicefield Posts: 45Questions: 19Answers: 1

Is it possible to add a where-clause to https://editor.datatables.net/examples/advanced/joinArray.html such that only users with a particular permission (e.g., 'Printer') are shown?

If so, could you post a snippet of the server-side Editor code with that clause added?

This question has an accepted answers - jump to answer

Answers

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    Hi @icefield ,

    I think this here is what you're after - shout back if no joy,

    Cheers,

    Colin

  • icefieldicefield Posts: 45Questions: 19Answers: 1
    edited January 2019

    Thank you Colin, familiar with that. My question relates more to this documentation: https://editor.datatables.net/manual/php/mjoin#Link-table, and the example linked to in original post. My datatables scenario mimics the example perfectly, though I wish to filter users based on permissions.

    The question relates to applying the where-clause to the permission table while the editor is showing user and permission table data joined via an Mjoin (and related via the user_permission link table).

    Was not sure if adding leftJoin on user_permission link table would mess with CRUD operations updating the user_permission link table (because it was already referenced via an Mjoin).

    After trying several combinations, I did add a leftJoin similar to what I've shown below and I am seeing the link table updated properly. So, I guess the answer to my original question is to use a leftJoin on the user_permission link table to filter users based on their permissions. Updates to the link table are intact.

    Editor::inst( $db, 'users' )
        ->field(
            Field::inst( 'users.first_name' )
        )
        ->join(
            Mjoin::inst( 'permission' )
                ->link( 'users.id', 'user_permission.user_id' )
                ->link( 'permission.id', 'user_permission.access_id' )
                ->order( 'name asc' )
                ->fields(
                    Field::inst( 'id' )
                        ->validator( 'Validate::required' )
                        ->options( Options::inst()
                            ->table( 'permission' )
                            ->value( 'id' )
                            ->label( 'name' ),
                    Field::inst( 'name' )
                )
        )
    
        /* only show users with specific permission */
        ->where( function( $q ) use ( $idAccess /* passed in from UI selection */ ) {
            $q->where( 'user_permission.access_id', $idAccess );
        } )
    
        /* Left Join user_permission table with users table to filter on permissions */
        ->leftJoin( 'user_permission', 'user_permission.user_id, '=' 'users.id )
        ->process($_POST)
        ->json();
    

    All the best,
    Tom

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin
    Answer ✓

    Hi Tom,

    As you have found, there isn't a simple direct way to do this on the server-side. You can apply a where condition to the Mjoin instance, which would limit the permissions sent back to just a specific permission. But because its a left join, users without that permission would still be selected from the parent table - just shown without any permissions.

    Another option would be to use a sub-query to get a list of the users who have the permission wanted, and then base the main condition on that array (i.e. WHERE ... IN ...). That approach is discussed a bit here and is probably the way I would do it myself.

    A final option would be to do the filtering client-side - either before the DataTable gets the data (ajax.dataSrc) or after using client-side filtering.

    Allan

This discussion has been closed.