Editor "advanced (advanced to me)" query that is giving me trouble

chrisworrell

Alright people I have been fighting with something and I am not sure if there is an easy solution or if this is supported at all.

Let me lay the ground work.

A company can have multiple stores. Each of these stores has a training schedule that involves multiple trainings. Each store can also have multiple users that are only allowed access to their respective store. Trainings have a user that is assigned to them.

I am trying to create a table where I am able to view all upcoming trainings for all stores of a company. The trick is I am trying to figure out how to use editor that will let me edit who the training is assigned to. So that means when i click on a particular training and the bubble editor pops up, the assigned to dropdown only shows users that are assigned to that particular store.

It is easy enough to get all the users that belong to a company, but I am trying to figure out if it is possible to get access to the store_id variable inside the Field::inst below for that particular row of the query.

  $this->dbParams['company_id'] = $companyId;

  Editor::inst( $db, 'events' )
        ->where(function ($q) {
            $stores = $this->Event->Store->find('all', array(
                'conditions' => array(
                    'Store.company_id' => $this->dbParams['company_id']
                )
            ));

            foreach ( $stores as $store ) {
                $q->where('events.store_id', $store['Store']['id']);
                $q->where('events.type', 'training');
            }
        })
        ->field(
            Field::inst( 'trainings.name' ),
            Field::inst( 'events.scheduled_with_id' )
                ->options( function () use ( $db ) {
                    // This is where I would love to have access to the individual row data
                    $userList = $this->Event->User->returnUsersWithAccessToStore($$$STORE_ID$$$$);
                    $out = array();

                    foreach ( $userList as $user ) {
                        $out[] = array(
                            "value" => $user['id'],
                            "label" => $user['first_name'] . ' ' . $user['last_name']
                        );
                    }

                    return $out;
                } ),
        )
        ->leftJoin( 'trainings',     'trainings.id',          '=', 'events.training_id' )
        ->process($_POST)
        ->json();

allan

So that means when i click on a particular training and the bubble editor pops up, the assigned to dropdown only shows users that are assigned to that particular store.

Just to confirm - you are okay with everything up to this point - is that correct?

If so, what I think you need to do here is use the dependent() method or open event to make an Ajax call to the server whenever the bubble editor is opened and update the list of options available in the select field using its update method. The Ajax call would submit the ids required to get the list of users that is appropriate for the list.

You could possibly modify the JSON return to contain that information on load, but it is not something that the Editor PHP libraries themselves have an API to do that. It would require looping over the data obtained by Editor to add that extra information.

Regards,
Allan

chrisworrell

Ah I like the open event with the ajax call, good idea. Figured it was a long shot that this would have been possibly with the editor api.

Thanks again.