Rails里的多对多关系声明极其简单,一句 has_and_belongs_to_many :projects 就可表示当前表通过一个中间表来拥有多个projects,唯一的约定就是复数的表名和_id后缀的外键。

但是如果你接手的数据库是在别的不同约定的语言框架里设计的,这样去套用Rails的 has_and_belongs_to_many 就炕爹了,你得一个一个去声明每一个选项,以下就是本人惨痛的经历,其他人看了就不用重蹈覆辙了。

参考:http://guides.rubyonrails.org/association_basics.html ,和本地的Rails rdoc文档

需求是查出一个用户收藏的所有代码。

先来看下表结构。用户,收藏,代码三个表结构主要部分如下:

 1 CREATE TABLE `common_member` (
 2   `uid` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
 3   PRIMARY KEY (`uid`),
 4 ) ENGINE=MyISAM AUTO_INCREMENT=802058 DEFAULT CHARSET=utf8;
 5 
 6 CREATE TABLE `common_user_favorite` (
 7   `itemid` int(11) NOT NULL,
 8   `uid` int(11) NOT NULL,
 9   `model` enum('blog','code','news','book') NOT NULL,
10   `create_time` int(11) NOT NULL,
11   `is_delete` tinyint(1) NOT NULL DEFAULT '0'
12 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
13 
14 CREATE TABLE `code_gists` (
15   `id` int(11) NOT NULL AUTO_INCREMENT,
16   `user_id` int(11) DEFAULT NULL,
17   `created_at` datetime NOT NULL,
18   `updated_at` datetime NOT NULL,
19   `deleted_at` tinyint(1) DEFAULT NULL,
20   PRIMARY KEY (`id`),
21   KEY `index_code_gists_on_deleted_at_and_user_id_and_updated_at` (`deleted_at`,`user_id`,`updated_at`)
22 ) ENGINE=InnoDB AUTO_INCREMENT=149 DEFAULT CHARSET=utf8;

我们接下来的代码逻辑就是查找出目标用户,通过收藏表,来找出该用户的所有代码。

先声明三个model如下:

1 class CommonMember < ActiveRecord::Base
2   self.table_name = :common_member
3 end
4 class CommonUserFavorite < ActiveRecord::Base
5   self.table_name = :common_user_favorite
6 end
7 class CodeGist < ActiveRecord::Base
8 end

接着声明三个model之间的关系

 1 CommonUserFavoriteConditions = "`common_user_favorite`.`is_delete` = 0 AND `common_user_favorite`.`model` = 'code'"
 2 class CommonMember < ActiveRecord::Base
 3   has_many :fav_gists, :through => :favs, :order => "`common_user_favorite`.`create_time` DESC", :source => :gist
 4   has_many :favs, :class_name => CommonUserFavorite, :foreign_key => :uid, :conditions => CommonUserFavoriteConditions
 5 end
 6 class CommonUserFavorite < ActiveRecord::Base
 7   belongs_to :gist, :class_name => CodeGist, :foreign_key => :itemid
 8 end
 9 class CodeGist < ActiveRecord::Base
10   has_many :favs, :class_name => CommonUserFavorite, :foreign_key => :itemid, :conditions => CommonUserFavoriteConditions
11 end

以下是解释

先声明收藏表(CommonUserFavorite) belongs_to 代码表(CodeGist),指定 收藏表(CommonUserFavorite) 的外键是:itemid,关联的代码表(CodeGist)的主键是:id。示例如: CommonUserFavorite.where(:model => 'code').first.gist

1 SELECT `common_user_favorite`.*
2         FROM `common_user_favorite`
3         WHERE `common_user_favorite`.`model` = 'code'
4         LIMIT 1;
5 
6 SELECT `code_gists`.*
7         FROM `code_gists`
8         WHERE `code_gists`.`id` = 43 AND (`code_gists`.`deleted_at` IS NULL)
9         LIMIT 1;

再声明代码表(CodeGist) has_many 收藏表(CommonUserFavorite) ,指定 收藏表(CommonUserFavorite) 的外键是 :itemid,且查询条件是 :conditions => ["common_user_favorite.model = 'code'"] 。示例如: CodeGist.last.favs

 1 SELECT `code_gists`.*
 2         FROM `code_gists`
 3         WHERE (`code_gists`.`deleted_at` IS NULL)
 4         ORDER BY `code_gists`.`id` DESC
 5         LIMIT 1;
 6 
 7 SELECT `common_user_favorite`.*
 8         FROM `common_user_favorite`
 9         WHERE `common_user_favorite`.`itemid` = 107
10                 AND (`common_user_favorite`.`is_delete` = 0
11                 AND `common_user_favorite`.`model` = 'code');

最后声明是用户表(CommonMember)对代码表(CodeGist)的has_many 是通过 用户表(CommonMember)对收藏表(CommonUserFavorite)的has_many 和 收藏表(CommonUserFavorite)对代码表(CodeGist)的belongs_to 共同实现的,这两个声明关系分别表述为 :through => :favs:source => :gist。各自的示例如:

cm = CommonMember.where(:uid => 470700).first

1 SELECT `common_member`.*
2         FROM `common_member`
3         WHERE `common_member`.`uid` = 470700
4         LIMIT 1;

cm.fav_gists

1 SELECT `code_gists`.*
2         FROM `code_gists`
3         INNER JOIN `common_user_favorite`
4               ON `code_gists`.`id` = `common_user_favorite`.`itemid`
5         WHERE `common_user_favorite`.`uid` = 470700
6               AND (`code_gists`.`deleted_at` IS NULL)
7               AND (`common_user_favorite`.`is_delete` = 0
8               AND `common_user_favorite`.`model` = 'code')
9         ORDER BY `common_user_favorite`.`create_time` DESC;