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

Gargiucn

I am resuming this discussion because I have not been able to solve the problem.
What I haven't figured out is how to return values so that I can have a selection list in the case of an mjoin.
In the cascading lists example I return values to the selection field in this way:

$select = "SELECT attrezzature.atr_id as value,attrezzature.atr_descr as label 
FROM attrezature LEFT JOIN attrimp_tipo ON attrimp_tipo.imp_id = 
attrimp.imp_id 
WHERE attrimp.imp_az = :azienda
AND attrimp.imp_enable = 1
AND attrimp_tipo.atp_cod = :atrtipo
ORDER BY attrimp.imp_descr ASC";        
    
$filtro = $db
  ->raw()
  ->bind( ':atrtipo', $_POST['per_tipo'] )
  ->bind( ':azienda', $_SESSION['azienda'] ) 
  ->exec( $select )
  ->fetchAll();

echo json_encode([ 
    "options" => [
        'attrezzature.atr_impiego' => $filtro
    ]   
]);

But I didn't understand how to do it with mjoin...
Can I have an example?
Thank you,
Giuseppe

allan

Are you looking for a different list of options for each row? i.e. should your PHP script above be called when a row is edited, sending a different $_POST['per_tipo'] and $filtro?

Allan

Gargiucn

Yes that's right, I pass a category that I selected earlier and the company id and I should display the list of products so that I can select the ones that I'm interested in to which I can attribute, for example, a date and a description of an audit that was done.

Thank you,
Giuseppe

Gargiucn

Actually all the rows in Mjoin that I select have the same category options and company id...

I apologize,
Giuseppe

allan

No problem. If they all have the same list of options, using the Options class as you have done should do the trick. Is that working for you?

Allan