PHP Editor | How to Mjoin Option Validator

mathias148

Hello,
I have the database structure like this

tasks
task_id
task_name
project_id
user_id

project_users
project_id
user_id

users
user_id
name

What i want is be able to change tasks.users_id, based on the users that are in the project (from project_users table)
sorry, i did not see an clear example of this and did not find a forum topic about it.

based on https://editor.datatables.net/manual/php/validation, i played the users.site logic but this is not what i wanted, i got all the users from the user table and not only the user from the projects and there are restictions, i can not assign a tasks to a user that is not part of the project.

if you could give me a clue on PHP and JS regarder tasks.user_id.

that would be very nice.

thats a lot

rf1234

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

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

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

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