Left Join a Select List in Editor

davepmoll

Hello, I am trying to do a leftJoin() on my select list in Editor. I keep getting this:

Fatal error: Call to undefined method DataTables\Database::leftJoin() in . . .

Here is the php:

if ( ! isset($_POST['action']) ) {
    // Get a list of sites for the `select` list
   $data['rringredient'] = $db
   ->leftJoin( 'rringredient', 'rringredient.ingredientID', '=', 'rringredientunit.IngredientID' )
   ->selectDistinct( 'rringredient', 'ingredientID as value, IngredientUnit as label' )
   ->fetchAll();
}

Thanks!

mRender

I think you need to select before you use a join.

davepmoll

I added the select statement but I get the same error

if ( ! isset($_POST['action']) ) {
    // Get a list of sites for the <code>select</code> list
   $data['rringredient'] = $db
   ->select( 'rringredient', 'ingredientID, description' )
   ->leftJoin( 'rringredient', 'rringredient.ingredientID', '=', 'rringredientunit.IngredientID' )
   ->selectDistinct( 'rringredient', 'ingredientID as value, IngredientUnit as label' )
   ->fetchAll();
}

mRender

Oh I see what you're doing. You have to use the leftjoin in the original field instances, not in your $_POST['action']

Use my example and follow the logic here...

$data = Editor::inst( $db, 'tbl_Login', 'UID' )
    ->field(
        Field::inst( 'tbl_Login.UID' ),
        Field::inst( 'tbl_Login.email' )->validator( 'Validate::notEmpty' ),
        Field::inst( 'tbl_Login.password' )->validator( 'Validate::notEmpty' ),
        Field::inst( 'tbl_Login.LID' )->validator( 'Validate::notEmpty' ),
        Field::inst( 'tbl_Level.level' )    
    )
    ->leftJoin( 'tbl_Level',     'tbl_Level.LID',          '=', 'tbl_Login.LID' )
    ->process($_POST)
    ->data();
 
// When there is no 'action' parameter we are getting data, and in this
// case we want to send extra data back to the client, with the options
// for the 'sites' and 'dept' select lists
if ( !isset($_POST['action']) ) {
    // Get a list of sites for the `select` list
    $data['tbl_Level'] = $db
        ->selectDistinct( 'tbl_Level', 'LID as value, level as label' )
        ->fetchAll();
}

davepmoll

Sure, sure got it. But . . .

Let's assume the column 'LID' was in a different table. How would we write it?

Your code:

if ( !isset($_POST['action']) ) {
    // Get a list of sites for the <code>select</code> list
    $data['tbl_Level'] = $db
        ->selectDistinct( 'tbl_Level', 'LID as value, level as label' )
        ->fetchAll();
}

In this line here:

        ->selectDistinct( 'tbl_Level', 'LID as value, level as label' )

If the column 'LID' was in a different table, how would we write it?

allan

There are two options:

1. You could use the sql method directly and just write your SQL query rather than using the PHP API that the libraries present.
2. You would use something like:

$data['tbl_Level'] = $db->query( 'select' )
  ->distinct( true )
  ->table( 'tbl_Level' )
  ->get( 'joinTableName.LID as value' )
  ->get( 'level as label' )
  ->join( 'joinTableName', 'joinTableName.id = tbl_Level.id' )
  ->exec()
  ->fetchAll();

I've obviously made up some field names there, but that is how you can create a complex query using the API.

Allan

davepmoll

Thank you!