php - codeigniter 从第二个表 ID 与第三个表 ID 多行连接

Question

我想连接多个表，如图:

这是我的代码:

$this->db->select('
  pt2.turl as `p_img`,
  p.title as `p_title`,
  p.text as `p_text`,
  p.create as `p_date`,
  pt3.turl as `c_img`,
  u.name as `c_name`,
  c.text as `c_text`,      
  c.create as `c_date`
');

$this->db->from('posts as p, users as u, photos as pt2, photos as pt3');    
$this->db->join('comments as c', 'p.id=c.pid AND u.id=c.uid');
$this->db->join('posts as p2', 'p2.pid=pt2.id', 'rihgt');
$this->db->join('users as u2', 'u2.photoid=pt3.id', 'right');
$this->db->order_by('c.id', 'DESC');
$this->db->limit('7');
$qry = $this->db->get();
return $qry->result();

Answer 1

如果我没听错的话，这就是您要找的东西。我还没有尝试过，所以它可能不准确，但您不需要为连接中的同一个表创建 2 个表关联(pt2 和 pt3)。只需将它们包含在选择中并加入唯一 ID 即可

“左”是一个以您的左表为中心的联接，因此所有内容都卡在上面。由于您在照片表之前加入用户表，因此您应该能够加入其列。

希望这对您有所帮助。如果我错过了什么，请告诉我。 :)

$select = array(
    pt2.turl as `p_img`,
    p.title as `p_title`,
    p.text as `p_text`,
    p.create as `p_date`,
    pt2.turl as `c_img`,
    u.name as `c_name`,
    c.text as `c_text`,      
    c.create as `c_date`
);

//Set tables to variables. Just makes it easier for me
$postsTable = "posts as p"; //This will be your left table. 
$userTable = "Users as u";
$commentsTable = "comments as c";
$photosTable = "photos as pt2";

$this
    ->db
        ->select($select)
        ->from($postsTable)
            ->join($userTable, "p.uid = u.id", "left")
            ->join($commentsTable, "p.cid = c.id", "left")
            ->join($photosTable, "u.photoid = pt2.id", "left")
        ->get();