Multiple selection filter

comptabilitat

Hi,

I'm trying to use the multiple selection example: https://editor.datatables.net/examples/datatables/mJoin.html

After adapt to my requirements, the table with all the possible values to allow multi select, has many values so i would like to filter this values.

First of all i get an array with the possible values:

// Realiza la consulta para obtener los microorganismes
$microorganismes = $this->db->select('microorganisme')
                            ->distinct() 
                            ->from('pacients_diagnostics')
                            ->where('npat', $pnpat)
                            ->get()
                            ->result_array();

// Extrae los códigos de microorganismes en un array
**$microorganismes_codis = array_column($microorganismes, 'microorganisme');**

Then i tried with a where clause, but doesn't works:

    ->join(
        Mjoin::inst('microorganismes')
            ->link('aillaments.id', 'aillament_microorganisme.aillament_id')
            ->link('microorganismes.id', 'aillament_microorganisme.microorganisme_id')
            ->order('codi asc')
            ->fields(
                Field::inst('id')
                    ->validator(Validate::required())
                    ->options(Options::inst()
                        ->table('microorganismes')
                        ->value('id')
                        ->label('codi')
                        **->where('codi', $microorganismes_codis, 'IN') // Filtra por los microorganismes obtenidos**
                    ),
                Field::inst('codi')
            )
    )

Without where clause works with all the possible values, but where doesn't works.

Any ideas ??

Many many thanks.

Carlos

comptabilitat

This works:

                            ->where( function ($q) {
                                $q->where( 'codi','ACILWO','=');
                            } ) 

Also this:

                            ->where(function ($q) {
                                $q->where('codi', 'P%', 'like'); // Filtro para el desplegable
                            })

But not this:

                            ->where( function ($q) {
                                $q->where( 'codi',$microorganismes_codis,'IN');

                            } ) 

Neither this:

                            ->where( function ($q) {
                                $q->where( 'codi', ['ACILWO','NOCOD'], 'IN');
                            } ) 

that return this error:

DataTables warning: table id=pacients_aillaments - An SQL error occurred: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''ACILWO' AND codi IN 'NOCOD' )' at line 1

comptabilitat

I founded this solution that worked for me:

                            ->where( function ( $q ) use ($pnpat_trimmed) {
                                $q->where( 'codi', "(SELECT microorganisme FROM pacients_diagnostics WHERE npat = '{$pnpat_trimmed}')", 'IN', false );
                            })

allan

Hi,

Thanks for the updates and sorry I wasn't able to reply to you earlier.

The Mjoin filter is done a little differently from the main table. There are two queries made:

1. Get the main table data
2. Get the Mjoin'ed data

Because there are two queries, a condition on the Mjoin does not effect the main table. That is definitely a limitation of the libraries, which I haven't yet found a cross database way of dealing with. I managed to do it n-levels deep in Postgres (for my CloudTables software, but that was with very specific schemas and a load of Postgres specific stuff.

Allan