Yii Framework Forum

Multirows subquery


(micpan) #1

Hi all!
How to do this (like oracle ‘cursor’ clause) with Mysql using Yii2 tools:

select id,
col1,
cursor(select col2, col3
from another_table t2
where t2.id = t1.another_table_id)
from t1


(Ptheofan) #2

you need to use the DBExpression class. This will add the term exactly as you write it.
Something like this should work though I haven’t tested it.

$t1->find()->select(['id', 'col1', new DbExpression('cursor(select col2, col3 from another_table t2 where t2.id = t1.another_table_id)'])->all();

(micpan) #3

All i need is docs a little bit! (and some time for it)

https://www.yiiframework.com/doc/guide/2.0/en/db-active-record#lazy-eager-loading

// find customers and bring back together their country and active orders
// SELECT * FROM `customer`
// SELECT * FROM `country` WHERE `id` IN (...)
// SELECT * FROM `order` WHERE `customer_id` IN (...) AND `status` = 1
$customers = Customer::find()->with([
    'country',
    'orders' => function ($query) {
        $query->andWhere(['status' => Order::STATUS_ACTIVE]);
    },
])->all();