How to pass a dinamic filter to mjoin?

Gargiucn

Good morning, everyone,
I would like to know if it is possible somehow to pass a variable filter to mjon.
Let me explain better, I already have a "static" filter that works fine:

PHP
$editor->join(
    Mjoin::inst( 'attrezzature' )
        ->link( 'attrconper.per_id', 'attrconper_tipo.per_id' )
        ->link( 'attrezzature.atr_id', 'attrconper_tipo.atr_id' )
        ->order( 'atr_descr asc' )
        ->fields(
            Field::inst( 'atr_id' )
                ->validator( Validate::required() )
                ->options( Options::inst()
                    ->table( 'attrezzature' )
                    ->value( 'atr_id' )
                    ->label( 'atr_descr' )
                    ->order( 'atr_descr ASC' )
                    ->where( function ($q) {
                        $q->where( 'atr_az',$_SESSION['azienda'],'=');
                        ---> second filter?
                    } ) 
                ),
            Field::inst( 'atr_id' ),    
            Field::inst( 'atr_descr' )
        )
);

What I would like to do is to add another "dynamic" filter based on the value of another field in the editor.
Basically in a field I select a product category and I would like to be able to select with mjoin one or more products belonging to the category selected before.
Is it possible to do this somehow?
Thanks for your patience...

Giuseppe

Gargiucn

Just a clarification...
The filter in mjoin works correctly if I set it manually:

$q->where( 'atr_descr', 'TEST','=');

I wonder if it is possible somehow to pass the selected value of the previous field and use it as a filter, maybe using editor.dependent().
I did some testing by storing the selected value of the previous field in a session and using it for the filter but it didn't work...

Giuseppe

allan

Hi Giuseppe,

If I'm understanding correctly, it sounds like you want to do cascading options (i.e. the list of options depends upon other values in the form).

Since the list of options can be different for every row in the table, there isn't an Mjoin option for that. Rather you need to make an Ajax call to get the options as the form is displayed, as detailed in that blog post.

Allan

Gargiucn

Good evening allan.
I try to explain what I need:
I need to insert control data in the "controls" table related to groups of documents contained in the "reports" table.
These documents are grouped according to alphanumeric codes defined in the "codes" table.

Table codes
id  code description
1  AAA  document type AAA
2  BBB  document type BBB

Table reports
id code title     date
1 AAA document_1 2023-01-01
2 AAA document_2 2023-05-12
3 BBB document_1 2023-05-19

Table controls
id id_rep  date_ctrl 
1    1     2023-08-24
2    2     2023-08-24
3    3     2023-08-24

When I add a row to the "controls" table, I should select the document family from the "codes" table.
This should create a filter on mjoin so that it displays only reports belonging to the same family and allow me to select the ones I am interested in to add them to the new "controls" row.
The value selected from the "codes" table should become the filter value for mjoin...

I hope I explained myself well...
Thank you,

allan

Mjoin has a where clause, so you could do:

 ->where( 'atr_az',$_SESSION['azienda'])

at the Mjoin level. However, I'm not 100% sure that is what you want? That isn't per row, it is like filtering the whole table to just the given Mjoin option.

Allan

Gargiucn

In editor I wish the selected value of:

attrconper.per_tipo

could be used as a filter in Mjoin...

->where( 'atr_tipo',"value of attrconper.per_tipo")

$editor = Editor::inst( $db, 'attrconper', 'per_id' );
$editor->fields(
    Field::inst( 'attrconper.per_id' )->set( false ),
    Field::inst( 'attrconper.per_az' ),
    Field::inst( 'attrconper.per_tipo' )
        ->options( Options::inst()
            ->table( 'attrezztipo' )
            ->value( 'atp_cod' )
            ->label( ['atp_cod','atp_descr'] )
            ->render( function ( $row ) {
                return $row['atp_cod'].' ('.$row['atp_descr'].')';
            } )                     
            ->order( 'atp_cod ASC' )
            ->where( function ($q) {
                $q->where( 'atp_az',$_SESSION['azienda']);
            } )             
        )   
        ->validator( Validate::dbValues() )
        ->validator( Validate::notEmpty( ValidateOptions::inst()
            ->message( 'Campo obbligatorio' )
        ) ),
    Field::inst( 'attrezztipo.atp_cod' ),   
    Field::inst( 'attrezztipo.atp_descr' ), 
.....
);
$editor->join(
    Mjoin::inst( 'attrezzature' )
        ->link( 'attrconper.per_id', 'attrconper_tipo.per_id' )
        ->link( 'attrezzature.atr_id', 'attrconper_tipo.atr_id' )
        ->order( 'atr_descr asc' )
        ->fields(
            Field::inst( 'atr_id' )
                ->validator( Validate::required() )
                ->options( Options::inst()
                    ->table( 'attrezzature' )
                    ->value( 'atr_id' )
                    ->label( ['atr_descr','atr_matr'] )
                    ->order( 'atr_descr ASC' )
                    ->render( function ( $row ) {
                        return $row['atr_descr']." ".$row['atr_matr']."";;
                    } )
                    ->where( function ($q) {
                        $q->where( 'atr_az',$_SESSION['azienda']);
                        ---> second filter?
                    } )
            ),
            
        )
);
$editor->on( 'preCreate', function ( $editor, $values ) {
    $editor
        ->field( 'attrconper.per_az' )
        ->setValue( $_SESSION['azienda'] ); 
} );
$editor->leftJoin( 'attrezztipo', 'attrezztipo.atp_cod', '=', 'attrconper.per_tipo' );

Giuseppe

allan

That's for the clarification. That is a per row filter - i.e. the list of options would be different for every option.

That is not something that Editor supports out of the box. You would need to get the list of options based on the value of the row being edited, in the same way that the cascade blog post I linked to before does.

Have you had a chance to read over that post?

Allan

Gargiucn

I often use cascading lists but have never tried with mjoin.
I will try to figure out how to do it
Thank you,

Giuseppe