Yii2 model relations

I have the following relations in yii2 model

    public function getStreamsFormations()

            {

                return $this->hasMany(StreamsFormations::className(), ['stream_id' => 'id']);

            }

    public function getFormations()

            {

                return $this->hasMany(Formations::className(), ['id' => 'formation_id'])->via('streamsFormations');

            }

How to create complete mysql query from the relation "getFormations()"?

I need it to use it some where else…

I used …

    $query = $model->getFormations()->createCommnad()->rawSql;

But it skipped the via relation tables from the query.

consider the below example…

class Customers extends ActiveRecord

{

    public function getOrders()

    {

        return $this->hasMany(Orders::className(), ['customer_id' => 'id']);

    }

    public function getOrderItems()

    {

        return $this->hasMany(OrderItems::className(), ['order_id' => 'id'])

                    ->via('orders');

    }

}

how i can generate any one of the follwing query from the getOrderItems() relation

SELECT * FROM `order-items`

LEFT JOIN `orders` ON `orders`.`id` = `order-items`.`order_id`

LEFT JOIN `customers` ON `customer`.`id` = `orders`.`customer_id`

OR

SELECT `order-items`.* FROM `order-items`,`orders`,`customers`

WHERE `customer`.`id` = `orders`.`customer_id` AND `orders`.`id` = `order-items`.`order_id`

OR

SELECT * FROM `order-items` WHERE `order_id` IN(

    SELECT * FROM `orders` WHERE `customer_id` IN(

        SELECT * FROM `customers`

    )

)

i use the following code to do this.

$customers = Customers::findAll();

$query = $customers->getOrderItems()->createCommand()->rawSql;

but it only generates

SELECT * FROM `order-items`

What to do…

[color="#006400"]/* topics merged */[/color]

Hi Bilal, welcome to the forum.

I’m sorry if I’m wrong, but you don’t seem to fully understand what ActiveRecord is and what relation between those ActiveRecords is.

The relational method of ActiveRecord (e.g. getOrderItems()) is not meant for standalone use. It is used by ActiveRecord internally for fetching the related models.

For instance, just try the following:

$customers = Customer::find()->all();

foreach($customers as $customer) {

    $orderItems = $customer->orderItems;

    foreach($orderItems as $orderItem) {

        echo $orderItem->name;

    }

}

Or, preferably:

$customers = Customer::find()->with('orderItems')->all();

foreach($customers as $customer) {

    $orderItems = $customer->orderItems;

    foreach($orderItems as $orderItem) {

        echo $orderItem->name;

    }

}

In the above, “getOrderItems()” method is called implicitly and you don’t have to worry about the raw SQL that the method generates for fetching the related models.

Please read the following section of the Guide:

Guide > Working with Databases > Active Record

http://www.yiiframework.com/doc-2.0/guide-db-active-record.html

Especially the subsection of "Working with Relational Data".

It’s a little bit long but is a MUST READ.

Hi,

By default yii will lazy load your associations, if you looking to generate the sql with joins you need to eager load your association, to do that tack on joinWith() like so

$customerSql = Customers::find()->joinWith('OrderItems')->createCommand()->sql;

edit: @softark thanks for the correction