Relational Yii ActiveRecord for a NULL composite key table

Question

To store custom bookmarks on any site, I have a table with a composite key:

CREATE TABLE bookmarks ( user_id int not null, book_id int not null, page_id int, ... ); CREATE UNIQUE INDEX ON bookmarks(user_id, book_id, page_id); 

Note that page_id can be NULL, while user_id and book_id cannot. When page_id is null, the bookmark is set for the entire book, otherwise for a specific page.

The corresponding ActiveRecord class defines some relationships:

 public function relations() { return array( "user" => array(self::BELONGS_TO, "User", "user_id"), "book" => array(self::BELONGS_TO, "Book", "book_id"), "page" => array(self::BELONGS_TO, "Page", "page_id"), ); } 

and primaryKey () method ::

 public function primaryKey() { return array("user_id", "book_id", "orig_id"); } 

Now I want to get all the bookmarks for the entire book for some users. So I:

 $bookmarks = Bookmark::model()->findAll(array( "condition" => "t.user_id = :user_id AND t.page_id IS NULL", "params" => array(":user_id" => 1), )); 

It works fine by returning 4 records, but obviously I want to use some related data from the book table:

 $bookmarks = Bookmark::model()->findAll(array( "with" => "book", "condition" => "t.user_id = :user_id AND t.page_id IS NULL", "params" => array(":user_id" => 1), )); 

and now I get 0 records (count ($ bookmarks) == 0), although the generated SQL statement selects all the necessary data, it simply is not recognized by the CActiveRecord class. Another strange thing: when I try to pick up all the bookmarks of the pages, everything is fine:

 $bookmarks = Bookmark::model()->findAll(array( "with" => "book", "condition" => "t.user_id = :user_id AND t.page_id IS NOT NULL", "params" => array(":user_id" => 1), )); 

What am I doing wrong? How to make the expression in the second example return some data? PHP 5.4.0, Yii 1.1.8, PostgreSQL 9.1.4, + 32 ° C outside.

Answer 1

Your problem can be solved as follows:

• Add a surrogate PK to the bookmark table (for example, automatically incremental sequence).

• Delete primaryKey() .

You can also use more convenient code:

 public function relations() { return array( //... 'wholeBook' => array(self::BELONGS_TO, 'Book', 'book_id', 'on'=>"page_id IS NULL", 'joinType'=>'INNER JOIN'), //... ); } 

Then in the controller it is simple:

 $bookmarks = Bookmark::model()->with('wholeBook')->findAllByAttributes(array('user_id'=>1)); 

In fact, with the UNIQUE key instead of PRIMARY and primaryKey() you use a hack to avoid the inability to use the NULL column in composite PKs. ActiveRecord is a kind of ORM, so any SQL logic must be converted to AR (Yii automatically loads DB schemas), and it must be the correct logic.

If I were you, I would normalize SQL to something like this:

Because of this, there are two different types of bookmarks with different relationships. You should join them only in the logic of vision, and not in the structure of relations. IMHO