REST API Controller to pull data from related table

Development
1

Hi All

Been a modx user for quite a few years and recently got into the REST api side of things combined with MIGXDB and everything is working fine as separate endpoints.

So i can pull in all of the data from my tables and show them as well as pass through as ID as well as pass through parameters to get just the data i want for that single table.

my issue now is that i want to try to combine the data of two tables in the response for one of my controllers.

Below is a snippet of my schema which works perfectly fine as separate endpoints. i can get all answers for a particular question ID by querying my answers endpoint and passing it a question ID.

<object class="GCPQuestions" table="questions" extends="xPDOSimpleObject">
<field key="label" dbtype="varchar" phptype="string" precision="100" null="false" default="" />
<field key="qorder" dbtype="int" precision="10" phptype="integer" null="false" default="0" />
<field key="subq" dbtype="int" precision="10" phptype="integer" null="false" default="0" />
<field key="category_id" dbtype="int" precision="10" phptype="integer" null="false" default="0" />
<field key="sub_category_id" dbtype="int" precision="10" phptype="integer" null="false" default="0" />
<field key="qtype" dbtype="varchar" phptype="string" precision="100" null="false" default="" />
<field key="question" dbtype="text" phptype="string" null="false" default="0" />
<field key="subtext" dbtype="text" phptype="string" null="false" default="0" />
<field key="placeholder" dbtype="text" phptype="string" null="false" default="0" />
<field key="status" dbtype="tinyint" precision="1" attributes="unsigned" phptype="integer" null="false" default="0" />
<field key="deleted" dbtype="tinyint" precision="1" attributes="unsigned" phptype="integer" null="false" default="0" />
<field key="createdon" dbtype="datetime" phptype="datetime" null="true" />
<composite alias="instructions" class="GCPLibrary" local="id" foreign="question_id" cardinality="many" owner="local" />
<composite alias="answers" class="GCPPossibleAnswers" local="id" foreign="question_id" cardinality="many" owner="local" />
</object>

<object class="GCPPossibleAnswers" table="possibleanswers" extends="xPDOSimpleObject">
<field key="question_id" dbtype="int" phptype="integer" precision="10" null="false" default="" />
<field key="then_question_id" dbtype="int" precision="10" phptype="integer" null="false" default="0" />
<field key="qtype" dbtype="varchar" phptype="string" precision="100" null="false" default="" />
<field key="type" dbtype="int" precision="2" phptype="integer" null="false" default="0" />
<field key="label" dbtype="varchar" precision="256" phptype="string" null="false" default="0" />
<field key="answer" dbtype="varchar" precision="256" phptype="string" null="false" default="0" />
<field key="result" dbtype="text" phptype="string" null="false" default="0" />
<field key="deleted" dbtype="tinyint" precision="1" attributes="unsigned" phptype="integer" null="false" default="0" />
<aggregate alias="question" class="GCPQuestions" local="question_id" foreign="id" cardinality="one" owner="foreign" />

</object>

Also i can get all questions or a particular question by passing through its ID or category etc.

See below an example of my questions controller

class MyControllerquestions extends modRestController {
    public $classKey = 'GCPQuestions';
    public $defaultSortField = 'id';
    public $defaultSortDirection = 'ASC';
    public $searchFields = array('question');
    
    
       protected function prepareListQueryBeforeCount(xPDOQuery $c) {
           
        $c->where(['deleted' => 0]);
            
        if ($subq = (int)$this->getProperty('subq')) {
            $c->where(['subq' => $subq]);
        }
            
            
        if ($category = (int)$this->getProperty('cat')) {
            $c->where(['category_id' => $category]);
        }
            
        if ($category = (int)$this->getProperty('cat')) {
            $c->where(['category_id' => $category]);
        }
        
        if ($subcat = (int)$this->getProperty('subcat')) {
            $c->where(['sub_category_id' => $subcat]);
        }
        
        if ($label = $this->getProperty('label')) {
            $c->where(array('label:LIKE' => '%'.$label.'%'));
        }
        
        
        
        return $c;
    }

}

so how can i now return a json response that will look like the following essentially, which will get me the question i request via ID but then also in the same response get me all of the possible answers for that question.

See a pure example below of what i am hoping to achieve.

{
  "questions": {
    "6": {
      "id": 6,
      "label": "1a",
      "qorder": 1,
      "subq": 0,
      "category_id": 2,
      "sub_category_id": 0,
      "qtype": "multinumber",
      "question": "How healthy do you think your lifestyle is? (1-8)",
      "subtext": "",
      "placeholder": "",
      "answers": {
        "1": {
          "id": 1,
          "question_id": 6,
          "then_question_id": 7,
          "qtype": "single",
          "type": 1,
          "label": "1",
          "answer": "1",
          "result": "1",
          "deleted": 0
        },
        "2": {
          "id": 2,
          "question_id": 6,
          "then_question_id": 7,
          "qtype": "single",
          "type": 1,
          "label": "2",
          "answer": "2",
          "result": "2",
          "deleted": 0
        },
        "3": {
          "id": 3,
          "question_id": 6,
          "then_question_id": 7,
          "qtype": "single",
          "type": 1,
          "label": "3",
          "answer": "3",
          "result": "3",
          "deleted": 0
        },

I understand i can hit both end points seperately, so get all the questions from the first endpoint, then hit the second endpoint and pass through a question ID to get all of the answers for that question. We are just finding that we might have the need for a single controller that can return all of that information on a single call instead of 2.

i also know i can do this manually in a snippet reasonably easily using the getObject() on the questions table and then a getMany() for the answers. Ideally i want to stick with using the API so if there is some xpoquery or join example that someone can assist with that i can get use in my controller that would be awesome.

    // Grab a question by ID 
    $question = $modx->getObject('GCPQuestions', 10 );
    
     // Grab all answers associated with that question ID 
    
    $answers = $question->getMany('answers');
    
    foreach ($answers as $answer) {
         loop through.... etc
    );

Many thanks in advance :slight_smile:

2

You probably can override the function prepareListObject in your modRestController and add the code to load the answers like you would in a snippet:

protected function prepareListObject(xPDOObject $object){
	$objectArray = $object->toArray();
	
	$answers = $object->getMany('answers');
	$answer_array = array();
	foreach ($answers as $answer) {
		//loop through.... etc
		$answer_array[$answer->get('id')] = ...;
    }
	$objectArray['answers'] = $answer_array;
	
	return $objectArray;
}
3

I think that Just about did it! I knew i had the pieces to my puzzle, just putting it together.

Also i don’t think i would have found the use of the prepareListObject, i know its in the base REST class but takes a bit of time determining what order the various functions get triggered in.

it would be good if the documentation can improve on the REST features in modx as it seems to be quite untapped in its potential.

Here is my final controller as it is right now with pulling the data from its related table if anyone else wanted some insight in the future.

class MyControllerquesanswers extends modRestController {
    public $classKey = 'GCPQuestions';
    public $defaultSortField = 'id';
    public $defaultSortDirection = 'ASC';
    public $searchFields = array('question');
     public $defaultLimit = 0;
     
    protected function prepareListObject(xPDOObject $object) {
             
            $objectArray = $object->toArray();
	
            $answers = $object->getMany('answers');
            $answer_array = array();
            
                foreach ($answers as $answer)
                 {
                    
                    $new_item = array(
                        'id' => $answer->get('id'), 
                        'question_id' => $answer->get('question_id'),
                        'then_question_id' => $answer->get('then_question_id'),
                        'qtype' => $answer->get('qtype'),
                        'type' => $answer->get('type'),
                        'label' => $answer->get('label'),
                        'answer' => $answer->get('answer'),
                        'result' => $answer->get('result'),
                        'deleted' => $answer->get('deleted')
                    );
                    
                    // finally push all arrays into a single array
                    array_push($answer_array, $new_item);
                }
            
            $objectArray['answers'] = $answer_array;
	

        return $objectArray;
    }
    
    
       protected function prepareListQueryBeforeCount(xPDOQuery $c) {
           
            $c->where(['deleted' => 0]);
            
        if ($subq = (int)$this->getProperty('subq')) {
            $c->where(['subq' => $subq]);
        }
            
            
        if ($category = (int)$this->getProperty('cat')) {
            $c->where(['category_id' => $category]);
        }
            
        if ($category = (int)$this->getProperty('cat')) {
            $c->where(['category_id' => $category]);
        }
        
        if ($subcat = (int)$this->getProperty('subcat')) {
            $c->where(['sub_category_id' => $subcat]);
        }
        
        if ($label = $this->getProperty('label')) {
            $c->where(array('label:LIKE' => '%'.$label.'%'));
        }
        
       
        
        
        return $c;
    }

}
4

When you want all the fields from your object as an array, you can also use the function toArray() to simplify your code:

foreach($answers as $answer){
    $answer_array[] = $answer->toArray();
}

GET requests with a primary key passed

Also, if you want to be able to make requests for a single question with something like GET .../rest/question/1 or GET .../rest/question?id=1, then you have to override the function afterRead too to load the answers.

public function afterRead(array &$objectArray) {
	//please take note that here the parameter is an array instead of an object!
	return !$this->hasErrors();
}
5

there might soon be an integrated way, to configure automatic REST API endpoints within MIGX

5 Likes
6

I believe @mayanktaker will be excited to hear that, @bruno17 :slight_smile:
He was asking about that very thing in another thread.

1 Like
7

OMG yes. This is probable best news of 2020 for me. :sweat_smile: :stuck_out_tongue_closed_eyes:

8

Many thanks for the reply on this and apologies for now getting back to you (tight deadlines) but i have another query about what you mentioned for the afterRead.

public function afterRead(array &$objectArray) {
//please take note that here the parameter is an array instead of an object!
return !$this->hasErrors();

}

The above is obviously passing as array, so im trying to figure out how i can replicate the same behaviour as my prepareListObject function. As its not an xPDOobject i can’t simply run

$answers = $object->getMany('answers');

So the main question is how can i get the related answers when the object is already an array beforehand.

hope that makes sense.

Also, not related to the above question but is it possible to get the contents of a different table entirely within one of my rest APIs if i know the ID of the object such as

$modx->getObject('modResource', 555);

when i run that in the prepareListObject it does not return anything or simply errors, So in this case my classlist is public $classKey = ‘GCPQuestions’; but i just want to get an object of something directly using the standard getObject method but sort of merge it into the result of my rest API.But i want to not change my classlist of the endpoint as its primary use is to get data from its intended classlist defined table.

9

Instead of getMany() you probably can use getCollection() like this:

$answers = $this->modx->getCollection('GCPPossibleAnswers', array('question_id' => $objectArray['id']));

This should also answer your second question. You have to use $this->modx instead of $modx.

Alternatively you can override the function read() from the class modRestController (that calls afterRead). There you have access to the xPDOobject. Just copy the whole code of the function from the source and then adjust it.

10

Awesome, i actually realised my error and fixed it before i came back here to here your reply, my issue was that i was missing the $this ->modx->getMany() and i was simply calling $modx->getMany();

But yep i could also use getCollection but i may just mod read in this case

I’ll give that a go but i think it should be straight forward if its passing a similar object.

11

Looking at the code again, it is probably also possible (and much easier) to use $this->object instead of $object in the function afterRead().

$answers = $this->object->getMany('answers');
1 Like