Join with same model

I would just like to share a small note on joining 2 records of the same model.

In Yii-1 this was pretty straightforward by setting aliases but

in Yii2 a lot changed but the solution seems straightforward enough.

Sometimes it can be necessary to handle self refering models due to converting legacy systems

(what I am working on right now).

Suppose you have a model ‘productorder’ with the following fields

• id (auto incrementing insertion id)

• creation date (set to the date of creation)

• productgroup_id (id to group the product orders by)

• e.g. description.

Every time e.g. the description for a productorder is changed,

a new record is inserted but 1 field is used as an identifier to

group these product orders. This is the productgroup_id and when updating an productorder

it is used to create the next productorder record.

If you query productorder by a specific productgroup_id then you would see all the changed that have

been made over time.

When creating an overview of the most up to date productorders, you

can use this very simple query:

SELECT `productorder`.*

FROM `productorder` `productorder` LEFT JOIN `productorder` po2

 ON (`productorder`.productgroup_id = po2.productgroup_id AND `productorder`.id < po2.id)

WHERE po2.id IS NULL;

This very simple query will find all the latest records for each unique productgroup.

When you want to show only the latest record in a gridview, you will need to specify a ‘search’ method

like this:

public function search($params) 

    $query = Order::find();

    $dataProvider = new ActiveDataProvider([

        'query' => $query,

        'pagination' => [ 'pageSize' => 50,],]);


		

    $query->select('`productorder`.*')

        ->from('productorder `productorder`')

        ->leftJoin('productorder po2', '(`productorder`.productgroup_id = po2.productgroup_id AND `productorder`.id < po2.id)')

        ->where('po2.id IS NULL');


  if (!($this->load($params) && $this->validate())) { return $dataProvider; }


   // adjust the query by adding the filters

   if (strlen($this->description)>0)   { $query->andFilterWhere(['like', 'productorder.description', $this->description]); }


return $dataProvider;

}

Note that the alias ‘productorder’ has been added to the ‘andFilterWhere’.

This should make the join on the same table possible and one can also add other parts of the query in the same way as one usually does.

I hope this might be usefull for someone else until db aliasses have been released in a next milestone.

Maybe these is a much easier way for doing this, but I could not find it within the current documentation. Any suggestions are welcome.