我有四张桌子如下所示
人数：
------------------------------------------------------------------------------------------------ | list_id |user_id | name | category | fees | details |created_on | ------------------------------------------------------------------------------------------------ | 90cc57a4-f782-4c57-ac98-1965c57ece57 |user 100 |satwik| music | 500 | dummy |2015-08-02 | ------------------------------------------------------------------------------------------------
将我的list_id从随机字符串更改为UUID.
from this comment on php.net
see this stackoverflow question
在列表表中,list_id是主键,我知道使用autoincreament是最好的,但我的要求是这样的.而s.no是休息表的主要关键.
我需要从PHP端生成一个随机密钥,因为在会话中设置list_id,并避免在会话中设置list_id的另一个查询.
喜欢：
---------------------------------------------------------------- |.sno | list_id | user_id | likes | ---------------------------------------------------------------- | 1 | 90cc57a4-f782-4c57-ac98-1965c57ece57 | user110 | 1 | ---------------------------------------------------------------- | 2 | 90cc57a4-f782-4c57-ac98-1965c57ece57 | user215 | 1 | ---------------------------------------------------------------- | 3 | 90cc57a4-f782-4c57-ac98-1965c57ece57 | user200 | 1 | ----------------------------------------------------------------
注释：
------------------------------------------------------------------------- |.sno | user_id | list_id | comment | ------------------------------------------------------------------------- | 1 | user 205| 90cc57a4-f782-4c57-ac98-1965c57ece57 | dummy comment | -------------------------------------------------------------------------
浏览次数：
---------------------------------------------------------------- |.sno | list_id | user_id | views | ---------------------------------------------------------------- | 1 | 90cc57a4-f782-4c57-ac98-1965c57ece57 | user110 | 2 | ---------------------------------------------------------------- | 2 | 90cc57a4-f782-4c57-ac98-1965c57ece57 | user215 | 1 | ---------------------------------------------------------------- | 3 | 90cc57a4-f782-4c57-ac98-1965c57ece57 | user200 | 1 | ----------------------------------------------------------------
我正在尝试从列表表中获取用户id user100的列表
并且需要从多个表中获取数量的视图,例如用户的列表ID的注释.
我尝试使用这个来自codeigniter的查询
$this->db->select ( 'list.*,count(v.views) as views,count(l.likes) as likes,count(c.comment) as comments' )
->from ( 'listings as list' )
->join ( 'views v','v.list_id = list.list_id')
->join ( 'likes l','l.list_id = list.list_id')
->join ( 'comments c','c.list_id = list.list_id');
$this->db->where ( 'list.user_id',$user_id);
$query = $this->db->get ();
我错了意见,喜欢和评论数.
这是数据库设计好还是我需要改变任何东西.我在使用联接功能方面没有什么问题,请帮助我.
编辑：
我从下面的答案中尝试过这个查询
$this->db->select ( 'l.*,count(distinct v.s_no) as views,count(distinct li.s_no) as likes,count(distinct c.s_no) as comments',false)
->from ( 'listings as l' )
->join ( 'likes li','l.list_id = li.list_id')
->join ( 'comments c','l.list_id = c.list_id')
->join ( 'views v','l.list_id = v.list_id')
->where ( 'l.user_id',$id);
我得到了我想要的,但这种查询方式失败(返回null),如果我没有任何意见或意见或喜欢.
如果user_id对于喜欢,评论或视图不是唯一的,那么在进行多个联接时,最终会出现交叉产品,这将增加您的计数.因为您只是查询一个user_id,所以子查询可能是最好的方式.
记住设置第二个参数来选择为false,所以代码号不会尝试转义子查询.
$this->db->select ( 'list.*,(select count(*) from views v where v.user_id = list.user_id) as views,(select count(*) from likes l where l.user_id = list.user_id) as likes,(select count(*) from comments c where c.user_id = list.user_id) as comments',false)->from ( 'listings as list' ); $this->db->where ( 'list.user_id',$user_id); $query = $this->db->get ();