Feed a select field with data from multiple mysql tables

Feed a select field with data from multiple mysql tables

carrarachristophecarrarachristophe Posts: 97Questions: 22Answers: 2

Hello,

I am using the following:

        Field::inst( 'bibliotheque_bibliotheques.oeuvre' )
            ->options( Options::inst()
                ->table( 'bibliotheque_oeuvres' )
                ->value( 'oeuvre_id' )
                ->label( array('oeuvre_id', 'titre',  'tome',  'auteur') )
                ->render( function ( $row ) {
                    if ( $row['tome'] & $row['auteur']) {
                    return $row['oeuvre_id'].' - '.$row['titre'].' ('.$row['tome'].') - ' .$row['auteur'] ;
                    }
                    if ( $row['tome']) {
                    return $row['oeuvre_id'].' - '.$row['titre'].' ('.$row['tome'].')' ;
                    }
                    return $row['oeuvre_id'].' - '.$row['titre'].' - ' .$row['auteur'].'' ;
                 }))
            ->validator( Validate::notEmpty( ValidateOptions::inst()
                ->message( 'Requis' ))),

To feed a select2 field:

            {
                label: "Oeuvre:",
                name: "bibliotheque_bibliotheques.oeuvre",
                type: "select2"
            },

The problem is that:
* if oeuvre_id, titre and tome are all part of the mysql table bibliotheque_oeuvres
* I would rather like to use the field bibliotheque_auteurs.auteur (the name) instead of auteur (the id, foreign key)

Anyone would know how I can trick the above code to achieve that?

Answers

  • allanallan Posts: 61,433Questions: 1Answers: 10,049 Site admin

    One option is to use the the Options class with a function that will get the data from the database using a join.

    Another option is to use a VIEW in the database which will do the join and then you just read from the VIEW (rather than using the table name, use the view name in the options class).

    Allan

  • carrarachristophecarrarachristophe Posts: 97Questions: 22Answers: 2

    Hi Allan,
    Are you suggesting to replace that part

    ->options( Options::inst()
    

    by

    ->options( function () {
    

    correct?

    But how can I replace the value and label (closed list in the example) by some fields from various tables?

  • allanallan Posts: 61,433Questions: 1Answers: 10,049 Site admin

    Correct. Something like:

    function () use ($db) {
      $res = $db->query( 'select' )
        ->table( 'bibliotheque_oeuvres' )
        ->get( 'oeuvre_id', 'titre', 'auteur', ... )
        ->join( 'table2', 'bibliotheque_oeuvres.auteur = bibliotheque_auteurs.auteur' )
        ->order( ... )
        ->exec();
    
      $out = [];
      $rows = $res->fetchAll();
    
      for ($i=0 ; $i<count($rows) ; $i++) {
        $out[] = [
          'value' => $row['oeuvre_id'],
          'label' => ...
         ];
      }
    
      return $out;
    }
    

    A few gaps to be filled in, but hopefully that will help!

    Allan

  • carrarachristophecarrarachristophe Posts: 97Questions: 22Answers: 2

    Hi Allan,
    Thank you for the tip.
    Unfortunatly, I cannoy get it work, even when I simplify it at its maximum:

            Field::inst( 'bibliotheque_bibliotheques.oeuvre' )
                ->options( function () use ($db) {
                    $res = $db->query( 'select' )
                        ->table( 'bibliotheque_oeuvres' )
                        ->get( 'oeuvre_id', 'titre')
                        ->exec();
    
                    $out = [];
                    $rows = $res->fetchAll();
    
                    for ($i=0 ; $i<count($rows) ; $i++) {
                        $out[] = [
                            **'value' => $row['oeuvre_id'],**
                            'label' => $row['titre']
                        ];
                    }
    
                    return $out;
                    } )
    

    I am getting the following message, related to the above line 13:

    Notice: Undefined variable: row in C:\xampp\htdocs\carrara.family\bibliotheque\php\table.bibliotheque_bibliotheques.php on line 39

    Notice: Trying to access array offset on value of type null in C:\xampp\htdocs\carrara.family\bibliotheque\php\table.bibliotheque_bibliotheques.php on line 39

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    My PHP isn't best, but shouldn't lines 13 and 14 be referencing $rows[i], as the error says row is undefined. Also, you're in a loop - the value of $out is being reset on each iteration,

    Colin

  • carrarachristophecarrarachristophe Posts: 97Questions: 22Answers: 2

    Thanks Colin.
    I think this is out of my abilities.
    There is no way to do that in the js side instead?

  • allanallan Posts: 61,433Questions: 1Answers: 10,049 Site admin
    edited September 2022

    No - you need to get the information from the database, so you must do this server-side.

    It is exactly as Colin says, you need to use:

    $rows[$i]['oeuvre_id']
    

    Or you could have $row = $rows[$i] at the top of the loop.

    Allan

  • carrarachristophecarrarachristophe Posts: 97Questions: 22Answers: 2

    Dear both,
    Thank. But unfortunately I could not get it work.

    I am getting the following message:
    Notice: Undefined index: bibliotheque_oeuvres.oeuvre_id in C:\xampp\htdocs\carrara.family\bibliotheque\php\table.bibliotheque_bibliotheques.php on line 39
    Notice: Undefined index: titre in C:\xampp\htdocs\carrara.family\bibliotheque\php\table.bibliotheque_bibliotheques.php on line 40

    Please note that I tried with and without the table name.

            Field::inst( 'bibliotheque_bibliotheques.oeuvre' )
                ->options( function () use ($db) {
                    $res = $db->query( 'select' )
                        ->table( 'bibliotheque_oeuvres' )
                        ->get( 'oeuvre_id', 'titre')
                        ->exec();
    
                    $out = [];
                    $rows = $res->fetchAll();
    
                    for ($i=0 ; $i<count($rows) ; $i++) {
                        $out[] = [
                            'value' => $rows['bibliotheque_oeuvres.oeuvre_id'],
                            'label' => $rows['titre']
                        ];
                    }
    
                    return $out;
                    } )
    
  • tangerinetangerine Posts: 3,342Questions: 35Answers: 394

    You seem to have ignored the advice you were given by Colin and Allan.

  • allanallan Posts: 61,433Questions: 1Answers: 10,049 Site admin

    Try:

    $rows['bibliotheque_oeuvres']['oeuvre_id']
    

    Allan

  • carrarachristophecarrarachristophe Posts: 97Questions: 22Answers: 2

    Thank you Allan.
    It works with the following code:

            Field::inst( 'bibliotheque_bibliotheques.oeuvre' )
                ->options( function () use ($db) {
                    $res = $db->query( 'select' )
                        ->table( 'bibliotheque_oeuvres' )
                        ->get( 'oeuvre_id', 'titre', 'auteur', 'tome')
                        ->exec();
    
                $out = [];
                $rows = $res->fetchAll();
    
                for ($i=0 ; $i<count($rows) ; $i++) {
                    $out[] = [
                        'value' => $rows[$i]['oeuvre_id'],
                        'label' => ( array( $rows[$i]['oeuvre_id'].' - '.$rows[$i]['titre'].' ('.$rows[$i]['tome'].') - ' .$rows[$i]['auteur'] ) )
                    ];
                }
    
                return $out;
            } )
    
    

    But in addition to be missing the if conditions, on which I will work later, I am back to my initial problem, with bibliotheque_oeuvres.auteur quoted instead of bibliotheque_auteurs.auteur.
    How can I had a leftjoin to the code? I tried the following:

            Field::inst( 'bibliotheque_bibliotheques.oeuvre' )
                ->options( function () use ($db) {
                    $res = $db->query( 'select' )
                        ->table( 'bibliotheque_oeuvres', 'bibliotheque_auteurs' )
                        ->get( 'oeuvre_id', 'titre', 'auteur', 'bibliotheque_auteurs.auteur', 'tome')
                        ->exec();
    
                $out = [];
                $rows = $res->fetchAll();
    
                for ($i=0 ; $i<count($rows) ; $i++) {
                    $out[] = [
                        'value' => $rows[$i]['oeuvre_id'],
                        'label' => ( array( $rows[$i]['oeuvre_id'].' - '.$rows[$i]['titre'].' ('.$rows[$i]['tome'].') - ' .$rows[$i]['bibliotheque_auteurs.auteur'] ) )
                    ];
                }
    
                return $out;
            } )
    
    

    But am getting the following message:
    DataTables warning: table id=bibliotheque_bibliotheques - An SQL error occurred: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'bibliotheque_auteurs.auteur' in 'field list'

  • allanallan Posts: 61,433Questions: 1Answers: 10,049 Site admin

    How can I had a leftjoin to the code?

    Use the join method or the newer left_join (added because it is so common).

            $res = $db->query( 'select' )
                ->table( 'bibliotheque_oeuvres' )
                ->get( 'oeuvre_id', 'titre', 'auteur', 'bibliotheque_auteurs.auteur', 'tome')
                ->join( 'bibliotheque_auteurs', 'condition', 'LEFT' )
                ->exec();
    

    Where condition is replaced with whatever your join condition is. Probably something like bibliotheque_oeuvres.auteur = bibliotheque_auteurs.id.

    Allan

  • carrarachristophecarrarachristophe Posts: 97Questions: 22Answers: 2

    Thank you Allan,
    Here is the code that works for me:

            Field::inst( 'bibliotheque_bibliotheques.oeuvre' )
                ->options( function () use ($db) {
                    $res = $db->query( 'select' )
                        ->table( 'bibliotheque_oeuvres' )
                        ->get( 'oeuvre_id', 'titre', 'bibliotheque_auteurs.auteur', 'tome')
                        ->join( 'bibliotheque_auteurs', 'bibliotheque_oeuvres.auteur = bibliotheque_auteurs.auteur_id', 'LEFT' )
                        ->exec();
             
                        $out = [];
                        $rows = $res->fetchAll();
                     
                        for ($i=0 ; $i<count($rows) ; $i++) {
                            $out[] = [
                                'value' => $rows[$i]['oeuvre_id'],
                                'label' => ( array( $rows[$i]['oeuvre_id'].' - '.$rows[$i]['titre'].' ('.$rows[$i]['tome'].') - ' .$rows[$i]['bibliotheque_auteurs.auteur'] ) )
                            ];
                        }
                     
                        return $out;
                } )
    

    What I am just missing now is to include in label something close to what I used to have in render, for example only display $rows[$i]['tome'] in the array only if this field exists.
    Should I use something like if (in_array(?

  • carrarachristophecarrarachristophe Posts: 97Questions: 22Answers: 2
    edited April 2023

    Dear all,
    I tried the below without success:

    Field::inst( 'bibliotheque_bibliotheques.oeuvre' )
        ->options( function () use ($db) {
            $res = $db->query( 'select' )
                ->table( 'bibliotheque_oeuvres' )
                ->get( 'oeuvre_id', 'titre', 'bibliotheque_auteurs.auteur', 'tome')
                ->join( 'bibliotheque_auteurs', 'bibliotheque_oeuvres.auteur = bibliotheque_auteurs.auteur_id', 'LEFT' )
                ->exec();
     
                $out = [];
                $rows = $res->fetchAll();
             
                for ($i=0 ; $i<count($rows) ; $i++) {
                    $out[] = [
                        'value' => $rows[$i]['oeuvre_id'],
    //                          'label' => array( $rows[$i]['oeuvre_id'].' - '.$rows[$i]['titre'].' ('.$rows[$i]['tome'].') - ' .$rows[$i]['bibliotheque_auteurs.auteur'] ),
                        'render' => function ( $rows ) {
                            if ( $rows[$i]['tome'] & $rows[$i]['auteur']) {
                                array( $rows[$i]['oeuvre_id'].' - '.$rows[$i]['titre'].' ('.$rows[$i]['tome'].') - ' .$rows[$i]['bibliotheque_auteurs.auteur'] ) ;
                            }
                            if ( $rows[$i]['tome']) {
                                $rows[$i]['oeuvre_id'].' - '.$rows[$i]['titre'].' ('.$rows[$i]['tome'].')' ;
                            }
                                $rows[$i]['oeuvre_id'].' - '.$rows[$i]['titre'].' - ' .$rows[$i]['bibliotheque_auteurs.auteur'].'' ;
                         }
                    ];
                }
             
                return $out;
        } )
        ->validator( Validate::notEmpty( ValidateOptions::inst()
            ->message( 'Requis' ))),
    
    • when I keep the label part (line 15), the render part is not taken into account (label is displayed)
    • when I remove the label part (line 15), the render part gives a blank info in the select field
    • I tried to return using array (line 18) or not (line 21), no impact
    • I initially used 'render' => function ( $rows[$i] ) { in line 16 but ir returns a http://datatables.net/tn/7 error message

    Can anyone help me include some if conditions?

  • allanallan Posts: 61,433Questions: 1Answers: 10,049 Site admin

    You don't have a return statement in your rendering function. So it is effectively a no-op. You probably want returns on line 21 and 23. It should return the string to display, not an array.

    Allan

  • carrarachristophecarrarachristophe Posts: 97Questions: 22Answers: 2

    Hi Allan,

    Adding return on line 21 and 23 does not solve the issue. The select field is still blank.

  • allanallan Posts: 61,433Questions: 1Answers: 10,049 Site admin

    Can you show me your latest code please?

    Also if you could enable debug mode (add ->debug(true) just before the ->process(...) call, and then send me the JSON response from the server from the Ajax request for the table load, that would be useful.

    Thanks,
    Allan

  • carrarachristophecarrarachristophe Posts: 97Questions: 22Answers: 2
    edited August 2023

    Hi Allan,
    Here is my latest code:

    Editor::inst( $db, 'bibliotheque_bibliotheques', 'bibliotheque_id' )
        ->fields(
            Field::inst( 'bibliotheque_bibliotheques.utilisateur' )
                ->options( Options::inst()
                    ->table( 'utilisateurs' )
                    ->value( 'utilisateur_id' )
                    ->label( 'utilisateur' ))
                ->validator( Validate::notEmpty( ValidateOptions::inst()
                    ->message( 'Requis' ))),
            Field::inst( 'bibliotheque_bibliotheques.oeuvre' )
                ->options( function () use ($db) {
                    $res = $db->query( 'select' )
                        ->table( 'bibliotheque_oeuvres' )
                        ->get( 'oeuvre_id', 'titre', 'bibliotheque_auteurs.auteur', 'tome')
                        ->join( 'bibliotheque_auteurs', 'bibliotheque_oeuvres.auteur = bibliotheque_auteurs.auteur_id', 'LEFT' )
                        ->exec();
             
                $out = [];
                $rows = $res->fetchAll();
             
                for ($i=0 ; $i<count($rows) ; $i++) {
                    $out[] = [
                        'value' => $rows[$i]['oeuvre_id'],
    /*                  'label' => array( $rows[$i]['oeuvre_id'].' - '.$rows[$i]['titre'].' ('.$rows[$i]['tome'].') - ' .$rows[$i]['bibliotheque_auteurs.auteur'] ),*/
                        'render' => function ( $rows ) {
                            if ( $rows[$i]['tome'] & $rows[$i]['auteur']) {
                                array ( $rows[$i]['oeuvre_id'].' - '.$rows[$i]['titre'].' ('.$rows[$i]['tome'].') - ' .$rows[$i]['bibliotheque_auteurs.auteur'] ) ;
                            }
                            if ( $rows[$i]['tome']) {
                                $rows[$i]['oeuvre_id'].' - '.$rows[$i]['titre'].' ('.$rows[$i]['tome'].')' ;
                            }
                                $rows[$i]['oeuvre_id'].' - '.$rows[$i]['titre'].' - ' .$rows[$i]['bibliotheque_auteurs.auteur'].'' ;
                        }
                    ];
                }
                return $out;
        } )
        ->validator( Validate::notEmpty( ValidateOptions::inst()
                    ->message( 'Requis' ))),
            Field::inst( 'bibliotheque_oeuvres.type' )
                ->options( Options::inst()
                    ->table( 'bibliotheque_types' )
                    ->value( 'type_id' )
                    ->label( 'type' )),
            Field::inst( 'bibliotheque_oeuvres.serie' )
                ->options( Options::inst()
                    ->table( 'bibliotheque_series' )
                    ->value( 'serie_id' )
                    ->label( 'serie' )),
            Field::inst( 'bibliotheque_oeuvres.auteur' )
                ->options( Options::inst()
                    ->table( 'bibliotheque_auteurs' )
                    ->value( 'auteur_id' )
                    ->label( 'auteur' ))
                ->validator( Validate::dbValues() ),
            Field::inst( 'bibliotheque_bibliotheques.possession' ),
            Field::inst( 'bibliotheque_bibliotheques.date_achat' )
                ->validator( Validate::dateFormat( 'd/m/Y' ) )
                ->getFormatter( Format::datetime( 'Y-m-d', 'd/m/Y' ) )
                ->setFormatter( Format::datetime( 'd/m/Y', 'Y-m-d' ) ),
            Field::inst( 'bibliotheque_bibliotheques.prix_achat' )
                ->setFormatter( Format::ifEmpty( null ) ),
            Field::inst( 'bibliotheque_bibliotheques.neuf' ),
            Field::inst( 'bibliotheque_bibliotheques.voeux' ),
            Field::inst( 'bibliotheque_oeuvres.oeuvre_id' ),
            Field::inst( 'bibliotheque_oeuvres.titre' ),
            Field::inst( 'bibliotheque_oeuvres.cycle' ),
            Field::inst( 'bibliotheque_oeuvres.tome' ),
            Field::inst( 'utilisateurs.utilisateur' ),
            Field::inst( 'bibliotheque_oeuvres.pages' ),
            Field::inst( 'bibliotheque_oeuvres.format' ),
            Field::inst( 'bibliotheque_oeuvres.langue' ),
            Field::inst( 'bibliotheque_oeuvres.parution' )
                ->validator( Validate::dateFormat( 'd/m/Y' ) )
                ->getFormatter( Format::datetime( 'Y-m-d', 'd/m/Y' ) )
                ->setFormatter( Format::datetime( 'd/m/Y', 'Y-m-d' ) ),
            Field::inst( 'bibliotheque_oeuvres.prix' ),
            Field::inst( 'bibliotheque_oeuvres.resume' ),
            Field::inst( 'bibliotheque_editeurs.editeur' ),
            Field::inst( 'bibliotheque_collections.collection' ),
            Field::inst( 'bibliotheque_types.type' ),
            Field::inst( 'bibliotheque_genres.genre' ),
            Field::inst( 'langues.langue' ),
            Field::inst( 'bibliotheque_series.serie' ),
            Field::inst( 'bibliotheque_auteurs.auteur' ),
            Field::inst( 'bibliotheque_coloristes.coloriste' ),
            Field::inst( 'bibliotheque_dessinateurs.dessinateur' ),
            Field::inst( 'bibliotheque_scenaristes.scenariste' )
        )
        ->leftJoin( 'utilisateurs', 'utilisateurs.utilisateur_id', '=', 'bibliotheque_bibliotheques.utilisateur' )
        ->leftJoin( 'bibliotheque_oeuvres', 'bibliotheque_oeuvres.oeuvre_id', '=', 'bibliotheque_bibliotheques.oeuvre' )
        ->leftJoin( 'bibliotheque_auteurs', 'bibliotheque_auteurs.auteur_id', '=', 'bibliotheque_oeuvres.auteur' )
        ->leftJoin( 'bibliotheque_coloristes', 'bibliotheque_coloristes.coloriste_id', '=', 'bibliotheque_oeuvres.coloriste' )
        ->leftJoin( 'bibliotheque_dessinateurs', 'bibliotheque_dessinateurs.dessinateur_id', '=', 'bibliotheque_oeuvres.dessinateur' )
        ->leftJoin( 'bibliotheque_scenaristes', 'bibliotheque_scenaristes.scenariste_id', '=', 'bibliotheque_oeuvres.scenariste' )
        ->leftJoin( 'bibliotheque_series', 'bibliotheque_series.serie_id', '=', 'bibliotheque_oeuvres.serie' )
        ->leftJoin( 'bibliotheque_types', 'bibliotheque_types.type_id', '=', 'bibliotheque_oeuvres.type' )
        ->leftJoin( 'bibliotheque_genres', 'bibliotheque_genres.genre_id', '=', 'bibliotheque_oeuvres.genre' )
        ->leftJoin( 'bibliotheque_editeurs', 'bibliotheque_editeurs.editeur_id', '=', 'bibliotheque_oeuvres.editeur' )
        ->leftJoin( 'bibliotheque_collections', 'bibliotheque_collections.collection_id', '=', 'bibliotheque_oeuvres.collection' )
        ->leftJoin( 'langues', 'langues.langue_id', '=', 'bibliotheque_oeuvres.langue' )
        ->where( 'bibliotheque_bibliotheques.possession', 1 )
        ->process( $_POST )
        ->json();
    
  • tangerinetangerine Posts: 3,342Questions: 35Answers: 394

    Allan needs the debug info, which will be at the tail-end of your JSON response.

  • carrarachristophecarrarachristophe Posts: 97Questions: 22Answers: 2

    My apologies:

    And an excerpt of the json response:

    "debug":["Editor PHP libraries - version 2.2.2",{"query":"SELECT  `bibliotheque_bibliotheques`.`bibliotheque_id` as 'bibliotheque_bibliotheques.bibliotheque_id', `bibliotheque_bibliotheques`.`utilisateur` as 'bibliotheque_bibliotheques.utilisateur', `bibliotheque_bibliotheques`.`oeuvre` as 'bibliotheque_bibliotheques.oeuvre', `bibliotheque_oeuvres`.`type` as 'bibliotheque_oeuvres.type', `bibliotheque_oeuvres`.`serie` as 'bibliotheque_oeuvres.serie', `bibliotheque_oeuvres`.`auteur` as 'bibliotheque_oeuvres.auteur', `bibliotheque_bibliotheques`.`possession` as 'bibliotheque_bibliotheques.possession', `bibliotheque_bibliotheques`.`date_achat` as 'bibliotheque_bibliotheques.date_achat', `bibliotheque_bibliotheques`.`prix_achat` as 'bibliotheque_bibliotheques.prix_achat', `bibliotheque_bibliotheques`.`neuf` as 'bibliotheque_bibliotheques.neuf', `bibliotheque_bibliotheques`.`voeux` as 'bibliotheque_bibliotheques.voeux', `bibliotheque_oeuvres`.`oeuvre_id` as 'bibliotheque_oeuvres.oeuvre_id', `bibliotheque_oeuvres`.`titre` as 'bibliotheque_oeuvres.titre', `bibliotheque_oeuvres`.`cycle` as 'bibliotheque_oeuvres.cycle', `bibliotheque_oeuvres`.`tome` as 'bibliotheque_oeuvres.tome', `utilisateurs`.`utilisateur` as 'utilisateurs.utilisateur', `bibliotheque_oeuvres`.`pages` as 'bibliotheque_oeuvres.pages', `bibliotheque_oeuvres`.`format` as 'bibliotheque_oeuvres.format', `bibliotheque_oeuvres`.`langue` as 'bibliotheque_oeuvres.langue', `bibliotheque_oeuvres`.`parution` as 'bibliotheque_oeuvres.parution', `bibliotheque_oeuvres`.`prix` as 'bibliotheque_oeuvres.prix', `bibliotheque_oeuvres`.`resume` as 'bibliotheque_oeuvres.resume', `bibliotheque_editeurs`.`editeur` as 'bibliotheque_editeurs.editeur', `bibliotheque_collections`.`collection` as 'bibliotheque_collections.collection', `bibliotheque_types`.`type` as 'bibliotheque_types.type', `bibliotheque_genres`.`genre` as 'bibliotheque_genres.genre', `langues`.`langue` as 'langues.langue', `bibliotheque_series`.`serie` as 'bibliotheque_series.serie', `bibliotheque_auteurs`.`auteur` as 'bibliotheque_auteurs.auteur', `bibliotheque_coloristes`.`coloriste` as 'bibliotheque_coloristes.coloriste', `bibliotheque_dessinateurs`.`dessinateur` as 'bibliotheque_dessinateurs.dessinateur', `bibliotheque_scenaristes`.`scenariste` as 'bibliotheque_scenaristes.scenariste' FROM  `bibliotheque_bibliotheques` LEFT JOIN `utilisateurs` ON `utilisateurs`.`utilisateur_id` = `bibliotheque_bibliotheques`.`utilisateur`  LEFT JOIN `bibliotheque_oeuvres` ON `bibliotheque_oeuvres`.`oeuvre_id` = `bibliotheque_bibliotheques`.`oeuvre`  LEFT JOIN `bibliotheque_auteurs` ON `bibliotheque_auteurs`.`auteur_id` = `bibliotheque_oeuvres`.`auteur`  LEFT JOIN `bibliotheque_coloristes` ON `bibliotheque_coloristes`.`coloriste_id` = `bibliotheque_oeuvres`.`coloriste`  LEFT JOIN `bibliotheque_dessinateurs` ON `bibliotheque_dessinateurs`.`dessinateur_id` = `bibliotheque_oeuvres`.`dessinateur`  LEFT JOIN `bibliotheque_scenaristes` ON `bibliotheque_scenaristes`.`scenariste_id` = `bibliotheque_oeuvres`.`scenariste`  LEFT JOIN `bibliotheque_series` ON `bibliotheque_series`.`serie_id` = `bibliotheque_oeuvres`.`serie`  LEFT JOIN `bibliotheque_types` ON `bibliotheque_types`.`type_id` = `bibliotheque_oeuvres`.`type`  LEFT JOIN `bibliotheque_genres` ON `bibliotheque_genres`.`genre_id` = `bibliotheque_oeuvres`.`genre`  LEFT JOIN `bibliotheque_editeurs` ON `bibliotheque_editeurs`.`editeur_id` = `bibliotheque_oeuvres`.`editeur`  LEFT JOIN `bibliotheque_collections` ON `bibliotheque_collections`.`collection_id` = `bibliotheque_oeuvres`.`collection`  LEFT JOIN `langues` ON `langues`.`langue_id` = `bibliotheque_oeuvres`.`langue` WHERE `bibliotheque_bibliotheques`.`possession` = :where_0 ","bindings":[{"name":":where_0","value":1,"type":null}]},{"query":"SELECT DISTINCT  `utilisateur_id` as 'utilisateur_id', `utilisateur` as 'utilisateur' FROM  `utilisateurs` ","bindings":[]},{"query":"SELECT  `oeuvre_id` as 'oeuvre_id', `titre` as 'titre', `bibliotheque_auteurs`.`auteur` as 'bibliotheque_auteurs.auteur', `tome` as 'tome' FROM  `bibliotheque_oeuvres` LEFT JOIN `bibliotheque_auteurs` ON `bibliotheque_oeuvres`.`auteur` = `bibliotheque_auteurs`.`auteur_id` ","bindings":[]},{"query":"SELECT DISTINCT  `type_id` as 'type_id', `type` as 'type' FROM  `bibliotheque_types` ","bindings":[]},{"query":"SELECT DISTINCT  `serie_id` as 'serie_id', `serie` as 'serie' FROM  `bibliotheque_series` ","bindings":[]},{"query":"SELECT DISTINCT  `auteur_id` as 'auteur_id', `auteur` as 'auteur' FROM  `bibliotheque_auteurs` ","bindings":[]}]
    
  • allanallan Posts: 61,433Questions: 1Answers: 10,049 Site admin

    Thanks. I can see for that debug trace that it is executing your query:

      SELECT
        `oeuvre_id` as 'oeuvre_id',
        `titre` as 'titre',
        `bibliotheque_auteurs`.`auteur` as 'bibliotheque_auteurs.auteur',
        `tome` as 'tome'
      FROM  `bibliotheque_oeuvres`
      LEFT JOIN `bibliotheque_auteurs` ON `bibliotheque_oeuvres`.`auteur` = `bibliotheque_auteurs`.`auteur_id`
    

    Looking at your current code, I can see that you still don't have a return statement in your render function:

                        'render' => function ( $rows ) {
                            if ( $rows[$i]['tome'] & $rows[$i]['auteur']) {
                                array ( $rows[$i]['oeuvre_id'].' - '.$rows[$i]['titre'].' ('.$rows[$i]['tome'].') - ' .$rows[$i]['bibliotheque_auteurs.auteur'] ) ;
                            }
                            if ( $rows[$i]['tome']) {
                                $rows[$i]['oeuvre_id'].' - '.$rows[$i]['titre'].' ('.$rows[$i]['tome'].')' ;
                            }
                                $rows[$i]['oeuvre_id'].' - '.$rows[$i]['titre'].' - ' .$rows[$i]['bibliotheque_auteurs.auteur'].'' ;
                        }
    

    That function just always returns undefined.

    However, I don't think that is actually the issue in retrospect. If you use a custom function to create the options for a select input, you are required to return an array with objects that contain label and value properties. You are currently returning value and render. label is commented out for some reason? Also the render function wouldn't be executed.

    I would also suggest adding some debug to make sure your SQL query is actually returning any results - i.e. what is the value of $rows?

    Allan

  • carrarachristophecarrarachristophe Posts: 97Questions: 22Answers: 2

    Hi Allan,

    I neutralised the label part because when present, this is what is rendered rather than the render part of the code.

    I removed the return because I am getting an error message. I think it is not needed anyway as return $out; should be sufficient.
    Would you have any working example of any kind of a 'render' => function ( $rows ) {?

Sign In or Register to comment.