Many To Many via unique records

Yii 2.0 General Discussions
1

If I have a table like so …

industry_id | town_id

1 | 1

2 | 1

2 | 1

And I do a many to many relation like so …




public function getIndustries() {

						

	return $this->hasMany(Industry::className(), ["id" => "fkIDWithIndustryID"])->viaTable(TownToIndustry::tableName(), ["fkIDWithTownID" => "id"]);

			

}

And I loop through it like so …




foreach($town->industries as $industry) {}

Then it only loops through the 2 records and not 3. Please explain why, also how can I get it to loop through the 3?

2 

SELECT industry_id FROM town2industry WHERE town_id = 1;

You’ve got 3 rows: 1, 2, 2


SELECT id FROM towns WHERE id IN (1, 2, 2);

You’ve got 2 rows: 1, 2

Becase the query is equal to:


SELECT id FROM towns WHERE id = 1 OR id = 2 OR id = 2;

Still 2 rows becase there are only 2 items: with id = 1 and id = 2.

I think the Yii AR does something similar. That is the reason. It’s my guess.

3

Yes it must do, because I was only getting 2 records, but I did expect 3, anyone know of a way to manipulate this so it returns 3 rows?

4

You should use JOIN.

I’m new to the Yii, so I can’t write the solution in AR Relations, but there is guide for it.

In SQL it looks like so:


SELECT industries.* FROM town2industry JOIN industries ON (town2industry.industry_id = industries.id) WHERE town2industry.town_id=1

I think in AR Relations it would be something like getting items from junction table, joining industries. So in your loop you will do someting like this:


foreach($town->town2industry as $t2i) {

    $t2i->industry; // here is the industry

}
5

Ok thanks for the info.

In the end I just decided to define 2 relations. One to access the "middle" table, and one to access the "relation" table through the "middle" table and I used the 2 relations under different circumstances.