Where clause in MJoin

kea0811

Hello,

Is it possible to include where clause in MJoin? The where clause will involve both tables.

allan

You can use a where condition on the target table, but you cannot currently have it cross between both tables I'm afraid. The reason for that is that it is actually two different SQL queries - one to get the host table's data and another to get the joined table's data. The library then combines them in PHP.

Allan

rw152

Howdy! I just stumbled upon this issue myself. This is how I got around it:

$editor->where( function ( $q ) {
    $q->where( 'accessLevel', '(SELECT id FROM access WHERE level LIKE "%admin%")', 'IN', false );
} )

The using the closure where function on the "parent" table allowed me to effectively simulate a where clause on the Mjoined table. Hope this helps anyone else stuck in the same situation!

Reference: https://editor.datatables.net/manual/php/conditions#Sub-selects

oroberto

Hi rw152,

I'm myself faced with the issue above and have tried the suggested solution using 'where' without success.
Would You be able to provide a bit more context for Your case (i.e. what does Your Mjoin statement look like etc)? It would be very helpful :-)

Thanks,
Oskar

allan

That's a sub-select, adding a condition to select only rows from the top level table which meet that requirement. So that's at the top level table, rather than in the Mjoin. Clever working around - nice one @rw152.

Allan

oroberto

Hi Allan - Thank You for the comment above!

If the field to use for filtering in the where clause is not in the parent table, this workaround does not work right?

I'm interested in filtering the resulting rows from the Mjoin, which is not a list of rows from the parent table but a list of rows from rightmost of the joined tables.

allan

Right - there needs to be some way of limiting by the parent table. In the case above the condition is on the nested table and then using the sub-select to limit on the parent row.

I'm interested in filtering the resulting rows from the Mjoin

What's the condition you want? You can use a where() on the Mjoin, but it is limited to that table only.

Allan

oroberto

Hi Allan - Thank You for world class response times!

So, my solution has one 'master' table with a large number of columns.
One of those columns is a checkbox multi-select field, which can take on (multiple) values ranging between 1-10.
Each of those values corresponds to an email adress. This integer-email-mapping is stored in the table 'users'.
Each row in the table 'master' is linked to one or more users via the third table 'synliga_rader'. The two fields that connect these tables are 'master.id' and 'synliga_rader.rad_id'
The table 'users' is connected to 'synliga_rader' via the fields 'users.UserID' and 'synliga_rader.user_id_till'.
The solution up to this point, i.e. to select link rows in the master table, i.e. 'master.id' to 'synliga_rader.user_id_till', and to display checkbox labels by linking 'users.UserID' to 'synlig_rader.rad_id', works fine!

Now, I want to filter the resulting rows based on a fourth field 'synliga_rader.user_id_fran', which is in the table 'synliga_rader'.

My solution looks like this:

$editor = Editor::inst( $db, 'master' )
    ->fields(
        Field::inst( 'master.id' )
            ->validator( Validate::notEmpty( ValidateOptions::inst()
                ->message( 'ID missing' )   
            ) ),
        
        ... <OTHER FIELD ROWS> ...
        ... <OTHER FIELD ROWS> ...
        ... <OTHER FIELD ROWS> ...

    ->join(
            Mjoin::inst( 'users' )
                ->link( 'master.id', 'synliga_rader.rad_id' )
                ->link( 'users.UserID', 'synliga_rader.user_id_till' )
                ->order( 'user_id_till asc' )
                ->fields(
                    Field::inst( 'UserID' )
                        ->validator( Validate::required() )
                        ->options( Options::inst()
                            ->table( 'users' )
                            ->value( 'UserID' )
                            ->label( 'Email' )
                            ->order( 'UserID' )
                        ),
                    Field::inst( 'Email' )
                )
                // THIS WHERE CLAUSE IS WHAT I WANT TO ACHIEVE
                // ->where( 'synliga_rader.user_id_fran', <CERTAIN VALUE> )

        )

When You write that a where clause is limited "to that table only", I assume You mean the table 'users' in my case above?
Do You see a way to rearrange the links such that the where clause can be applied on the field 'synliga_rader.user_id_fran'?
Are there other ways to achieve this with DataTables?

If not, does django views run on windows? Do You know how to get started with views? The documentation for views is very limited...

BR,
O

allan

I'm with you - thanks! So the main issue you are going to run into here (even if we found a way to make that condition work) is that the link table (synliga_rader in the above) does not support any more than two columns with the PHP libraries.

The reason for that is that when you do an edit that involves changing the values of the link table, the Mjoin class will actually do a DELETE on the link table for records related to the parent row, and then an INSERT for the new links. Doing a diff would be fairly complicated, so only two columns can be preserved.

Does the relationship have to be a three way link? Or can it be split into two two-way links? I can't think of a way to make the libraries (as they are) support a three way join at the moment I'm afraid.

Allan

oroberto

Hi Allan - Thanks!

Yes, my solution requires a three way link. I will continue to look for a solution to this.

BR,
O