我有两个表:
events event_follows
------- -------------
id id
title user_id
content event_id
user_id
我需要获取特定user_id的所有事件,事件计数,获取该用户所关注的所有事件以及所有事件和事件计数。真的,我不知道如何在1个查询中正确执行此操作。
根据你的问题,我只能想到这个解决方案:
$query = "select e.*, count(e.*), ef.*, count(ef.*) from events e join event_follows ef on ef.user_id = e.user_id where e.user_id = '$my_id' ";
$data['info'] = $this->db->query($query)->result_array();
//Then, you can use php's echo "<pre>", print_r() and die() functions
//to test the result:
echo "<pre>";
print_r(data['info']);
die();
我已经制定了一些解决方案。但不幸的是,它比1个查询更复杂:
// Fetch all events which was joined by user
$this->db->select('event_id')
->from('event_follows')
->where('user_id', $id);
$query1 = $this->db->get();
$result = $query1->result_array();
// Drop keys
$joined_events = array();
foreach($result as $row)
{
array_push($joined_events, $row["event_id"]);
}
// Fetch all events
$this->db->select('events.*, COUNT(event_follows.id) as followers')
->from('events')
->where('events.user_id', $id)
->or_where_in('events.id', $joined_events);
$this->db->join('event_follows', 'event_follows.event_id = events.id', 'left')
->group_by('event_follows.event_id');
$query2 = $this->db->get();