Adding columns into query

Hi!

I have one question about usability of the code.

So, I have two tables :


CREATE TABLE Employee (

    id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,

    departmentId SMALLINT UNSIGNED NOT NULL

        COMMENT 'CONSTRAINT FOREIGN KEY (departmentId) REFERENCES Department(id)',

    firstName VARCHAR(20) NOT NULL,

    lastName VARCHAR(40) NOT NULL,

    email VARCHAR(60) NOT NULL,

    ext SMALLINT UNSIGNED NULL,

    hireDate TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    leaveDate DATETIME NULL,

    INDEX name (lastName, firstName),

    INDEX (departmentId)

)

and


CREATE TABLE Department (

    id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,

    deptHeadId MEDIUMINT UNSIGNED NOT NULL

        COMMENT 'CONSTRAINT FOREIGN KEY (deptHeadId) REFERENCES Employee(id)',

    name VARCHAR(40),

    INDEX (deptHeadId),

    UNIQUE (name)

)

I executed :


model Department

model Employee

crud Department

crud Employee

I want to concatenate two columns by “CONCAT(firstName, ’ ', lastName) as fullName” and get access to “fullName” column.

There’s my way how I do it :

  1. In Employee model I created beforeFind() method



class Employee extends CActiveRecord

{

.....

    public function beforeFind()

    {

        $column = new CMysqlColumnSchema();

        $column->dbType = 'varchar(255)';

        $column->type = 'string';

        $column->name = 'fullName';

        $column->rawName = '`fullName`';

        $column->isPrimaryKey = false;

        $column->isForeignKey = false;

        $column->allowNull = true;

        $this->metaData->columns["fullName"] = $column;

    }

.....

}



  1. And in one of view I created this code :



<?php

$criteria = new CDbCriteria();

$criteria->select = 'CONCAT(firstName, \' \', lastName) as fullName';

echo CHtml::activeDropDownList($model, 'deptHeadId', CHtml::listData(

  Employee::model()->findAll($criteria), 'id', 'fullName'

 ));

?>



I think that my way is … some “dirty” and “not flexible” :)

Is there more graceful way to do it?

Tahnks.

Sure!

You can define a method in your model:




public function getFullName(){

   return $this->firstName.', '.$this->lastName;

}



and then in yours views you can use




$yourModel->fullName();



No. It’s not.

Main string is :

CHtml::listData(Employee::model()->findAll($criteria), ‘id’, ‘fullName’)

I need to get ‘fullName’ column in query for using it in CHtml::listData (third argument).

Did you try it?

This is the way I do this kind of stuff and works perfect…

Ohhh. Yes. It’s working! I forget to remove definition of $column in ‘beforeFind’ method ;)

It’s very simple! Cooool!

Thank you!

I tried this also, but it didn’t work for me, because I need to use getFullName() in a Department model, rather than in the Employee model. I’m not sure how to do that.

How is it possible?

I tryed to do this:

$people=CHtml::listData($people,‘id’,‘fullName’);

and the model is this one

   public function fullName()


   {


     return &#036;this-&gt;name.' '.&#036;this-&gt;surname;


   }

but it gives me this error:

Property "Guy.fullName" is not defined.

How can i solve it?

Cause the method mus be called getFullName()

public function getFullName()

   {


     return &#036;this-&gt;name.' '.&#036;this-&gt;surname;


   }

and used this way:

$model->fullName;

Or

$people=CHtml::listData($people,‘id’,‘fullName’);

So everytime i need to create a custome model query to be used "as model attribute" i musq call it get plus the name (so getSomethig ) ?

Thansk,

Riccardo

@riccardo

Yes, this is because the “magic” behind the scenes…;)

@waveslider

you can use this way:

$something=CHtml::listData($someModel,‘id’,‘departament.fullName’);

Where $someModel is a model that has a departament relation pointing to some Departament model.

In Departament model you mus define getFullName() to return what you want.

Thank you so much for this information. It would be nice to have this in the definite guide.

Still one more question though: how would I compare user input to this concatenated string? For example a search field in a cgridview widget.

I can’t use something like


$criteria->compare('Employee.fullName',$this->user);

//or

$criteria->addSearchCondition('Employee.fullName',$this->user);

Or do i need to specify the concat statement all over again (like I’m doing now)?

Any suggestions?

You have to specify the search you need.

For example:




$criteria->compare('Employee.name',$this->name, true);

$criteria->compare('Employee.surname',$this->surname, true);



or




$criteria->compare('Employee.name',$this->fullname, true);

$criteria->compare('Employee.surname',$this->fullname, true, 'OR');



Thank you for the input, but that wouldn’t do I’m afraid(, because my real application has a rather complex concatenation with fields from 3 different tables).

Currently I’m using




$criteria->addSearchCondition('concat(Employee.name," ",Employee.surname)',$this->user);



which works as intended: a user can input a search string like "e S" and get "Pete Sampras" as resultset.

My question was: do I really need to spell out the concat in the search condition again? Or can I use the Employee.fullName in the criteria somehow?

Hi Plantin!!

I have never used ‘concat’ in my project, so I am answering you only by “school theory”.

Yon can use HAVING instead of WHERE, so by doing:




$criteria->having="Employee.fullName LIKE '%:fullname%'";

$criteria->params[':fullname']=$fullname;



It should work. like that you can find “Pete Sampras” even by searching ‘e S’.

This work because WHERE is applied before calculate aggregation function (like max, avg and even concat), while HAVING is applied after, and so you can use your concat without rewrite the statement.

Pay attention that for this reason is a bit less efficient, because the concat will be done on all fields, even on fields that will be later discaded by HAVING.

Thank you Zaccaria for your information. I hadn’t considered a ‘having’ clause.

Unfortunately, I think I finally understand the real problem.

In the Employee model there’s a getFullName function that allows yii to use Employee->fullName as an attribute.

In the Employee relation (from another model), Employee.fullName has absolutely no meaning at all because it is never declared. It has to be included in a select statement.

In short, I have to do both to get both.

When implementing the having-clause, I noticed a rather user unfriendly inconvenience: it’s case-sensitive as opposed to the addSearchCondition. Just to let you know.

ei thanks for your reply -renzy