Yii Framework Forum: Yii2 model relations - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

Yii2 model relations generating mysql query from relations

#1 User is offline   Bilal Jafar 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 2
  • Joined: 27-December 17

Posted 27 December 2017 - 04:07 AM

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.

This post has been edited by softark: 27 December 2017 - 06:27 AM
Reason for edit: added code tags

0

#2 User is offline   Bilal Jafar 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 2
  • Joined: 27-December 17

Posted 27 December 2017 - 06:05 AM

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...???

This post has been edited by softark: 27 December 2017 - 06:29 AM
Reason for edit: added code tags

0

#3 User is offline   softark 

  • Keep It Simple
  • Yii
  • Group: Moderators
  • Posts: 3,498
  • Joined: 16-February 11
  • Location:Japan

Posted 27 December 2017 - 06:24 AM

/* topics merged */

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.yiiframew...ive-record.html

Especially the subsection of "Working with Relational Data".

It's a little bit long but is a MUST READ.
0

#4 User is offline   alrazi 

  • Elite Member
  • Yii
  • Group: Moderators
  • Posts: 1,533
  • Joined: 08-August 12
  • Location:Durban, South Africa

Posted 28 December 2017 - 12:09 AM

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
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users