Multiple selection filter

Multiple selection filter

comptabilitatcomptabilitat Posts: 10Questions: 3Answers: 0

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

This question has an accepted answers - jump to answer

Answers

  • comptabilitatcomptabilitat Posts: 10Questions: 3Answers: 0

    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

  • comptabilitatcomptabilitat Posts: 10Questions: 3Answers: 0
    edited August 13

    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 );
                                })
    
  • allanallan Posts: 63,205Questions: 1Answers: 10,415 Site admin
    Answer ✓

    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

Sign In or Register to comment.