Possible with PHP libs?

rpmccormick

Once again I fear I am going to have to not use Editor and roll my own.... Unless... is this possible?

Here is the code I was using on the table with inline editing... all worked great:

Editor::inst( $db, 'Pricing_Rules', 'ID')
          ->fields(
                    Field::inst('RegionID')     ->validator('Validate::numeric'),
                    Field::inst('ID'),
                    Field::inst('Created'),
                    Field::inst('Modified'),
                    Field::inst('Name'),
                    Field::inst('BasePrice'),
                    Field::inst('BaseMiles'),
                    Field::inst('PricePerMile')
                  )
          ->process( $_POST )
          ->json();

Now I need to implement a filter, actually 2 optional filters, that each filter off of an ID in a separate database that link to some of the rows. The code below displays the table perfectly, but it doesn't have edit capabilities so if I stick with it then I need to have DT render input boxes and write custom onBlur json code.

//Save Filters
      $zone   = isset($_REQUEST['zone'  ]) ? $_REQUEST['zone'  ] : '0';
      $faa    = isset($_REQUEST['faa'   ]) ? $_REQUEST['faa'   ] : '0';

//Make SQL
      $sql    = 'SELECT p.*, COUNT(DISTINCT a.FAA) AS NumFAAs, COUNT(DISTINCT a.ZoneID) AS NumZones'.
                ' FROM Pricing_Rules p'.
                ' LEFT JOIN Pricing_Airports a ON (p.ID = a.Air2Zone OR p.ID = a.Zone2Air)';
      if ((strlen($zone) == 5) && (strlen($faa) == 3))
       {$sql .= ' WHERE a.ZoneID="'.$zone.'" AND a.FAA="'.$faa.'"';}
      else if (strlen($faa) == 3)
       {$sql .= ' WHERE a.FAA="'.$faa.'"';}
      else if (strlen($zone) == 5)
       {$sql .= ' WHERE a.ZoneID="'.$zone.'"';}
      $sql   .= ' GROUP BY p.ID';

//Run SQL and Return JSON
     try {$con = new PDO('mysql:host='.DB_SERVER.';dbname='.DB_NAME, DB_USER, DB_PASS);
           $result = $con->prepare($sql);
           $result->execute();
           if ($result->errorCode() == 0) {$data = $result->fetchAll(PDO::FETCH_ASSOC);}
           else                           {$data = array();}
           if (empty($data))              {$data = array();}
          }
      catch (PDOException $e)             {$data = array();}
      header('Content-type: application/json');
      echo '{"data":'.json_encode($data).'}';

...it sure would be great if I could do the same thing with Editor and keep the inline editing instead. Is this possible?

allan

Hi,

You can certainly do conditionals with the libraries so the WHERE aspects of your query shouldn't be an issue. What the libraries don't support is the GROUP BY command, since that has complications for the editing aspect.

Unfortunately it also doesn't support SQL functions (the COUNT statements).

Having said that, I suspect the above would be rewritten with a couple of one-to-many joins. That will give you the count information and the grouping wouldn't be needed (I think, from scanning over your query).

Another option is to use your own query to get the data, but still use Editor for the editing aspect, which is perfectly possible if the editing is just simple updates on the main table.

Allan

rpmccormick

Thanks Allan. I will have to check out one-to-many...

I really like the idea of yours to use seperate ajax: for the DT and the editor. Unfortunately I tried it and it didn't work...

The inline edit just stays open after blur (funny, as that is what I was trying to be able to do in the other thread) and it does that because Editors setRules.php is returning this: {"error":"SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '00005' for key 'PRIMARY'"}

I need it to update not insert. Everything works fine if I set DT to setRules.php (my 13 line code above) instead of getRules.php (my 30-line code above)

allan

Did you include the DT_RowId parameter in the returned data (or use idSrc to tell Editor to get it from somewhere else)?

Allan

rpmccormick

Nevermind... I added this to my SQL and all is good.

CONCAT("row_", p.ID) AS DT_RowId

Awesome solution! Thanks for the idea.

rpmccormick

I spoke too soon. After getting it all working, I added the new NumZones and NumAirports columns to the DT, but now whenever I inline-edit one of the 3 price fields, I get an alert saying it could not find NumZones/NumAirports (I got the same message about created/modified when I tried to remove those from the setRules.php as well).

Is it possible to tell editor just to ignore those fields that only come from the DT's sql and will never be edited? It seems to apply the edit fine, the only issues are 1) the alert, and 2) the NumAir/Zones goes blank after editing any of the 3 editable price columns.

allan

Ah yes, that aspect had slipped my mind, sorry. So what you'll need to do is, on edit, run your query again, although with a WHERE condition that will return only the data required for the edit being edited. That needs to be returned to Editor.

The reason for this is that Editor expects the information for the updated row to be returned from the server - in case there are any parameters on the server-side that have been updated.

What I might do for a future version of Editor is provide the ability to give your own GET query as a string, which can then be as complex as needed, and it will handle that for you, as long as it interfaces with the standard configuration for editing.

Allan

rpmccormick

I think I understand what you are saying, but I'm not sure how to implement it. Do I just edit setRules.php, remove the ->json from the end, and then return my own data for the edited row?

This seems unsafe as then Editor cannot communicate back at all, so if there was an error on the Edit it would not return the error.

Really, I don't understand why Editor needs to get the updated row from the database at all. It is the thing that updated the row... the unedited values didn't change, and the edited one was typed in, so if the edit is successful Editor shouldn't need to query anything or receive any data back other than 'success'. Or it could get all of the updateable the columns that belong to the Editor instance, while leaving columns that only belong to the DT alone. If needed you could even have an "editable": false as part of the DT definition (just like orderable and searchable).

Anyway, please let me know how to get something working... is just deleting the last line "->json" and replacing it with my own "return ..." the proper way you were suggesting?

rpmccormick

FYI: My normal solution for this common (for me) issue is to create a VIEW of my query. Editor seems to be able to update the root table from a VIEW (though it can't INSERT/DELETE), and all the values are there. The reason I can't do that this one time is because the conditional WHERE's need to be applied before the GROUP BY, so I cannot create the VIEW and then apply the conditional WHERE's.

...I think that fact prevents me from using your One-To-Many solution as well (though I am not positive)

rpmccormick

I got it working...

Had to change data: to row:, remove the [ ] from the json, and detect delete and return only "[]" so that it would work.

I still think it is kinda sketchy with not getting any reply from Editor, but it will do.

Code:

Editor::inst( $db, 'Pricing_Rules', 'ID')
          ->fields(
                    Field::inst('ID'),
                    Field::inst('Created'),
                    Field::inst('Modified'),
                    Field::inst('Name')         ->validator('Validate::required'),
                    Field::inst('RegionID')     ->validator('Validate::numeric'),
                    Field::inst('BasePrice')    ->validator('Validate::numeric'),
                    Field::inst('BaseMiles')    ->validator('Validate::minMaxNum', array('min' => 0, 'max' => 250, 'message' => 'Please enter a number of Base Miles (0-250)')),
                    Field::inst('PricePerMile') ->validator('Validate::numeric')
                  )
          ->where( 'RegionID' , $region, '=')   //ignored if '' - not no result as expected
          ->process( $_POST );
//          ->json();


      if ($_POST['action'] == 'remove') {echo '[]';}
      else
       {$sql    = 'SELECT CONCAT("row_", p.ID) AS DT_RowId, p.*,'.
                       ' COUNT(DISTINCT a.FAA) AS NumFAAs,'.
                    ' COUNT(DISTINCT a.ZoneID) AS NumZones'.
                  ' FROM Pricing_Rules p'.
                  ' LEFT JOIN Pricing_Airports a ON (p.ID = a.Air2Zone OR p.ID = a.Zone2Air)'.
                  ' WHERE CONCAT("row_", p.ID)="'.$_POST['id'].'"'.
                  ' GROUP BY p.ID';

      try {$con = new PDO('mysql:host='.DB_SERVER.';dbname='.DB_NAME, DB_USER, DB_PASS);
           $result = $con->prepare($sql);
           $result->execute();
           if ($result->errorCode() == 0) {$data = $result->fetchAll(PDO::FETCH_ASSOC);}
           else                           {$data = array();}
           if (empty($data))              {$data = array();}
          }
      catch (PDOException $e)             {$data = array();}
      header('Content-type: application/json');
      echo '{"row":'.str_replace(array('[', ']'), '', json_encode($data)).'}';
       }

allan

Good to hear you got it working!

Really, I don't understand why Editor needs to get the updated row from the database at all.

It is because many tables will include information that is generated by the database (and updated time stamp for example), or the server-side script. If you don't have that, then yes, this requirement can be a bit of a pain rather than using the data that is available at the client-side.

Allan