Selection by Mjoin condition

Alexandr45

How to make a selection in the server-side handler based on the mjoin connection condition, i.e. by the presence of an entry in the attached table?

allan

I'm afraid I don't quite understand. How is what you are looking for different from this example?

Thanks,
Allan

Alexandr45

I'll try to explain with this example. I need to select only those records from the "users" table for which there are related records in the "user_permission" table.

allan

Oh I see - so if there aren't any permissions for a given user, you wouldn't show them in the list. Only users with permissions would be shown?

That isn't something that the Mjoin class has the ability to do I'm afraid (since it is effectively a left join to the array of "many" data).

However, what you could do is filter out the rows which don't have any permissions. That could be done either with column().search() (if you wanted to show that some records had been filtered out) or ajax.dataSrc if you want to remove them before DataTables sees the data.

Allan

rf1234

Oh I see - so if there aren't any permissions for a given user, you wouldn't show them in the list. Only users with permissions would be shown?

That isn't something that the Mjoin class has the ability to do I'm afraid (since it is effectively a left join to the array of "many" data).

Right but you could use a left join in addition and achieve the desired effect like this. This way you won't see users that don't have a permission for anything.

/*
 * Example PHP implementation used for the join.html example
 */
Editor::inst( $db, 'users' )
    ->field(
        Field::inst( 'users.first_name' ),
        Field::inst( 'users.last_name' ),
        Field::inst( 'users.site' )
            ->options( Options::inst()
                ->table( 'sites' )
                ->value( 'id' )
                ->label( 'name' )
            ),
        Field::inst( 'sites.name' )
    )
    ->leftJoin( 'sites', 'sites.id', '=', 'users.site' )
//add the left join from users to user_permission
    ->leftJoin( 'user_permission', 'user_permission.user_id', '=', 'users.id' )
    ->join(
        Mjoin::inst( 'permission' )
            ->link( 'users.id', 'user_permission.user_id' )
            ->link( 'permission.id', 'user_permission.permission_id' )
            ->order( 'name asc' )
            ->fields(
                Field::inst( 'id' )
                    ->validator( Validate::required() )
                    ->options( Options::inst()
                        ->table( 'permission' )
                        ->value( 'id' )
                        ->label( 'name' )
                    ),
                Field::inst( 'name' )
            )
    )
//check for records that don't have a user_permission and exclude those
    ->where( function($q) {
        $q ->where( 'user_permission.user_id', null, '!=' );
    })
//this is likely to be needed because the left join may lead to the fact that multiple identical user records are being returned even though no field from user_permission is in the selected field list
    ->on( 'postGet', function ( $e, &$data, $id ) use ( $db ) { 
        $data = array_values(array_unique($data, SORT_REGULAR));
    })           
    ->process($_POST)
    ->json();

rf1234

@allan
I remember discussing the necessity of SELECT DISTINCT with you some time ago. You were not convinced. But now I can see more clearly why this is really needed: If you want to check the existence of a child record through a left join with checking for null values (to imitate an inner join) zero, one or more child records may exist. If more than one child record exists you will have multiple parent table records returned even though you don't even have a child record field in the field list!
I could mitigate this using "array_values(array_unique ..." etc. but that shouldn't be the final solution. In my opinion this is a clear case for SELECT DISTINCT to be implemented in Editor.

rf1234

To avoid the issues with the left join and the requirement of SELECT DISTINCT here is another way to achieve the same result and avoid duplicate parent table records:

/*
 * Example PHP implementation used for the join.html example
 */
Editor::inst( $db, 'users' )
    ->field(
        Field::inst( 'users.first_name' ),
        Field::inst( 'users.last_name' ),
        Field::inst( 'users.site' )
            ->options( Options::inst()
                ->table( 'sites' )
                ->value( 'id' )
                ->label( 'name' )
            ),
        Field::inst( 'sites.name' )
    )
    ->leftJoin( 'sites', 'sites.id', '=', 'users.site' )
    ->join(
        Mjoin::inst( 'permission' )
            ->link( 'users.id', 'user_permission.user_id' )
            ->link( 'permission.id', 'user_permission.permission_id' )
            ->order( 'name asc' )
            ->fields(
                Field::inst( 'id' )
                    ->validator( Validate::required() )
                    ->options( Options::inst()
                        ->table( 'permission' )
                        ->value( 'id' )
                        ->label( 'name' )
                    ),
                Field::inst( 'name' )
            )
    )
//check whether the respective user has any permission
    ->where( function($q) {
        $q  ->where( 'users.id',  
               '( SELECT DISTINCT user_id    
                    FROM user_permission
                  )', 'IN', false);
    })
    ->process($_POST)
    ->json();

Alexandr45

Thank you so much. Everything works