Dynamic Where condition based on groups in Editors

Benn Solomon D

Hi,

I need some help on Editors to filter the table data using dynamic where condition to be applied on editor based on the group selection.

Thanks & Regards !

rf1234

So what help is it that you need? "some help" is a little unspecific, isn't it?

But anyway, here is a nice, ugly and complex example of a dynamic where condition with Editor and PHP. I am not saying you should build complex conditions like this, I'd rather not. But it shows you what you can do which is pretty much anything you like.

Take a look at this, too: https://editor.datatables.net/manual/php/conditions

->where( function ( $q ) {
        if ( isset($_SESSION['settingsId']) ) {
            $idLower = $_SESSION['settingsId'];
            $idHigher = $_SESSION['settingsId'];
        } else {
            $idLower = 0;
            $idHigher = 999999999999999999999;
        }
        if (! isset($_SESSION['govCredUserId']) ) {
            $q  ->where( 'user.id', $idLower, '>=' );
            $q  ->where( 'user.id', $idHigher, '<=' );
        } else {
            $q  ->where('user.id',  $_SESSION['govCredUserId'], '!=' ); 
            $q  ->where( function ( $r ) { 
                $r  ->where( 'user.id',  
                    '( SELECT DISTINCT user.id  
                        FROM user, govdept_has_user  
                        WHERE govdept_has_user.govdept_id IN  
                        ( SELECT DISTINCT govdept_id FROM govdept_has_user     
                          WHERE user_id = :id AND role IN ("Administrator", "Principal" ) ) AND  
                        govdept_has_user.user_id = user.id  
                        ORDER BY user.id ASC  
                        )', 'IN', false); 
                $r  ->or_where( 'user.id', 
                    '( SELECT DISTINCT user.id  
                        FROM user, ctr_govdept_has_user_complete  
                        WHERE ctr_govdept_has_user_complete.ctr_govdept_id IN  
                        ( SELECT DISTINCT ctr_govdept_id FROM ctr_govdept_has_user_complete      
                          WHERE user_id = :id AND role IN ("Administrator", "Principal" ) ) AND  
                        ctr_govdept_has_user_complete.user_id = user.id  
                        ORDER BY user.id ASC  
                        )', 'IN', false);  
                //all users that belong to the gov(s) of the respective ctr_installation for which the user is a principal
                $r  ->or_where( 'user.id',
                    '( SELECT DISTINCT a.user_id 
                        FROM gov_has_user a
                  INNER JOIN ctr_installation_has_gov b         ON a.gov_id = b.gov_id    
                  INNER JOIN ctr_installation c                 ON b.ctr_installation_id = c.id
                  INNER JOIN ctr_installation_has_principal d   ON c.id = d.ctr_installation_id                      
                       WHERE d.user_id = :id
                       ORDER BY a.user_id ASC
                       )', 'IN', false);                    
                $r  ->or_where( 'user.id',
                    '( SELECT DISTINCT user.id  
                        FROM user, creditor_has_user  
                        WHERE creditor_has_user.creditor_id IN   
                        ( SELECT DISTINCT creditor_id FROM creditor_has_user     
                          WHERE user_id = :id AND role IN ("Administrator", "Principal" ) ) AND  
                        creditor_has_user.user_id = user.id  
                        ORDER BY user.id ASC  
                        )', 'IN', false);
                $r  ->or_where( 'user.id',  
                    '( SELECT DISTINCT user.id  
                        FROM user, lgf  
                        WHERE user.id IN   
                        ( SELECT DISTINCT user_id FROM lgf ) AND   
                        lgf.user_id = :id   AND    
                        lgf.role IN ("Administrator", "Principal" )  
                        ORDER BY user.id ASC  
                        )', 'IN', false);
                //you can also see users that you have created and/or updated last time
                $r  ->or_where( 'user.updater_id', $_SESSION['govCredUserId'], '=' );
                $r  ->bind( ':id', $_SESSION['govCredUserId'] );
            } );
        }
    } )

allan

based on the group selection.

What is the group data being submitted? What is the condition you are looking for?

The condition documentation might also be of some help.

Allan

Benn Solomon D

Thank you very much for the answers.
My requirement is very much similar to below condition

$editor->where( function ( $q ) use ( $userId ) {
$q->where( 'id', $userId );
} );

My application is in .NET. How to verify the above condition in .NET. Can you please help me on how to pass variables in where conditions in .NET application

allan

The .NET documentation for Editor has a similar example for .NET.

Allan