多对多,三表联查: 查主表,LEFT JOIN关系表,再LEFT JOIN第三表,最后按关系表中的“主表ID”进行 GROUP BY。
GROUP_CONCAT(groups.name SEPARATOR ' | ') AS name 进行重复项合并。 例如: users:主表 users_groups:关系表 groups:第三表(可根据实际需要 变成“主表”) $this->db->select("users.*, GROUP_CONCAT(groups.name SEPARATOR ' | ') AS name "); $this->db->from('users'); $this->db->join('users_groups','users.id = users_groups.user_id','LEFT'); $this->db->join('groups','users_groups.group_id = groups.id','LEFT'); $this->db->group_by('users_groups.user_id'); return $this->db->get();
用sql就是 $sql = "SELECT us.* ,GROUP_CONCAT(gs.name SEPARATOR ' | ') AS name FROM users us LEFT JOIN users_groups ug ON us.id = ug.user_id LEFT JOIN groups gs ON ug.group_id = gs.id GROUP BY ug.user_id "; return $this->db->query($sql);