现在有这样一个需求,表1 topics 与 表2 comments 一对多关联,比如一个帖子包含用户的多个评论
表结构如下:
Table topics:
+----+----------+------------------------------------+
| id | name | content
+----+----------+------------------------------------+
Table comments
+----+----------+---------------+-------------------+
| id | name | topic_id | user_id
+----+----------+---------------+--------------------+
我现在想查出一个用户参与评论的topics,首先想到用distinct,而MySQL对distinct的支持不好,必须只有这样使用:
mysql> select * , count(distinct user_id) from comments where user_id= 1 group by user_id;
+----+----------+------------------------------------------------------------------------------+
| id | name | topic_id | user_id | count(distinct user_register_id)
+----+----------+------------------------------------------------------------------------------+
| 1 | aa | 1 | 1 | 1
+----+----------+------------------------------------------------------------------------------+
1 row in set (0.00 sec)
这样在MySQL中是将结果正确得出,但是还没结束,我需要将这个结果映射给Rails的ActiveRecord,因为查出的结果多了一个字段 count(distinct user_register_id),结果AR不认识了,查出来的结果为空,我晕!修改一下
mysql> select * , count(distinct user_id) as name from comments where user_id= 1 group by user_id;
+----+----------+------------------------------------------------------------------------------+
| id | name | topic_id | user_id | name
+----+----------+------------------------------------------------------------------------------+
| 1 | aa | 1 | 1 | 1
+----+----------+------------------------------------------------------------------------------+
1 row in set (0.00 sec)
问题就是这样了,就是采用MySQL的distinct以后怎么去做ORMapping?难道我要添加一个字段?或者我这个需求有没有其他的解法?比如用left join,但是我不知道怎么在left join的时候去掉相同的字段。或者得到用户评论的topics可以使用其他方法后可以得到不重复的记录? 请指教
表结构如下:
Table topics:
+----+----------+------------------------------------+
| id | name | content
+----+----------+------------------------------------+
Table comments
+----+----------+---------------+-------------------+
| id | name | topic_id | user_id
+----+----------+---------------+--------------------+
我现在想查出一个用户参与评论的topics,首先想到用distinct,而MySQL对distinct的支持不好,必须只有这样使用:
mysql> select * , count(distinct user_id) from comments where user_id= 1 group by user_id;
+----+----------+------------------------------------------------------------------------------+
| id | name | topic_id | user_id | count(distinct user_register_id)
+----+----------+------------------------------------------------------------------------------+
| 1 | aa | 1 | 1 | 1
+----+----------+------------------------------------------------------------------------------+
1 row in set (0.00 sec)
这样在MySQL中是将结果正确得出,但是还没结束,我需要将这个结果映射给Rails的ActiveRecord,因为查出的结果多了一个字段 count(distinct user_register_id),结果AR不认识了,查出来的结果为空,我晕!修改一下
mysql> select * , count(distinct user_id) as name from comments where user_id= 1 group by user_id;
+----+----------+------------------------------------------------------------------------------+
| id | name | topic_id | user_id | name
+----+----------+------------------------------------------------------------------------------+
| 1 | aa | 1 | 1 | 1
+----+----------+------------------------------------------------------------------------------+
1 row in set (0.00 sec)
问题就是这样了,就是采用MySQL的distinct以后怎么去做ORMapping?难道我要添加一个字段?或者我这个需求有没有其他的解法?比如用left join,但是我不知道怎么在left join的时候去掉相同的字段。或者得到用户评论的topics可以使用其他方法后可以得到不重复的记录? 请指教