Answers

• rf1234 Posts: 3,026Questions: 88Answers: 422

  June 2020

  Validation is one option but not the most user friendly one in this case.

  Let's assume you have an Editor instance to work on "tasks":
  - You would define a task name
  - Subsequently select a project from a drop down
  - Depending on the project selected you would need to filter the users client side so that only the users assigned to the respective project are selectable.
  - In case no project has been selected no user should be selectable.
  - In case the project selection changes the user selection should be emptied and the options for user selection should be adjusted that they comply with the newly selected project.

  In order to achieve this you would need to
  - send all (relevant) projects to the the client as an array of objects { label: not in your quoted tables, value: project_id } which is the list of the selectable options
  - you can't retrieve the relevant users up front because you don't know what project will be selected and hence what are the relevant users

  To do this you would need one options instance server side that probably won't even need a WHERE clause. Rather simple and you can find one in the examples like in here: https://editor.datatables.net/examples/advanced/joinArray.html
  

  It is a little more complicated client side because you would need to retrieve and change the options for the users dynamically depending on project selection:

  You can use this to define the dependency:
  https://editor.datatables.net/reference/api/dependent()
  and this to manipulate the options accordingly:
  https://editor.datatables.net/reference/api/field().update()

  Something like this could work:

  yourEditor
      .dependent('tasks.project_id', function (val, data, callback) {
          var self = yourEditor;
          if ( val <= 0 ) { //no project id selected => no user selectable
              self.field('tasks.user_id').update([], false);
          } else {
              $.ajax({
                  type: "POST",
                  url: 'get the relevant users for the dropdown',
                  data: { projectId: val },
                  dataType: "json",     
                  success: function (data) {
                      self.field('tasks.user_id').update(data, false);
                      //you would also need to search the returned options and
                      //find out whether the current value of user_id is in that
                      //list. If not: empty tasks.user_id
                      if ( self.val('tasks.user_id') > 0 ) {
                          var i = 0; 
                          var relevantUserIds = [];
                          while ( data[i] ) {
                              relevantUserIds.push(data[i].value);
                              i++;
                          }
                          if ( $.inArray(self.val('tasks.user_id'), relevantUserIds) < 0 ) {
                              self.set({'tasks.user_id': ''});
                          }
                      }
                  }
              });
          }
      })
  

  Server side you would need a query that returns the json encoded options for the users based on the posted "projectId"

  Using Editors raw() method it could look like this ($db being Editor's db handler):

  $stmt = ('SELECT DISTINCT b.name AS label, b.user_id AS value 
              FROM project_users a
        INNER JOIN users b ON a.user_id = b.user_id
             WHERE a.project_id = :projectId');  
  $result = $db ->raw()
                ->bind(':projectId', $_POST['projectId'])
                ->exec($stmt);
  echo json_encode( $result->fetchAll(PDO::FETCH_ASSOC) );
  
• mathias148 Posts: 3Questions: 1Answers: 0

  June 2020 edited June 2020

  thanks a lot for your answer, that looks like the full option.

  I have forgotten to give you some tiny detail of the main editor query.

  • i'm only focused on editing data not creating data.
  • [EDIT] task.task_id, task.task_name, task.project_id will be read_only values
  • my view is based on one project only
    ->where(project_id,452)

  your answer seems like the full solution when it comes to create and have many projects, but here, I think i could do a little simpler.

  so i was looking on the field php option closure to create my own query like

  SELECT u.id, u.email 
  FROM project_users AS pu
  INNER JOIN users AS u ON pu.user_id = u.id
  WHERE pu.project_id = 452;
  

  and put it in the option of my tasks.user_id field but i did not find any closure example.

  the Field.php class says in the options function :

  /*
       * Note that this is for simple 'label / value' pairs only. For more complex
       * data, including pairs that require joins and where conditions, use a
       * closure to provide a query
  */
  

  do you have some example on php closure for the option field base on my sql ?
• rf1234 Posts: 3,026Questions: 88Answers: 422

  June 2020 edited June 2020 Answer ✓

  Here you are: https://editor.datatables.net/manual/php/joins#Options

  Either way you must return an array of "label / value" pairs irrespective of whether or not it is "complicated" to retrieve them.

  Something like this should work. If you prefer to have the user's email instead of the user's name as the label just select that instead of "name".

  Field::inst( 'tasks.user_id' )
      ->options( function () use ( $db ) { //use Editor's db handler
         $stmt = ('SELECT DISTINCT b.name AS label, b.user_id AS value
                              FROM project_users a
                        INNER JOIN users b ON a.user_id = b.user_id
                             WHERE a.project_id = :projectId'); 
          $result = $db ->raw()
             ->bind(':projectId', 452)
             ->exec($stmt);
          //return an array of 'label / value' pairs
          return $result->fetchAll(PDO::FETCH_ASSOC) );
      } );
  

  You can achieve the same - even implement an INNER JOIN - using Editor's options instance as well. Here is a more complex example:
  https://datatables.net/forums/discussion/comment/116150/#Comment_116150
• mathias148 Posts: 3Questions: 1Answers: 0

  June 2020

  thanks a lot that's exactly what i was looking for