Yii2 REST ActiveRecord deep relation not working

Question

We've been coding an API service with Yii2 and created all model classes extending from ActiveRecord.

Next we started adding the simple relations. This is where things got strange.

We have 3 tables (limited to explain the problem) 'app_customers', 'lu_postcodes' and 'lu_countries'.

A customer has one postcode_id. A postcode has one country_id.

In the customer model we would like to add relationships to get the postcode and country data when getting customer data.

Customer model:

namespace api\modules\v1\models;

use Yii;
use \api\common\components\BaseModel;

/**
 * 
 */
class Customer extends BaseModel {

    public function extraFields() {
        return ['postcode'];
    }

    public function getPostcode() {
        return $this->hasOne(Postcode::className(), ['id' => 'postcode_id'])
            ->with(['country']);
    }
    ....[more code]

Postcode model:

namespace api\modules\v1\models;

use Yii;
use \api\common\components\BaseModel;

/**
 * 
 */
class Postcode extends BaseModel {

    public function extraFields() {
        return ['country'];
    }

    public function getCountry() {
        return $this->hasOne(Country::className(), ['id' => 'country_id']);
    }
    ....[more code]     

So when calling v1/customer?expand=postcode it returns all customer data and postcode is populated with a postcode object. But we can't get the country data to load together with the postcode data.

In an ideal situation we would like the index and view actions from customer to include both postcode and country data. (did not try yet .. one step at a time :) )

Trying to debug this issue We dumped the sql from Customer::getPostcode with var_dump($q->createCommand()->sql); and got the output:

'SELECT * FROM `lu_postcodes` WHERE `id`=:qp0'

This might have something to do with missing tablenames?

In earlier attempt I managed to get the country data loaded, but it used the ID from customer which resulted in a wrong country obviously.

Any ideas? Thanks in advance!

I did some research and found https://github.com/yiisoft/yii2/issues/6844#issuecomment-131482508, which looked promising, but once implemented it still remained the same result.

----------------------- EDIT BELOW

I tried all options and in most cases I get a JSON parse error from Yii. In one case I get a result like

...
"display_name": "Rxxxxxxx xxxxxxx",
"postcode": {
  "id": 361,
  "country_id": 20,
  "state_id": 2,
  "zip": "3945",
  "city": "Ham",
  "alpha": "ham",
  "longitude": "5.1730329000",
  "latitude": "51.0966039000"
},
"country": null
...

Used option: Adding country in extraFields() in Customer.

public function extraFields() {
    return ['postcode', 'country' => function($model) { return $model->postcode->country; }];
}

Answer 1

I got it working. It was an encoding issue...

'charset' => 'UTF-8', in the db config array did the trick.

Answer 2

Yii2 itself does not directly support nested relations . It is not good idea to build such complex objects in a REST API using ActiveRecord classes. Remember you will likely have a collection API i.e. a group of customer , each customer will need multiple subqueries to satisfy the request.

There are multiple ways to address this.

1. Use separate controller to solve this /customer/<id>/country. This is good solution if country/ postcode is a hasMany relationship

2. Define country in the default fields() functions of postcode instead of extraFields(). This way when you pass expand?=postcode, both postcode and country will always show in the expanded output

3. Use ad-hoc expansion function in your extraFields() definition of Customer Class with something like this

.

public function extraFields() 
{
    return [
      'postcode',
      'country'=>function($model){
         return $model->postcode->country;
       }
    ];
}

4. Define a country function in your Customer class using Via Relation, this is more useful when you have hasMany relationships

.

public function getCountry()
{
   return $this->hasOne(Country::className(), ['id' => 'country_id'])
            ->via('postCode');
}

5. Define a custom query with the exact parameters you desire and use a dataProvider as the response to your Index function something like this

..

public function actionIndex()
 {
   $query = (new \yii\db\Query()) 
  // This can also be a ActiveQuery 
  // using Customer::find() with addSelects() etc
  // ... additional query conditions
  return new ActiveDataProvider(['query'=>$query]);
}

Similarly

public function actionView($id)
{
  $query = (new \yii\db\Query())  // simlar to above
  $query->andWhere(['customer.id'=>(int)$id]); // or similar condition
  return $query->one();
}