Joining Tables

Yii 2.0 General Discussions
1

Hi all!

I’m new to Yii - so bear with me, please. Any suggestions and hints that lead me to the right direction are greatly appreciated…

My problem:

I have a TbGridView that’s filled with values from a query (ok, I know that’s obvious :)

Now, I need some values from a foreign table. The problem is, that the foreign table has fieldnames that are similar to the original table.

In SQL, I’d do something like this:


SELECT table1.*, table2.fieldname AS altered_fieldname FROM table1

LEFT JOIN table2 ON table1.foreign_id = table2.id

That’s pretty simple, isn’t it?

But: How the heck do I do this in Yii???

I found out about relations, but this always seems to join the whole foreign table, without the possibility to change fieldnames from the foreign table.

I also looked at criterias (which are another subject, since I need to be able to search that foreign field), but that’s quite confusing as to what a criteria actually does.

I’m used to write my own SQL-statements, so accessing the database with the yii-approach is quite confusing to me…

Thanks in advance for any help.

2

Here is an example using Query, but with inner join.




$query = new Query();

$data = $query->select('l.id, l.town, l.region, l.postcode, l.lat, l.long')

                    ->from('location l')

                    ->join('INNER JOIN', 'location_index li', 'li.location_id = l.id')

                    ->where('li.search_string LIKE :query', array(':query' => "%$search%"))

                    ->createCommand()

                    ->queryAll();

If you’re using ActiveRecord then you can set up relations in your model easily enough.




public function getLocation()

{

        return $this->hasOne(EntityLocation::className(), ['id' => 'location_id']);

}

The above example is a relation method in a model called Entity that has a one-to-one relation with EntityLocation. the’id’ is Location::id and the location_id is Entity::location_id. FYI if you set up foreign keys in your database then Yii’s crud model generator will set up relations for you.

Cheers

Ash