CODEWITHSUNDEEP
yii2expression
Fadly Dzil
Fadly Dzil

Reputation: 2226

Select string using Expression Yii2 is detected as column

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,

    ]);

Upvotes: 2

Views: 2462

Answers (2)

dataskills
dataskills

Reputation: 656

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

Upvotes: 4

ScaisEdge
ScaisEdge

Reputation: 133380

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")

Upvotes: 1

Related Questions