Select string using Expression Yii2 is detected as column

Question

I have a query like this :

use yii\db\Expression;

public function search($params)
{
$query = (new \yii\db\Query())
        ->select([
                "ir.id",
                "ir.no_surat",
                "tc.level",
                "nominal" => 'tc.cleaning',
                new Expression("clean as tagihan"),
                "tc.ditagihkan_bulan"
            ])

        ->from('ydepotras_estimator.repair_estimate as re')
        ->leftJoin(['ydepotras_estimator.inspection_report as ir'],
            "ir.id = re.inspection_id")
        ->innerJoin(['ydepotras_finance.tagihan_cleaning as tc'],
            " re.id = tc.repair_estimate_id");
}

Please see in new Expression("clean as tagihan"), Yii2 detectedclean as column. But I need the clean as value on tagihan's column

+----+-------------+
| id |   tagihan   |
+----+-------------+
| 1  |   clean     |
| 2  |   clean     |
+----+-------------+

Update

UNION in Yii2

$cleanigQuery = (new \yii\db\Query())
        ->select([
            "ir.id",
            "ir.no_surat",
            "tc.level",
            "nominal" => 'tc.cleaning',
            new Expression("clean as tagihan"),
            "tc.ditagihkan_bulan"
        ])
        ->from('ydepotras_estimator.repair_estimate as re')
        ->leftJoin(['ydepotras_estimator.inspection_report as ir'],
            "ir.id = re.inspection_id")
        ->innerJoin(['ydepotras_finance.tagihan_cleaning as tc'],
            " re.id = tc.repair_estimate_id");

$oneBarQuery = (new \yii\db\Query())
        ->select([
            "ir.id",
            "ir.no_surat",
            "tob.level",
            "nominal" => 'tob.one_bar',
                new Expression("one_bar as tagihan"),
            "tob.ditagihkan_bulan"
        ])
        ->from('ydepotras_estimator.repair_estimate as re')
        ->leftJoin(['ydepotras_estimator.inspection_report as ir'],
            "ir.id = re.inspection_id")
        ->innerJoin(['ydepotras_finance.tagihan_one_bar as tob'],
            " re.id = tob.repair_estimate_id");

So, I can do something like this :

$cleanigQuery->union($oneBarQuery, true);

    $query = (new \yii\db\Query())
        ->select('*')
        ->from(['u' => $cleanigQuery])
        ->orderBy('no_surat');

    $dataProvider = new ActiveDataProvider([
        'query' => $query,

    ]);

Your code is fine, you only have tu put your striung to show into extra semicolons to tell sql that this is a string and no keyword or column like this: new Expression("'one_bar' as tagihan") — Radon8472
– Radon8472, Commented Sep 12, 2023 at 6:12

dataskills · Accepted Answer · 2018-12-24 18:04:53Z

4

Like this: $query->select("*,CONCAT('clean') as tagihan")

answered Dec 24, 2018 at 18:04

dataskills

6567 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ScaisEdge · Accepted Answer · 2017-06-26 17:05:24Z

1

in this case you could use a literal select statement

          ->select("ir.id, ir.no_surat, tc.level, 
                tc.cleaning, 'clean' as tagihan, tc.ditagihkan_bulan")

edited Jun 26, 2017 at 17:05

answered Jun 26, 2017 at 16:21

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

7 Comments

Fadly Dzil Over a year ago

I have try this way a lot of time, but still, Unknown column 'clean' in 'field list'

ScaisEdge Over a year ago

be sure you have a clean column and if it is could be you must assign the table alias .. answer update

Fadly Dzil Over a year ago

I am pretty sure, I dont have a column with 'clean' name

ScaisEdge Over a year ago

then if you don't have a column with name clean .. why you are trying to select it ??' .. or better what do you mean with "clean as tagihan" ?

ScaisEdge Over a year ago

In this case you need a literal value eg: 'clean' as tagihan .. answer updated .

|

Collectives™ on Stack Overflow

Select string using Expression Yii2 is detected as column

2 Answers 2

Comments

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related