SubQuery select reference parent table

Yii 2.0 General Discussions
1

Hi

I’m trying to convert this to Yii Query builder and I’m stuck on the final piece.

$approved = (new Query())->select(['TOPIC_ITEM_VALUE'])
                                     ->from('CSPROD.PART_TOPIC_ITEM_VALUE PTV')
                                     ->innerJoin('CSPROD.TOPIC_ITEM TI', 'TI.TOPIC_ITEM_ID = PTV.TOPIC_ITEM_ID')
                                     ->where(['PTV.DEL_DT' => null])
                                     ->andWhere(['TI.TOPIC_ITEM_NAME' => 'APPROVAL'])
                                     ->andWhere(['PT.PART_TOPIC_ID' => 'PTV.PART_TOPIC_ID']); --> this line.
        $query = (new Query())->select(['PART_TOPIC_ID', 'PARTICIPANT_ID'])
                              ->addSelect(['APPROVAL' => $approved])
                              ->from('CSPROD.PART_TOPIC PT')
                              ->andWhere(['in', 'PT.TOPIC_ID', $topics]);

Where I’ve highlighted the line. That’s read in the QueryBuilder as AND ("PT"."PART_TOPIC_ID"='PTV.PART_TOPIC_ID') where PTV.PART_TOPIC_ID is interpreted as a string. Really it should be a table.key = table.key

How do I achieve this in Yii?

2

I solved this. You need to do:

->andWhere(['PT.PART_TOPIC_ID' => new Expression('PTV.PART_TOPIC_ID') ]);

A good forum feature I saw on sonarQube the other day was the ability to mark something as “solved”. Perhaps that would benefit the community

1 Like