In Editor with PHP backend, how do I filter based on a MJoin table?

kicks66

I have a table with fields like so:

Editor::inst( $db, 'users' )
    ->fields(
        Field::inst( 'username' ),
        Field::inst( 'email' ),
    )
    ->join(
        Mjoin::inst('tags')
            ->link('users.username', 'user_tags.username')
            ->link('tags.id', 'user_tags.tag_id')
            ->fields(
                Field::inst( 'id' )
                  ->options( Options::inst()
                    ->table( 'tags' )
                    ->value( 'id' )
                    ->label( 'tag' )
                ),
                Field::inst( 'tag' )
            )
    )
    ->process( $_POST )
    ->debug(true)
    ->json();

I want to be able to display the data showing only users with a given tag.

I can't seem to find in the documentation how to go about doing this.

I would also like to know if its possible to make it optional, so only when it is in the query params do I apply this filter.

allan

You can add a ->where() to the Mjoin class instance, however that doesn't filter at the top level, which might or might not be what you want. The result would be that it just filters the Mjoined tags, it doesn't filter out users which have a certain tag or not.

If that is what you want you'd need to use a VIEW to do it in a single query. The issue is that Editor uses two queries - one on the top level table (users) in this case and a second on tags to get that information and then joins them in PHP. I was never able to find a cross database way to do an array select that would be needed for that.

Allan