Dude inner join

rompeplatos

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

allan

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

Allan

rompeplatos

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"
                    }    
        });

allan

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

rompeplatos

It´s work fine!
Thank very much!