Query in options not taking second where clause

jimik1979

Hi,
I'm working on an php Editor instance that uses a drop down select for one of the columns. It takes its options from a second query that I've set up in the AJAX file (below). It works brilliantly when I have the one where clause on line 16, but it doesn't return anything if I change it to ['fleet_level.fleetID' => $fleet_id, 'fleet_level.isDeleted' => 0] as I need both clauses to apply. The data in the second part is either a 1 or a 0, and manually running the query on the database returns several rows. If I take the fleetID clause out of the php and leave the isDeleted one it also returns nothing, which again is not the case if I run the same query on the database.

What am I doing wrong? The query I need is SELECT fleetLevelID, name, parentID FROM fleet_level WHERE fleetID = $fleet_id AND isDeleted = 0

 Editor::inst( $db, 'vehicle', 'vehicle.vehicleID' )
        ->field(
          Field::inst( 'vehicle.vehicleID' ),
          Field::inst( 'vehicle_manufacturer.brandName' ),
          Field::inst( 'vehicle_model.vehicleModelName' ),
          Field::inst( 'vehicle.VRN' ),
          Field::inst( 'vehicle.vehicleNickname' ),
          Field::inst( 'fleet_level.fleetLevelID' )
            ->options( 
              // need to get the names and ids of all the fleet levels in this fleet 
              // so that Datatables can make the correct drop down in the edit window
              function () use ($db, $fleet_id) {
                $attrList = $db->select(
                  'fleet_level',
                  ['fleet_level.fleetLevelID', 'fleet_level.name', 'fleet_level.parentID'],
                  ['fleet_level.fleetID' => $fleet_id]
                );
                $prepare = array();
                while ($row = $attrList->fetch()) {
                  $prepare[$row['fleet_level.fleetLevelID']] = array(
                    "fleetLevelID" => $row['fleet_level.fleetLevelID'],
                    "name" => $row['fleet_level.name'],
                    "parentID" => $row['fleet_level.parentID']
                  );
                }
                // change the names to add the level's ancestors name(s) in front
                foreach ($prepare as $row) {
                  if (isset($row['parentID']) && $row['parentID']!=0) {
                    $prepare[$row['fleetLevelID']]['name'] = $prepare[$row['parentID']]['name'].'-'.$row['name'];
                  }
                }
                
                $out = array();
                foreach ($prepare as $row) {
                  $out[] = array(
                    "value" => $row['fleetLevelID'],
                    "label" => $row['name']
                  );
                }
                return $out;
              }
            ),
          Field::inst( 'fleet_level.name')
        )
        
        ->leftJoin ( 'vehicle_fleet_level', 'vehicle.vehicleID', '=', 'vehicle_fleet_level.vehicleID' )
        ->leftJoin ( 'fleet_level', 'vehicle_fleet_level.fleetLevelID', '=', 'fleet_level.fleetLevelID')
        ->leftJoin ( 'vehicle_manufacturer', 'vehicle.vehicleManufacturerID', '=', 'vehicle_manufacturer.vehicleManufacturerID')
        ->leftJoin ( 'vehicle_model', 'vehicle.vehicleModelID', '=', 'vehicle_model.vehicleModelID')
        ->where ( 'fleet_level.fleetID', $fleet_id)
        ->process( $_POST )
        ->json();

jimik1979

Bump

allan

All you should need to do is use:

->where ( 'fleet_level.fleetID', $fleet_id)
->where ( 'fleet_level.isDeleted', 0)

Editor's where() method combines conditions with AND conditions. You need to sure a closure for anything else.

Allan

jimik1979

Thanks Allan,
That looks like it has worked.

minion_warrior

```
Field::inst( 'fleet_level.fleetLevelID' )
->options(
// need to get the names and ids of all the fleet levels in this fleet
// so that Datatables can make the correct drop down in the edit window
function () use ($db, $fleet_id) {
$attrList = $db->select(
'fleet_level',
['fleet_level.fleetLevelID', 'fleet_level.name', 'fleet_level.parentID'],
['fleet_level.fleetID' => $fleet_id]
);
$prepare = array();
while ($row = $attrList->fetch()) {
$prepare[$row['fleet_level.fleetLevelID']] = array(
"fleetLevelID" => $row['fleet_level.fleetLevelID'],
"name" => $row['fleet_level.name'],
"parentID" => $row['fleet_level.parentID']
);
}
// change the names to add the level's ancestors name(s) in front
foreach ($prepare as $row) {
if (isset($row['parentID']) && $row['parentID']!=0) {
$prepare[$row['fleetLevelID']]['name'] = $prepare[$row['parentID']]['name'].'-'.$row['name'];
}
}

           $out = array();
           foreach ($prepare as $row) {
             $out[] = array(
               "value" => $row['fleetLevelID'],
               "label" => $row['name']
             );
           }
           return $out;
         }
       ),
     Field::inst( 'fleet_level.name')
   )

```
is this code workin?