Dude inner join

Dude inner join

rompeplatosrompeplatos Posts: 20Questions: 4Answers: 0

I have this:

program: id, id_sala, text
sala: id, id_congress, text
congress: id, text

And I need this:

SELECT * FROM program INNER JOIN sala ON program.id_sala = sala.id where sala.id_congress=1

When I show all the elements of the table "sala" in the datatable "program"
( https://image.prntscr.com/image/r6tdufxtSSGiJ8j3WedU4A.png )
I only want the sala with "id_congress"="1" When I'm going to make a new program

This question has an accepted answers - jump to answer

Answers

  • allanallan Posts: 63,356Questions: 1Answers: 10,444 Site admin

    What code are you using to populate the list of options for the select list?

    Allan

  • rompeplatosrompeplatos Posts: 20Questions: 4Answers: 0

    in server:

    <?php
    
    // DataTables PHP library
    include( "php/DataTables.php" );
    
    // Alias Editor classes so they are easy to use
    use DataTables\Editor,
        DataTables\Editor\Field,
        DataTables\Editor\Format,
        DataTables\Editor\Mjoin,
        DataTables\Editor\Options,
        DataTables\Editor\Upload,
        DataTables\Editor\Validate;
    
    /*
     * Example PHP implementation used for the joinSelf.html example - the basic idea
     * here is that the join performed is simply to get extra information about the
     * 'manager' (in this case, the name of the manager). To alter the manager for
     * a user, you would change the 'manager' value in the 'users' table, so the
     * information from the join is read-only.
     */
    
    $id = $_GET["id"];
    
    Editor::inst( $db, 'program' )
        ->field(
            Field::inst( 'program.id' ),
            Field::inst( 'program.text' ),
            Field::inst( 'program.id_sala' )
                ->options( Options::inst()
                    ->table( 'sala' )
                    ->value( 'id' )
                    ->label( 'text' )
                ),
            Field::inst( 'sala.text' )
    
         )
        ->leftJoin( 'sala', 'sala.id', '=', 'program.id_sala' )
        ->where( 'sala.id_congress', $id ) //this not work
        ->process($_POST)
        ->json();
    
    

    js:

     editor = new $.fn.dataTable.Editor({
                ajax: "server.php?id=3",
                table: "#table",
                fields: [{
                            label: "Text:",
                            name: "program.text"
                        },{
                            label: "Nombre Sala:",
                            name: "program.id_sala",
                            type: "select",
                            placeholder: "Select sala"
                        }    
            });
    
  • allanallan Posts: 63,356Questions: 1Answers: 10,444 Site admin
    Answer ✓

    Move your where condition into the Options instance's chain - e.g.:

    Options::inst()
      ->table( 'sala' )
      ->value( 'id' )
      ->label( 'text' )
      ->where( function ($q) use ($id) {
        $q->where( 'sala.id_congress', $id )
      } )
    

    Note that I've wrapped it in a function. The Options->where() method accepts a function that provides access to the underlying query object. Some more information about that is available here.

    Allan

  • rompeplatosrompeplatos Posts: 20Questions: 4Answers: 0

    It´s work fine!
    Thank very much!

This discussion has been closed.