Getting Many-Many Related Fields

http://www.yiiframework.com/tutorial/image?type=guide&version=1.1&lang=en&file=er.png

Take a look at the example from Yii tutorial above. Let say tbl_post_category has additional field, for example date_added.

I would like to get that value along with the posts values so I could access it like this:




$categories=...;


foreach ($categories as $category) {

   foreach ($category->posts as $post) {

      echo 'Post ',$post->id,' added: ',$post->date_added,'<br>';

   }

}



That is bad db design, Actually whole idea of having tbl_post_category is to maintain many-to-many relation. You do not save any info into this table, even it is transparent for your system(Means you, your classes, your code does not about it). Only you know is category and post is many-to-many. the tbl_post_category is just helper method. which has two column. You put date_added either in category, or in post table. The big orms like hibernate, you just tell this two models are connected in many-to-many way, So it will create those two tables(Post and Category) and for maintaining the relationship it creates extra post_category table. So for developer it was done in transparent way.

So conclusion is You do not save any info in this kind of helper tables(needed for maintaining many-to-many relationship)

I strongly disagree. There are cases where the relationship has to bear a value. The ORM must not always dictate the schema of your DB or you will lose many possibilities.

Let’s consider another example: a movie DB. If I have a “Movie” table and a “Person” table, do I have to set up a table for each kind of relationship? Well, there is Actor, Director, Writer, Producer, Composer, Casting, Makeup, Costume… I’d need twenty tables, and it would be a pain to query them all. Don’t forget MySQL recommend to avoid querying more than 7 tables in one query. Even listing the complete crew/cast would be fastidious. So the easiest solution if to add one or more fields to the “Movie\_Person” table. For instance: Crew(id, movieid, personid, role) and RoleDetail(crewid, surname, datebegin…).

By the way, Yii can instantiate a different model according to a column (e.g. "role").

Another example is EAV: too many attributes to put them in columns, and integer/float values. Relational databases like Postgresql and MySql aren’t perfectly suited to deal with this kind of modeling, but they can do a good job when the data isn’t massive.

Add a date_added property to the model Post and describe it in the rules(). Add a method called getPosts(&#036;date=null) to the model Category that will fetch the posts along with the extra field. Something like:




public function getPosts($date=null)

{

    $sql = "SELECT p.*, cp.date_added FROM Category_Post cp JOIN Post p ON p.id=cp.post_id";

    $params = array();

    if ($date) {

        $sql .= " WHERE cp.date_added = :date";

        $params[':date'] = $date;

    }

    return Post::model()->findAllBySql($sql, $params);

}



Now you can write &#036;category-&gt;posts[0]-&gt;date_added.

Well

  1. I am not telling we need to follow to ORM, I just wanted to explain what I mean by transparent

  2. Can you give me real example that I can put in meddle table? It does not makes sense for me to put any data in it.

  3. In computer science there are 3 DO NOT rules(Please refer to Donald Knuth). How do you maintain many-to-many without creating tables? You need to create it, is not it? And you are telling many tables becomes slow, … First make something makes sense, before making it fast(this is one of do not rule, first make it simple and straight, DO NOT think it’s performance).

So my question for you (François Gannaz) is can you give real example for putting data in middle layer table(that makes sense of course)? Please leave tables count for now, as long as we are going to have bad design we do not care tables count(DO it later).

I never had any info in middle table.

Please read carefully my previous post. I gave an example of such a table. Its name was "Crew" with a "role" attribute that could be an enum of 20 values (or more cleanly, a FK on a third table). Ternary relationships do exists in the wild, and some ORM can even handle them properly (nHibernate).

BTW, I did not look for performance. I don’t know why you think I emphasized on this. Of course a clear and useful design is often to be preferred, and I think a talk about “premature optimization” is totally inadequate here.

I think it’s very common that sometimes we want to add an extra column to a MANY_MANY bridge table.

For instance:




Person (id, name)

Group (id, name)

PersonGroup (person_id, group_id, join_date)



Isn’t it a common requirement to record the date when a person joined a group? Where do you want to store it? I want to store it in the bridge table.

I think it’s not appropriate to call this a “bad design”, just because you can’t apply a clean-cut MANY_MANY relation to it. What we have to do is to apply the proper relations among those tables … two 1:N relations.

No reason to favor MANY_MANY relation all the time. :)

in this case you should define model for Person and also for Group?