Relationship Between Posts and Users
++Recall that there was a foreign key relationship between the users and posts table. The entity-relationship diagram is shown below for convienence. +
+ +
+
++From the entity-relationship diagram above, we see that the posts table contains a field named author_id. This author_id field is a foreign key to the reference table users. In particular, the values in the author_id field should be of that from the users table's username field. One of the consequence of this relationship, thinking in terms of objects, is that each "post" belongs to an "author" and one "author" may have many "posts". +
+ ++We can model the relationship between posts and users table in Active Record by modifying the PostRecord and UserRecord classes as follows. +
+ ++The static $RELATIONS property of PostRecord defines that the property $author belongs to an UserRecord. In array(self::BELONGS_TO, 'UserRecord'), the first element defines the relationship type, in this case self::BELONGS_TO. The second element is the name of related record, in this case an UserRecord. The UserRecord is defined similarly below, the difference is that, the user record has many PostRecords. +
+ ++An array of UserRecord with and its corresponding posts may be fetched as follows. +
+ ++Further detailed documentation can be found in the quickstart Active Record docs. +
+ \ No newline at end of file diff --git a/demos/blog-tutorial/protected/pages/Day2/CreateDB.page b/demos/blog-tutorial/protected/pages/Day2/CreateDB.page index eebda4c1..de4e2436 100644 --- a/demos/blog-tutorial/protected/pages/Day2/CreateDB.page +++ b/demos/blog-tutorial/protected/pages/Day2/CreateDB.page @@ -30,9 +30,11 @@ CREATE TABLE users ( /* create posts table */ CREATE TABLE posts ( post_id INTEGER NOT NULL PRIMARY KEY, - author VARCHAR(128) NOT NULL, /* references users.username */ + author_id VARCHAR(128) NOT NULL + CONSTRAINT fk_author REFERENCES users(username), create_time INTEGER NOT NULL, /* UNIX timestamp */ title VARCHAR(256) NOT NULL, /* title of the post */ + content TEXT, /* post body */ status INTEGER NOT NULL /* 0: published; 1: draft; 2: pending; 2: denied */ ); @@ -43,14 +45,17 @@ INSERT INTO posts VALUES (NULL, 'admin', 1175708482, 'first post', 'this is my fWe then use the SQLite command line tool to create the SQLite database. We create a directory protected/data to hold the SQLite database file. We now execute the following command under the directory protected/data:
-