Yii2 use methods in controller from the model (REST API)

Question

I've made a simple REST API with the Yii2 framework. I've made a controller with some queries to get data from the database. But now I want to seperate those queries from the controller. I've thought that I shuld put the function to get the query in the Model (ActiveRecord), but I can't access the method from my controller.

namespace app\models;

use yii\db\ActiveRecord;

class Car extends ActiveRecord {
    public function __construct(){
        parent::__construct();
    }

    public static function tableName(){
        return 'auto_new';
    }

    public static function primaryKey(){
        return ['auto_id'];
    }

    // Regels voor POST callback
    public function rules(){
        //voorbeeld
        /*
        return [
            [['username', 'password'], 'required']
        ];
        */
    }

    public static function getCar(){
        $query = (new Query())
            ->select([
                'a.auto_id                          auto_id',
                'm.merk                             merk',
                'a.model                            model',
                'a.uitvoering                       uitvoering',
                'k.kleur                            kleur',
                't.transmissie                      transmissie',
                'a.kmstand                          kmstand',
                'a.bouwjaar                         bouwjaar',
                'a.vermogen                         vermogen',
                'b.brandstof                        brandstof',
                'g.garantie                         garantie',
                'a.prijs                            prijs',
                'kl.postcode                        postcode',
                'kl.woonplaats                      woonplaats',
                'GROUP_CONCAT(DISTINCT(atn.NL))     opties',
                'GROUP_CONCAT(DISTINCT(f.foto_id))  fotos'
            ])

            ->from('auto_new a')

            ->join('INNER JOIN', 'tbl_merken m', 'a.merk = m.merk_id')                          //Merk
            ->join('INNER JOIN', 'tbl_kleur k', 'a.kleur = k.kleur_id')                         //Kleur
            ->join('INNER JOIN', 'tbl_transmissie t', 'a.transmissie = t.transmissie_id')       //Transmissie
            ->join('INNER JOIN', 'tbl_brandstof b', 'a.brandstof = b.brandstof_id')             //Brandstof
            ->join('INNER JOIN', 'tbl_garantie g', 'a.garantie = g.garantie_id')                //Garantie
            ->join('INNER JOIN', 'klanten kl', 'a.ac = kl.ac')                                  //Klantgegevens

            //Alle opties van de auto
            ->leftJoin('auto_accessoire acc', 'a.auto_id = acc.auto_id AND a.ac = acc.ac')
            ->leftJoin('tbl_accessoires_trader_new atn', 'acc.code_id = atn.code_id')
            //Alle foto's van de auto
            ->leftJoin('auto_foto f', 'a.auto_id = f.auto_id AND a.ac = f.ac')

            ->groupBy('a.auto_id')
            ->all();
        return $query;
    }
}

This is the method in my controller:

public function actionGetcarsbyac($ac) {
    Yii::$app->response->format = Response::FORMAT_JSON;
    $model = $this->modelClass;
    $query = Car::getCar();
    $query->where(['a.ac' => $ac]);
    return $query;
}

This gives me the following error when I try to open the url:

Call to a member function getUniqueId() on a non-object

I don't know what to do or where to place these query methods.

Answer 1

I think this is because when you put ->all() or ->one() or any other query method it gives you populated result object which you cannot query further...

So when you want to work further on to that query you should return the query without query method, and then complete that query in to your controller action. So by doing like this your code would be like:

public static function getCar(){
        $query = new Query();
            $query->select([
                'a.auto_id                          auto_id',
                'm.merk                             merk',
                'a.model                            model',
                'a.uitvoering                       uitvoering',
                'k.kleur                            kleur',
                't.transmissie                      transmissie',
                'a.kmstand                          kmstand',
                'a.bouwjaar                         bouwjaar',
                'a.vermogen                         vermogen',
                'b.brandstof                        brandstof',
                'g.garantie                         garantie',
                'a.prijs                            prijs',
                'kl.postcode                        postcode',
                'kl.woonplaats                      woonplaats',
                'GROUP_CONCAT(DISTINCT(atn.NL))     opties',
                'GROUP_CONCAT(DISTINCT(f.foto_id))  fotos'
            ])

            ->from('auto_new a')

            ->join('INNER JOIN', 'tbl_merken m', 'a.merk = m.merk_id')                          //Merk
            ->join('INNER JOIN', 'tbl_kleur k', 'a.kleur = k.kleur_id')                         //Kleur
            ->join('INNER JOIN', 'tbl_transmissie t', 'a.transmissie = t.transmissie_id')       //Transmissie
            ->join('INNER JOIN', 'tbl_brandstof b', 'a.brandstof = b.brandstof_id')             //Brandstof
            ->join('INNER JOIN', 'tbl_garantie g', 'a.garantie = g.garantie_id')                //Garantie
            ->join('INNER JOIN', 'klanten kl', 'a.ac = kl.ac')                                  //Klantgegevens

            //Alle opties van de auto
            ->leftJoin('auto_accessoire acc', 'a.auto_id = acc.auto_id AND a.ac = acc.ac')
            ->leftJoin('tbl_accessoires_trader_new atn', 'acc.code_id = atn.code_id')
            //Alle foto's van de auto
            ->leftJoin('auto_foto f', 'a.auto_id = f.auto_id AND a.ac = f.ac')

            ->groupBy('a.auto_id')
            return $query;
    }

Notice that I have removed ->all() from the getCar(). And now your action would be like:

public function actionGetcarsbyac($ac) {
    Yii::$app->response->format = Response::FORMAT_JSON;
    $model = $this->modelClass;
    $query = Car::getCar();
    $query->where(['a.ac' => $ac])
    ->all(); // here you are completing your query
    return $query;
}

Hope this should work...