Relationshions error while sorting

SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘firstname’ in ‘order clause’

The SQL being executed was: SELECT * FROM dairies ORDER BY firstname, lastname LIMIT 20

==============

the error emanates from clicking the farmer header (dairies search model). I have successfully concatenated the firstname+lastname=fullname. I suspect the joint isnt working.

the model shows the animal type(freshian etc),date_of_birth,sex, name(dairies table) and the farmers name - fullname from (registration table)

the tables are join on registration->user_id and dairies=>user_id

=======================

//schema




CREATE TABLE `registration` (

  `firstname` varchar(30) DEFAULT NULL,

  `middlename` varchar(30) DEFAULT NULL,

  `lastname` varchar(30) DEFAULT NULL,

  `email` varchar(30) DEFAULT NULL,

  `id_no` int(20) DEFAULT NULL,

  `telephone` varchar(100) DEFAULT NULL,

  `gender` varchar(1) DEFAULT NULL,

  `date_of_birth` date DEFAULT NULL,

  `marital_status` varchar(1) DEFAULT NULL,

  `box_no` varchar(100) DEFAULT NULL,

  `postal_code` varchar(5) DEFAULT NULL,

  `town` varchar(100) DEFAULT NULL,

  `home_address` varchar(100) DEFAULT NULL,

  `home_number` varchar(100) DEFAULT NULL,

  `next_of_kin` varchar(100) DEFAULT NULL,

  `type` varchar(1) DEFAULT NULL COMMENT 'farmer, semen supplier (1,2)',

  `user_id` int(10) unsigned NOT NULL AUTO_INCREMENT,

  PRIMARY KEY (`user_id`)

) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;





CREATE TABLE `dairies` (

  `type` int(3) NOT NULL COMMENT 'freshian, holstein etc',

  `date_of_birth` date DEFAULT NULL,

  `sex` varchar(1) DEFAULT NULL,

  `milk_quantity` int(2) DEFAULT NULL,

  `dairy_name` varchar(20) DEFAULT NULL,

  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,

  `user_id` int(10) DEFAULT NULL COMMENT 'registration table user_id',

  PRIMARY KEY (`id`),

  UNIQUE KEY `type` (`type`)

) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;



=============================

//dairies model




<?php


namespace app\models;


use Yii;


/**

 * This is the model class for table "dairies". 

 *

 * @property string $type

 * @property string $date_of_birth

 * @property string $sex

 * @property integer $milk_quantity

 * @property string $dairy_name

 * @property string $id

 * @property integer $user_id

 */

class Dairies extends \yii\db\ActiveRecord

{


	/* your calculated attribute */

	public $fullName;


    const MALE = 1;

    const FEMALE = 2;

    

    /**

     * @inheritdoc

     */

    public static function tableName()

    {

        return 'dairies';

    }


    /**

     * @inheritdoc

     */

    public function rules()

    {

        return [

            [['user_id', 'dairy_name'], 'required'],

			[['type', 'sex'], 'safe'],

            [['milk_quantity', 'user_id'], 'integer'],

            [['dairy_name'], 'string', 'max' => 20],

			[['date_of_birth'], 'default', 'value' => '00-00-0000'],

			[['fullName'], 'safe'],

			['type', 'compare', 'compareValue' => 0, 'operator' => '!=', 'message' => 'Please choose a category.'], 

        ];

    }


    /**

     * @inheritdoc

     */

    public function attributeLabels()

    {

        return [

            'type' => 'Dairy Type',

            'date_of_birth' => 'Date Of Birth',

            'sex' => 'Sex',

            'milk_quantity' => 'Milk Quantity',

            'dairy_name' => "Dairy's Name",

            'id' => 'ID',

            'user_id' => "Farmer's Name",

			'fullName' => Yii::t('app', 'Full Name'),

        ];

    }

	

    /**

     * @inheritdoc

     */

    public function beforeSave()

    {

		$this->date_of_birth = Yii::$app->formatter->asDate($this->date_of_birth, 'php:Y-m-d');

		

		return true;

    }

	

	public function getGenderLabel()

	{

		return ($this->sex == self::MALE) ? Yii::$app->params['male'] : Yii::$app->params['female'] ;

	}

	

    public function getType()

    {

        return $this->hasMany(Type::className(), ['type' => 'id']);

    }   

	

	

	public function getDairyType()

    {

		$dairytype = Type::findOne($this->type);

		return $dairytype->type;  // displays "freshian", "gurnsey", "aryshure" ... etc

    }   

	

	public function getFullName()

    {

		$fullname = Registration::findOne($this->user_id);

		return $fullname->firstname.' '.$fullname->lastname;  // displays "freshian", "gurnsey", "aryshure" ... etc

    }   

	

	public function getRegistration()

	{

		return $this->hasOne(Registration::className(), ['user_id' => 'user_id']);

	}	

}



======================

//registration model




<?php


namespace app\models;


use Yii;


/**

 * This is the model class for table "registration".

 *

 * @property string $firstname

 * @property string $middlename

 * @property string $lastname

 * @property string $email

 * @property integer $id_no

 * @property string $telephone

 * @property string $gender

 * @property string $date_of_birth

 * @property string $marital_status

 * @property string $box_no

 * @property string $postal_code

 * @property string $town

 * @property string $home_address

 * @property string $home_number

 * @property string $next_of_kin

 * @property string $type

 * @property string $user_id

 */

class Registration extends \yii\db\ActiveRecord

{

    /**

     * @inheritdoc

     */

    public static function tableName()

    {

        return 'registration';

    }


    /**

     * @inheritdoc

     */

    public function rules()

    {

        return [

            [['firstname', 'middlename', 'lastname', 'telephone', 'town', 'id_no'], 'required'],

			//[['id_no'], 'integer', 'max' => 10],

			[['id_no', 'telephone', 'home_number'], 'integer', 'min' => 1, 'max' => 9999999999],

            [['firstname', 'middlename', 'lastname', 'email', 'date_of_birth'], 'string', 'max' => 30],

            [['box_no', 'town', 'home_address', 'next_of_kin', 'postal_code'], 'string', 'max' => 100],

			[['date_of_birth'], 'default', 'value' => null],

			['date_of_birth', 'compare', 'compareValue' => 18, 'operator' => '>='],			

			[['gender' , 'marital_status', 'type'], 'in', 'range' => [0, 1, 2]]

        ];

    }


    /**

     * @inheritdoc

     */

    public function attributeLabels()

    {

        return [

            'firstname' => 'Firstname',

            'middlename' => 'Middlename',

            'lastname' => 'Lastname',

            'email' => 'Email',

            'id_no' => 'Id No',

            'telephone' => 'Telephone',

            'gender' => 'Gender',

            'date_of_birth' => 'Date Of Birth',

            'marital_status' => 'Marital Status',

            'box_no' => 'Box No',

            'postal_code' => 'Postal Code',

            'town' => 'Town',

            'home_address' => 'Home Address',

            'home_number' => 'Home Number',

            'next_of_kin' => 'Next Of Kin',

            'type' => 'Category of Member',

            'user_id' => 'Registration No.',

        ];

    }


    public function getFullname()

    {

        return $this->hasMany(Dairy::className(), ['user_id' => 'id']);

    }   

	

	public function getDairies()

	{

		return $this->hasOne(Dairies::className(), ['user_id' => 'user_id']);

	}	


}



Post code in which that (wrong) SQL is executed.

the errors occur at the dairiessearch model listed below. It seems unable top connect to the other table(registration )

=================

//DairiesSearch




<?php


namespace app\models;


use Yii;

use yii\base\Model;

use yii\data\ActiveDataProvider;

use app\models\Dairies;


/**

 * DairiesSearch represents the model behind the search form about `app\models\Dairies`.

 */

class DairiesSearch extends Dairies

{


	public $fullName;

	

	

    /**

     * @inheritdoc

     */

    public function rules()

    {

        return [

            [['type', 'date_of_birth', 'sex', 'dairy_name'], 'safe'],

            [['milk_quantity', 'id', 'user_id'], 'integer'],

			[['fullName'], 'safe'],

        ];

    }


    /**

     * @inheritdoc

     */

    public function scenarios()

    {

        // bypass scenarios() implementation in the parent class

        return Model::scenarios();

    }


    /**

     * Creates data provider instance with search query applied

     *

     * @param array $params

     *

     * @return ActiveDataProvider

     */

    public function search($params)

    {

        $query = Dairies::find();


        $dataProvider = new ActiveDataProvider([

            'query' => $query,

			//'sort'=> ['defaultOrder' => ['type'=>SORT_ASC]]

        ]);

		

		/**

		 * Setup your sorting attributes

		 * Note: This is setup before the $this->load($params) 

		 * statement below

		 */

		$dataProvider->setSort([

			'attributes' => [

				'type',

				'id',

				'date_of_birth',

				'sex',

				'milk_quantity',

				'dairy_name',

				'fullName' => [

					'asc' => ['firstname' => SORT_ASC, 'lastname' => SORT_ASC],

					'desc' => ['firstname' => SORT_DESC, 'lastname' => SORT_DESC],

					'label' => 'Full Name',

					'default' => SORT_ASC

				],

				'user_id',

				/**/

				

			]

		]);		


        $this->load($params);


        if (!$this->validate()) {

			/**

			 * The following line will allow eager loading with country data 

			 * to enable sorting by id on initial loading of the grid.

			 */ 

			$query->joinWith(['registration']);

			// uncomment the following line if you do not want to any records when validation fails

            // $query->where('0=1');

			

			//$this->addCondition($query, 'id');

			//$this->addCondition($query, 'registration.firstname', true);

			//$this->addCondition($query, 'registration.lastname', true);

			$this->addCondition($query, 'id');

			$this->addCondition($query, 'user_id');

		 

			/* Add your filtering criteria */

		 

			// filter by person full name

			$query->andWhere('registration.firstname LIKE "%' . $this->fullName . '%" ' .

				'OR registration.lastname LIKE "%' . $this->fullName . '%"'

			);

			

			// filter by country name

			/*$query->joinWith(['registration' => function ($q) {

				$q->where('registration.firstname LIKE "%' . $this->countryName . '%"');

			}]);*/			

			

			// filter by country name

			/*$query->joinWith(['country' => function ($q) {

				$q->where('tbl_country.country_name LIKE "%' . $this->countryName . '%"');

			}]);*/

				

            return $dataProvider;

        }


        $query->andFilterWhere([

            'date_of_birth' => $this->date_of_birth,

            'milk_quantity' => $this->milk_quantity,

            'id' => $this->id,

            'user_id' => $this->user_id,

        ]);


        $query->andFilterWhere(['like', 'type', $this->type])

            ->andFilterWhere(['like', 'sex', $this->sex])

            ->andFilterWhere(['like', 'dairy_name', $this->dairy_name]);


        return $dataProvider;

    }

}



You can’t use this:




/**

* Setup your sorting attributes

* Note: This is setup before the $this->load($params)

* statement below

*/

$dataProvider->setSort([

'attributes' => [

'type',

'id',

'date_of_birth',

'sex',

'milk_quantity',

'dairy_name',

'fullName' => [

'asc' => ['firstname' => SORT_ASC, 'lastname' => SORT_ASC],

'desc' => ['firstname' => SORT_DESC, 'lastname' => SORT_DESC],

'label' => 'Full Name',

'default' => SORT_ASC

],

'user_id',

/**/


]

]); 



because firstname is not available in Dairies.

You can solve in 2 steps:

  1. Change query adding joinWith to join with needed table:



        $query = Dairies::find()->joinWith(['registration']);



  1. Add related field to sort in this form:



        $dataProvider->sort->attributes['user_id'] = [

                'asc' => ['registration.firstname' => SORT_ASC],

                'desc' => ['registration.firstname' => SORT_DESC],

                'default' => SORT_ASC,

                'label' => 'FirstName',

        ];