我有一个有点像社交网络的日志项目，让用户输入他们一整天所做的任何活动。
在我的数据库上，我有一个'日志'表，其中有以下字段:
| log_id | log_content | log_date | log_author |
我的问题是，我怎么能做一个查询，将获取前n天，然后在网格视图中显示其结果，像这样:
| Monday, December 7, 2013
| log_content, log_author, 6 AM | log_content, log_author, 8:30 AM | ...|
| Tuesday, December 8, 2013
| log_content, log_author, 7:10 AM | log_content, log_author, 12:00 NN | log...|
| Wednesday, December 9, 2013
| log_content, log_author, 6:30 PM |
| Thursday, December 10, 2013
| log_content, log_author, 9:53 AM | log_content, log_author, 4:02 PM | ...|
| Friday, December 11, 2013
| log_content, log_author, 1:34 PM |
我知道我不能对整个'logs'表进行查询，因为当日志增长时会导致瓶颈。我正在考虑做一个比特查询，当用户向下滚动时，另一个后台查询将运行下一组要显示的数据(使用限制和偏移)。
目前我的查询是这样的:
function fetch_logs($group_id, $limit, $offset){
$this->db->select('l.log_id, l.log_date, l.log_content, u.username');
$this->db->from('logs AS l');
$this->db->join('log_group_ref AS r', 'l.log_id = r.log_id');
$this->db->join('users AS u', 'l.log_author = u.user_id');
$this->db->where('r.group_id', $group_id);
$this->db->limit($limit, $offset);
return $this->db->get()->result_array();
}
但是我不知道如何将查询的数据按天分组，并将它们放入网格中。
我使用Twitter的Bootstrap与网格，所以我可以把每个日志放到一个网格中，然后添加滚动条，如果有更多的日志。
更新:所以我根据raheel shan的第二种方法更新了我的代码，但我似乎无法得到我想要的数据，直到我添加:
$this->db->group_by(l.log_date);
基于Exander的代码，获取正确的数据集，尽管格式与raheel shan的不同。
在这两个查询中，这是我更新的查询:
$select = array(
"DATE_FORMAT(l.log_date,'%a , %b %d , %Y') AS LogDate",
"GROUP_CONCAT(l.log_id) AS LogIDs",
"GROUP_CONCAT(l.log_content) AS LogContents",
"GROUP_CONCAT(u.username) AS LogAuthors",
"GROUP_CONCAT(DATE_FORMAT(l.log_date,'%h:%i %p')) AS LogTimes"
);
$this->db->select($select);
$this->db->from('logs AS l');
$this->db->join('log_group_ref AS r', 'l.log_id = r.log_id');
$this->db->join('users AS u', 'l.log_author = u.user_id');
$this->db->where('r.group_id', $group_id);
$this->db->group_by('l.log_date');
$this->db->limit($limit, $offset);
return $this->db->get()->result_array();
现在，我将不得不处理每个LogContents, LogAuthors, LogTimes并将它们分组回数组。我需要重新研究一下raheel shan的代码。
更新:我终于得到了实现这一目标所需的所有东西。因为我只能接受一个，所以我会把raheel shan的标记为接受。虽然Exander's也是正确的，加上更少的代码，使它更棒。
我不完全确定，但这个查询似乎能够得到结果，你需要没有任何子查询:
SELECT
DATE_FORMAT(l.log_date,'%W , %M %d , %Y') AS day,
GROUP_CONCAT(l.log_content, ', ', u.username, ', ', DATE_FORMAT(l.log_date, '%l:%i %p') ORDER BY l.log_date SEPARATOR ' | ') AS activity
FROM logs as l
INNER JOIN users u ON u.user_id = l.log_author
INNER JOIN log_group_ref r ON r.log_id = l.log_id
WHERE r.group_id = **<YOUR_GROUP_ID>**
GROUP BY DATE(l.log_date) ASC;
您可以在这里看到示例(部分从raheel shan的示例中复制和粘贴)。
您可以这样做。查看Demo
Here
SELECT
DATE_FORMAT(l.log_date,'%a , %b %d , %Y') as LogDate,
DATE(l.log_date) as LDate,
GROUP_CONCAT(r.data) as LogData
FROM logs as l
INNER JOIN (
SELECT
logs.log_date,
GROUP_CONCAT(other) as data
FROM logs
LEFT JOIN (
SELECT
log_date,
CONCAT(log_content, ' | ', log_author , ' | ' , DATE_FORMAT(log_date,'%h:%i %p')) as other
FROM logs
GROUP BY log_date
) as t ON t.log_date = logs.log_date
GROUP BY log_date
) as r ON r.log_date = l.log_date
GROUP BY LDate
和Codeigniter
$query =" SELECT
DATE_FORMAT(l.log_date,'%a , %b %d , %Y') as LogDate,
DATE(l.log_date) as LDate,
GROUP_CONCAT(r.data) as LogData
FROM logs as l
INNER JOIN (
SELECT
logs.log_date,
GROUP_CONCAT(other) as data
FROM logs
LEFT JOIN (
SELECT
log_date,
CONCAT(log_content, ' | ', log_author , ' | ' , DATE_FORMAT(log_date,'%h:%i %p')) as other
FROM logs
GROUP BY log_date
) as t ON t.log_date = logs.log_date
GROUP BY log_date
) as r ON r.log_date = l.log_date
GROUP BY LDate";
$result = $this->db->query($query);
return $result->result();
左连接Group_Concat
Concat
DATE_FORMAT
编辑:
2方法
如果你想用PHP做，这很容易。你可以这样做。第一个模型函数
function fetch_logs($group_id, $limit, $offset)
{
$select = array(
"DATE_FORMAT(l.log_date,'%a , %b %d , %Y') as LogDate",
"GROUP_CONCAT(l.log_id) as LogIDs",
"GROUP_CONCAT(l.log_content) as LogContents",
"GROUP_CONCAT(l.log_author) as LogAuthors",
"GROUP_CONCAT(DATE_FORMAT(l.log_date,'%h:%i %p')) LogTimes"
);
$this->db->select($select);
$this->db->from('logs AS l');
$this->db->join('log_group_ref AS r', 'l.log_id = r.log_id');
$this->db->join('users AS u', 'l.log_author = u.user_id');
$this->db->where('r.group_id', $group_id);
$this->db->group_by(l.log_date);
$this->db->limit($limit, $offset);
return $this->db->get()->result_array();
}
And Now in Controller function
function getLogs()
{
$result = $this->yourmodel->fetch_logs();
$data = array();
$i = 0;
foreach($result as $row)
{
$logids = explode(',',$row['LogIDs']);
$logcontents = explode(',',$row['LogContents']);
$logauthors = explode(',',$row['LogAuthors']);
$logtimes = explode(',',$row['LogTimes']);
$counter = count($logids);
$test = array();
for($j=0;$j<$counter;$j++)
{
$test[] = $logcontents[$j] .','. $logauthors[$j] .','.$logtimes[$j];
}
$data[$i]['logdata'] = implode('|',$test);
$data[$i]['logdate'] = $row['LogDate'];
unset($test);
$i++;
}
$viewdata['data'] = $data;
$this->load->view('my_view',$viewdata);
}
你可以试试——
$this->db->select("l.log_id, date_format(l.log_date,'%W %M, %d %Y') D, l.log_content, u.username");
$this->db->from('logs AS l');
$this->db->join('log_group_ref AS r', 'l.log_id = r.log_id');
$this->db->join('users AS u', 'l.log_author = u.user_id');
$this->db->where('r.group_id', $group_id);
$this->db->order_by('l.log_date','desc');
$this->db->limit($limit);
return $this->db->get()->result_array();
我只是格式化了日期(l.log_date)并添加了order_by子句，以便它获取前N天的活动。