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']);
}
}