Yii2: selecting column with control flow functions

[omer-sds]

I am working in YII2 Framework on the following query

SELECT T.id, T.name,T.status, IFNULL(T.image,'no-image.png') as DP FROM teams T

here is my code

$modelTeam  = Teams::find()->
select(  ['T.id','T.name','T.status'] )
->addSelect([new \yii\db\Expression('IFNULL(T.image,\'no-image.png\') AS DP')])
->from('{{%teams}} T')->all();
print_r(ArrayHelper::toArray($modelTeam));

output:

(
    [0] => Array
        (
            [id] => 1
            [name] => Pirates
            [status] => active
        )

    [1] => Array
        (
            [id] => 2
            [name] => The Smashers
            [status] => active
        )
)

but it never shows the DP column can you tell what am i doing wrong here

[samdark]

Do you have DP property in your Teams model?

[omer-sds]

yes the model has the property defined see below

class Teams extends \yii\db\ActiveRecord
{
    public $DP;
    /**
     * @inheritdoc
     */
    public static function tableName()
    {
        return '{{%teams}}';
    }

    /**
     * @inheritdoc
     */
    public function rules()
    {
        return [
            [['name'], 'required'],
            [['image', 'status'], 'string'],
            [['added_on', 'updated_on'], 'safe'],
            [['name'], 'string', 'max' => 150],
            [['name'], 'unique'],
        ];
    }

    /**
     * @inheritdoc
     */
    public function attributeLabels()
    {
        return [
            'id' => 'ID',
            'name' => 'Name',
            'image' => 'Image',
            'status' => 'Status',
            'added_on' => 'Added On',
            'updated_on' => 'Updated On',
            'DP'=>'Display Picture'
        ];
    }

    /**
     * @return \yii\db\ActiveQuery
     */
    public function getPlayerToTeams()
    {
        return $this->hasMany(PlayerToTeams::className(), ['team_id' => 'id']);
    }
}

[samdark]

Hmm... that should work according to http://www.yiiframework.com/doc-2.0/guide-db-active-record.html#selecting-extra-fields. Can you check in the debugger which SQL is actually executed?

[yii-bot]

_This is an automated comment, triggered by adding the label question._

Please note, that the GitHub Issue Tracker is for bug reports and feature requests only.

We are happy to help you on the support forum, on IRC (#yii on freenode), or Gitter.

Please use one of the above mentioned resources to discuss the problem.
If the result of the discussion turns out that there really is a bug in the framework, feel free to
come back and provide information on how to reproduce the issue. This issue will be closed for now.

[omer-sds]

the debugger is printing the right query here you can see
SELECTT.id,T.name,T.status, IFNULL(T.image,'no-image.png') AS DP FROMbadminton_teamsT`

btw: why did the bot closed the issue ?

[dynasource]

the bot can be cranky sometimes ;)

[samdark]

Hmm... that is weird. Can you reproduce that starting with basic app and clean models?

[omer-sds]

i just started to deploy the code into yii framework from a simple php&mysql application, i have only generated model and crud and the above query is the first query in the application inside the frontend\controllers\SiteContoller's index function , infact it's only the first part of the query the complete query looks like this

"SELECT T.id, T.name,T.status, IFNULL(T.image,'no-image.png') as DP,
        (SELECT COUNT(*) FROM badminton_matches M WHERE (M.match_type='final' AND M.winner_id=T.id)) as medals, 
        (SELECT COUNT(*) FROM badminton_matches M WHERE ((M.team_one =T.id or M.team_two = T.id) and M.winner_id IS NOT NULL)) as played,
        (SELECT COUNT(*) FROM badminton_matches M WHERE M.winner_id=T.id) as won, 
        (SELECT COUNT(*) FROM badminton_matches M WHERE ((M.team_one =T.id or M.team_two = T.id) AND (M.winner_id!=T.id))) as lost,
        ((SELECT (SUM(CASE WHEN BMS.winner_id=T.id THEN BMS.points_won ELSE BMS.points_lost END)-SUM(CASE WHEN BMS.winner_id=T.id THEN BMS.points_lost ELSE BMS.points_won END))/(COUNT(BMS.id)) FROM
 badminton_match_score BMS 
 JOIN badminton_matches M ON (M.id=BMS.match_id) where M.team_one=T.id OR M.team_two=T.id and M.winner_id is not null)) AS AVG_SCORE,
(
            ((SELECT COUNT(*) FROM badminton_matches M WHERE M.winner_id=T.id)*2) + (SELECT COUNT(*) FROM badminton_matches M WHERE (M.match_type='quarter' AND M.winner_id=T.id))
            +
            ((SELECT COUNT(*) FROM badminton_matches M WHERE (M.match_type='semi' AND M.winner_id=T.id))*2)
                +
            ((SELECT COUNT(*) FROM badminton_matches M WHERE (M.match_type='final' AND M.winner_id=T.id))*5)

 ) as Points 
FROM badminton_teams T 
order by (Points) DESC, lost ASC, AVG_SCORE DESC"

i am using yii2-advaced-template by link

[primipilus]

try to use property name in lowercase
dp instead of DP

[omer-sds]

@primipilus , that did'nt helped either.

[samdark]

If you run this query you're manually you're getting correct results, right?

[omer-sds]

@samdark damn right i am running the application with the results here you can see the ranking and display picture thumbs displayed in the background ruuning application in php&mysql

[omer-sds]

here is the table structure by running show create table;

CREATE TABLE `teams` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(150) NOT NULL,
 `image` text,
 `status` enum('active','in-active') NOT NULL DEFAULT 'active',
 `added_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `updated_on` datetime DEFAULT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=latin1

[primipilus]

do you write like this?
public $dp;

ArrayHelper::toArray($modelTeam) - not show public propperties of model

a had the same problem with UPPERCASE property and ArrayHelper::toArray

[omer-sds]

@primipilus Yes i did, if i dont use the array helper and print the model object instead of array the it shows the public property for the model , hmm.. that's strange see below result for printing the model object

(
    [0] => common\models\Teams Object
        (
            [dp] => IMG_20161019_160438282.jpg
            [_attributes:yii\db\BaseActiveRecord:private] => Array
                (
                    [id] => 1
                    [name] => Pirates
                    [status] => active
                )

            [_oldAttributes:yii\db\BaseActiveRecord:private] => Array
                (
                    [id] => 1
                    [name] => Pirates
                    [status] => active
                )

            [_related:yii\db\BaseActiveRecord:private] => Array
                (
                )

            [_errors:yii\base\Model:private] => 
            [_validators:yii\base\Model:private] => 
            [_scenario:yii\base\Model:private] => default
            [_events:yii\base\Component:private] => Array
                (
                )

            [_behaviors:yii\base\Component:private] => Array
                (
                )

        )
)

[primipilus]

so what conclusions?
this solves your problem?

[omer-sds]

Well i got the point where the problem is and to get the desired results i have to use the ArrayHelper::toArray() with the mapping like below

ArrayHelper::toArray($modelTeam,[
                    'common\models\Teams'=>[
                        'id',
                        'name',
                        'status',
                        'dp'
                    ]
                ])

[samdark]

Glad that it's solved.