error when joining two table

Yii 2.0 General Discussions
1

[size=“4”]I want to get description, (first_name,last_name,’-’,title) as title from team table and name as image from image_gallery table by using id from team table

I am Using Windows 10 OS, XAMPP Server with PHP 5.6 aand Mysql 5.6, Yii 2.0.7

I have read from other forum and modify my code by i still not get intended result

When I use without


->select()

i get output but i want output with


->select(["CONCAT(first_name,space(5),last_name,space(10),'  -  ',space(10),title) as title","name as image","description"])

[/size]


  CREATE TABLE `quickbrain_team` (

 `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',

 `first_name` varchar(45) NOT NULL COMMENT 'First Name',

 `middle_name` varchar(45) DEFAULT NULL COMMENT 'Middle Name',

 `last_name` varchar(45) NOT NULL COMMENT 'Last Name',

 `title` varchar(45) NOT NULL COMMENT 'Title',

 `designation` varchar(45) DEFAULT NULL COMMENT 'Designation',

 `photo` int(11) DEFAULT NULL COMMENT 'Photo',

 `description` text COMMENT 'Description',

 `created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT 'Created',

 `updated_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT 'Updated',

 `email` varchar(50) DEFAULT NULL COMMENT 'Email',

 `mobile` varchar(50) DEFAULT NULL COMMENT 'Mobile',

 `facebook` varchar(200) DEFAULT NULL,

 `twitter` varchar(200) DEFAULT NULL,

 `googleplus` varchar(200) DEFAULT NULL,

 `linkedin` varchar(200) DEFAULT NULL,

 `status` int(1) NOT NULL DEFAULT '1',

 PRIMARY KEY (`id`),

 KEY `fk_photo_idx` (`photo`),

 CONSTRAINT `fk_photo` FOREIGN KEY (`photo`) REFERENCES `quickbrain_image_gallery` (`id`) ON DELETE NO ACTION ON UPDATE CASCADE

) 



CREATE TABLE `quickbrain_image_gallery` (

 `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',

 `name` varchar(300) NOT NULL COMMENT 'Name',

 `caption` varchar(300) DEFAULT NULL COMMENT 'Caption',

 `path` varchar(50) NOT NULL COMMENT 'Path',

 `size` double(15,2) NOT NULL COMMENT 'Size',

 `type` varchar(10) NOT NULL COMMENT 'Type',

 `created_at` varchar(30) DEFAULT NULL COMMENT 'Created',

 `updated_at` varchar(30) DEFAULT NULL COMMENT 'Updated',

 PRIMARY KEY (`id`),

 KEY `name_ig_idx` (`name`)

)





    public function getPhotos()

    {

        return $this->hasOne(Images::className(), ['id' => 'photo'])->from(Images::tableName()." i");

    }







        $model=\backend\models\Team::find()

                    ->from(Team::tableName()." t")

                    ->select(["CONCAT(first_name,space(5),last_name,space(10),'  -  ',space(10),title) as title","name","i.id"])

                    ->joinWith("photos",true)->where('t.id=:page',[':page'=>$page])->one();
2

I would probably write like this (assuming you have generated Team model using Gii’s model generateor) :




$model = Team::find()->with('phpto')->where(['id' => $page])->one();

$dispTitle = $model->first_name . ' ' . $model->last_name . ' - ' . $model->title;

$photoName = $model->photo->name;

$photoId = $model->photo->id;

If you are going to use ActiveRecord, don’t try to do everything in SQL layer.

Some tasks may be done better using PHP when you are working with ActiveRecord.

And note that a certain kind of effort to optimize the query by hand may result in a malfunction of ActiveRecord.

3

Yes it works i get the result but what if i want output in a single object like


$model

as i want to access the details in view page as




$model->title

$model->description

$model->image
4

I don’t like the tiny tweaky optimization. It’s just cumbersome and useless at its best. :(

I would rather want to go straight and simple.

Anyway, here are some tips:

When you manually select ‘title’ as “CONCAT(first_name, … , title)”, you are populating a special value to the ‘title’ attribute of your Team object which normally would have got the value from ‘title’ column of the table. It’s very confusing.

Consider defining a dedicated attribute for it.




class Team extends ActiveRecord

{

   ...

   public $disp_title;

   ...

}


$query->select("CONCAT(first_name, ... ,title) as disp_title") ...


echo $model->disp_title;

Although ‘select(“name as image”)’ will not cause an SQL error, it won’t do anything to the Team object, since Team has no attribute named “image”. You have to define an attribute for it, too.




class Team extends ActiveRecord

{

   ...

   public $disp_title;

   public $image;

   ...

}


$query->select("name as image") ...


echo $model->name;

Check this part of the Guide: ActiveRecord - Selecting extra fields

But, once again, no need to do this kind of useless optimization since you are to fetch the related model.

And last but not least, joinWith works in a different way than you are expecting.

If you want to use ‘joinWith’ or ‘with’ to get the related model, you have to select the FK in the main table (‘photo’ in your case).

The following is from the guide:ActiveRecord - Lazy Loading and Eager Loading

Please take a look at the following wiki, too.

Relational Query - Lazy Loadnig and Eager Loading in Yii 2.0