Result of one query as option to another query

imran.rafai · October 22, 2015, 12:30pm

Hi,

I am new to Yii2, but I am fascinated by the power of this framework. I am trying to develop an application and I have a small query. It shouldn’t be too complicated, but since I am new, I am completely lost!

Our business is into leasing apartment building from landlords and releasing each of those apartment units to customers. I have the following tables:

[b]Master Property: (The Apartment Building)

ID | Name | Location | Rent | <more fields>[/b]

[b]Property: (Individual Apartments in the Master Property)

ID | Name | Master_Property_ID | <more fields>[/b]

[b]Customer:

ID | Name | Email | Phone | <more fields>[/b]

[b]Tenancy:

ID | Customer_ID | Property_ID | Rent | <more fields>[/b]

I want a query to get all the Apartments (Property) which are part of a Building (Master Property). Then I want to use that result to run another query on the Tenancy table to get information like rents and customer. What I am looking to establish is when I go to Master Property > View action, I want the Master Property Information on the top and at the bottom, I want to list out all the Apartments on that Building with the current tenant and the rent.

I hope I made sense. Please ask me for any clarification. Thanks for your time. Any help much appreciated.

Imran

fabriziocaldarelli · October 22, 2015, 12:36pm

Take a look to:

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

Then try to start a project and write some code to handle your need.

imran.rafai · October 22, 2015, 12:59pm

Thanks Fabrizio for the reply. I have already read through the document. But I couldn’t find anything that could help me.

I have written few lines, which I think makes sense:


$subQuery = Property::find()->select(['id'])->where(['master_property_id' => $id])->all();        

$query = Tenancy::find()->where(['in', 'property_id', $subQuery]);        

$models = $query->all();

But I am getting an error:

[i][b]Database Exception – yii\db\Exception

Object of class app\models\Property could not be converted to string

Failed to prepare SQL: SELECT * FROM tenancy WHERE property_id IN (:qp0, :qp1, :qp2)[/b][/i]

Please help.

fabriziocaldarelli · October 22, 2015, 1:05pm




$subQuery = Property::find()->select(['id'])->where(['master_property_id' => $id])->all();

this will return all objects that respect ‘master_property_id = $id’ condition.

So the next expression:




$query = Tenancy::find()->where(['in', 'property_id', $subQuery]);

has no sense because $subQuery is a list of objects and not a query.

Go to section 10 of the link i’ve posted:

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

There you can find useful helps to solve your problem.

imran.rafai · October 22, 2015, 4:58pm

Thanks for your reply.

From the documentation, the Customer and Order table are related. Hence


$customer = Customer::findOne(123);

$orders = $customer->orders;

would work. But in my case ‘Master Property’ (MP) table has absolutely no relationship with ‘Tenancy’ (T) table. The MP is related to ‘Property’ § table which in turn is related to T table.

What I was trying to establish is something like in this example:

[sql]SELECT lastname, firstname FROM employees WHERE officeCode IN (SELECT officeCode FROM offices WHERE country = ‘USA’)[/sql]

The subquery returns all officeCode of the offices that are located in the USA.
The outer query selects the last name and first name of employees whose office code is in the result set returned from the subquery.

Sorry, maybe I am missing something quite obvious. But I am stuck.

fabriziocaldarelli · October 22, 2015, 5:25pm

Check it:

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

Your junction table should be Property §

imran.rafai · October 23, 2015, 12:19am

From the document


class Order extends ActiveRecord

{

    public function getItems()

    {

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

            ->viaTable('order_item', ['order_id' => 'id']);

    }

}

Here, the Order and Items are related through a VIA table Order_Item. And in my case Property and Customer are related through the VIA table called Tenancy. So if I want Property to get Customer or Vice Versa, that is fine.

But in my case, Master Property has no relation whatsoever with Tenancy table. So I am confused ‘how’ to create a relationship. Its not a Many to Many relationship. So how do I write the relationship like shown here:


class MasterProperty extends ActiveRecord

{

    public function getTenancy()

    {

        return $this->hasMany(Tenancy::className(), ['id' => 'property_id'])->via('Property');

    }

}

I am attaching MySQL designer view to understand my database. Please assist.

fabriziocaldarelli · October 23, 2015, 12:46am

It should be:




class MasterProperty extends ActiveRecord

{

    // A MasterProperty can have many properties, is it right?

    public function getProperties()

    {

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

    }


    // A property can have many tenancies, is it right?

    public function getTenancies()

    {

        return $this->hasMany(Tenancy::className(), ['property_id' => 'id'])->via('properties');

    }

}


$model = MasterProperty::findOne(1);    // id #1

$tenancies = $model->tenancies;

imran.rafai · October 23, 2015, 1:57am

Thank you! Absolutely Magical!

I know I am a terrible student, but you are a rockstar! I was little apprehensive because the code


  public function getTenancies()

    {

        return $this->hasMany(Tenancy::className(), ['property_id' => 'id'])->via('properties');

    }

has ‘$this->hasMany’ and I kept thinking how can Master Property have anything to do with Tenancy! But your explanation cut it. Anyway, now it works. Only thing is for me to figure out, how to put this data in a GridView Widget. Would you be kind enough to give some pointers on that too.

Once again, a big thank you!

fabriziocaldarelli · October 23, 2015, 2:59am

I’m happy that you have solved your problem.

What columns should the GridView display?

imran.rafai · October 23, 2015, 11:18am

So basically when I run the action: …index.php?r=master-property/view&id=1, I want all the Apartments of that building to be listed below. The columns to display are: Property.Name, Tenancy.Rent, Tenancy.End_Date

Doing


$model = MasterProperty::findOne($id);

$tenancies = $model->tenancies;

$properties = $model->properties;

gets me objects of Property and Tenancy, which I can verify by doing a ‘print_r()’. But putting it in GridView throws an error. I am assuming its a problem with converting this to a format the DataProvider is expecting it to be in.

Thanks for your help.

fabriziocaldarelli · October 23, 2015, 11:38am

In GridView you must use a data provider, such as ActiveDataProvider.

An ActiveDataProvider object need a ‘query’ parameter that is an ActiveQuery object instance.

So in that case you should make same relationship (that you have made for MasterProperty) applied to Property, making needed changes.




function actionGrid()

{

....


$dataProvider = new \yii\data\ActiveDataProvider([

    'query' => Property::find()->innerJoinWith('masterProperty')->where(['masterProperty.id' => $masterPropertyId])

]);


return $this->render('grid', ['dataProvider' => $dataProvider]);


}

In the grid file (but you can name it as you want), you will have $dataProvider object passed from Controller to fill ‘dataProvider’ parameter of the GridView object.

imran.rafai · October 23, 2015, 3:00pm

Thanks for your reply.

Following your instructions, I could successfully setup the ActiveDataProvider and all the property (data from the Property table) gets listed. But the data for RENT (from Tenancy table) is not getting set. I am getting ‘(not set)’. Here is what I am doing.

Master Property Model


class MasterProperty extends ActiveRecord

{    

     public function getProperties()    

      {        

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

      }        


     public function getTenancies()

      {        

       return $this->hasMany(Tenancy::className(), [ 'property_id' => 'id'])->via('properties');    

      }

}

Tenancy Model


class Tenancy extends ActiveRecord

{

    public function getRent()

    {

        return $this->rent;

    }

}

Master Property Controller


class MasterPropertyController extends Controller

{

    public function actionView($id)

    {

       $dataProvider = new ActiveDataProvider([

            'query' => Property::find()->innerJoinWith('masterProperty')->where(['master_property.id' => $id])

        ]);


        return $this->render('view', [

            'model' => $this->findModel($id),

            'dataProvider' => $dataProvider,

        ]);

    }

}

Master Property > View Action


  <?= GridView::widget([

        'dataProvider' => $dataProvider,

        'columns' => [

            ['class' => 'yii\grid\SerialColumn'],


            

             [

                'label'=>'Property Name',

                'format' => 'raw',

                'value'=>function ($data) {

                           return Html::a($data->name, ['property/view', 'id' => $data->id]);

                         },

             ],

                        

            'tenancies.rent',

            'bed',

            'bath',

            'electricity_num',

            'water_num',


        ],

    ]); ?>

On this view, everything gets displayed except for ‘tenancies.rent’. That field just displays ‘(Not Set)’. Please help.

fabriziocaldarelli · October 23, 2015, 6:02pm

Using innerJoinWith doesn’t load data from the model class used in the join.

To get data from the join you have to use ‘with’ method.

So ‘query’ parameter in $dataProvider changes in this way:




class MasterPropertyController extends Controller

{

    public function actionView($id)

    {

       $dataProvider = new ActiveDataProvider([

            'query' => Property::find()->innerJoinWith('masterProperty')->with('tenancies')->where(['master_property.id' => $id])

        ]);


        return $this->render('view', [

            'model' => $this->findModel($id),

            'dataProvider' => $dataProvider,

        ]);

    }

}

Take care that Property model contains relations named ‘tenancies’ (throught getTenancies() method).

imran.rafai · October 23, 2015, 9:59pm

Property Model


    public function getTenancies()

    {

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

    }

Tenancy Model


    public function getRent()

    {

        return $this->rent;

    }

Master Property Controller


    public function actionView($id)

    {

        

   

        $dataProvider = new ActiveDataProvider([

            'query' => Property::find()->innerJoinWith('masterProperty')->with('tenancies')->where(['master_property.id' => $id])

        ]);


        return $this->render('view', [

            'model' => $this->findModel($id),

            'dataProvider' => $dataProvider,

        ]);

    }

Master Property > View


   <?= GridView::widget([

        'dataProvider' => $dataProvider,

        'columns' => [

            ['class' => 'yii\grid\SerialColumn'],




             [

                'label'=>'Property Name',

                'format' => 'raw',

                'value'=>function ($data) {

                           return Html::a($data->name, ['property/view', 'id' => $data->id]);

                         },

             ],

            'tenancies.rent',

             // This one does not work! Comes up with '(Not Set)'


            'electricity_num',

            'water_num',


        ],

    ]); ?>

The result is the same - (Not Set). Could this have anything to do with the ‘hasMany’ relationship between Property and Tenancies? Please assist.

imran.rafai · October 24, 2015, 6:59am

Sorry I guess I forgot to mention in the last reply, the GridView does not pull the data for ‘tenancies.rent’. Please assist.

imran.rafai · October 24, 2015, 7:12pm

The output of


$testing = Property::find()->innerJoinWith('masterProperty')->with('tenancies')->where(['master_property.id' => $id])->all();

is as follows. The [tenancies] related data is not getting pulled up. Anything I am doing wrong with the code?

imran.rafai · October 24, 2015, 9:49pm

Some progress here. Let me update:

When I change the dataProvider query to:


$dataProvider = new ActiveDataProvider([

            'query' => Property::find()->innerJoinWith('masterProperty')->with('tenancies', 'customer')->where(['master_property_id' => $id])

        ]);

I am getting all the information I need when I do a print_r on a $test running the same query.

In the GridView if I use tenancies.rent it comes up with (Not Set), but if I use tenancies.0.rent it gets displayed correctly. Same thing for the name customer.0.name. Is there a better way? Or is that the correct implementation? Please assist.

imran.rafai · October 24, 2015, 10:44pm

Thanks Fabrizio Caldarelli. I think I have solved my problem. It was going back to my original question: QUERY IN A QUERY!

I did the following:


$subQuery = Property::find()->select(['id'])->where(['master_property_id' => $id]);


$dataProvider = new ActiveDataProvider([

            'query' => Tenancy::find()->where(['tenancy.active'=>1])->andWhere(['in', 'property_id', $subQuery])->innerJoinWith(['property', 'customer'])

        ]);

This gave me all the data the way I want. And since I was querying the Tenancy table, it was all with the hasOne relationship. So I could get rid of the ‘0’.

Thanks for your help. You pointed me in the right direction, so I kept reading and analysing. Cheers!