SQL Join视图在Yii2中不起作用

Question

I am using 3 tables(property,property_rooms,property_images). I have a sql query and need to implement it in Yii action and view.

SELECT * FROM property JOIN property_rooms ON property.property_id = property_rooms.property_id JOIN property_images ON property.property_id = property_images.property_id 

In controller/action i have used the following code:

$sql ='SELECT * FROM property JOIN property_rooms ON property.property_id = property_rooms.property_id JOIN property_images ON property.property_id = property_images.property_id';
$properties = property::findBySql($sql)->all();          
return $this->render('index',['properties'=>$properties]);

In View file, I have used:

<?php foreach ($properties as $property): ?>
    <?= $property->property_id ?>
    <?= $property->price?>
<?php endforeach; ?>

The values for the first table is displaying but to access & diplay the values of the other tables.

Answer 1

Since you are using

$properties = property::findBySql($sql)->all();          

$properties will contain property models.

You can define relations (such as propertyRooms) and access to them using

<?php foreach ($properties as $property): ?>
    <?= $property->property_id ?>
    <?= $property->price?>
    <?php foreach($property->propertyRooms as $pr) : ?>
           <?php /* ... */ ?>
    <?php endforeach; ?>
<?php endforeach; ?>

For example, propertyRooms property can be defined inside property model as:

public function getPropertyRooms() { return $this->hasMany(PropertyRoom::className(), ['property_id' => 'property_id']); }

Finally, I'll use ActiveDataProvider methods to get properties data:

$properties = property::find()->with('propertyRooms')->all();

So you will get all properties data in a single step.