MODX Community

REST API Controller to pull data from related table

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:

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;
}

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;
    }

}

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();
}

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

4 Likes

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