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

icefield

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?

colin

Hi @icefield ,

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

Cheers,

Colin

icefield

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

allan

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