如何将SQL联合转换为代码点火器方式

Question

如何使用 PHP CodeIgniter 框架进行 UNION 查询 我在执行此查询时收到错误

Every derived table must have its own alias

型

function search_blog($title){
$query = $this->db->query("SELECT * FROM (
SELECT id,section,title,img,year,lastupdateon AS ondate,'moves' AS dept,STATUS FROM movies
UNION
SELECT ID,section,Title,img,year,lastupdateon AS ondate,'kid' AS dept,STATUS FROM kids
UNION
SELECT ID,views,Title,img,version AS year,ondate,'software' AS dept,STATUS FROM soft
UNION
SELECT ID,section,Title,img,'PC Game' AS year,ondate,'game' AS dept,STATUS FROM games
UNION
SELECT ID,season,Title,img,type AS year,lastupdatedon AS ondate,'tvshow' AS dept,STATUS FROM tvshows
)");
$this->db->like('Title', $title , 'after');
$this->db->order_by('ondate', 'DESC');
$this->db->limit(10);
return $this->db->get($query)->result();
}

如何更改它

Answer 1

您只需为每个表添加查询并使用像这样的联合来连接它们

$this->db->select('ID,section,title,img,year,lastupdateon AS ondate,`moves` AS dept,STATUS');
$this->db->from('movies');
$query1 = $this->db->get_compiled_select();
$this->db->select('ID,section,title,img,year,lastupdateon AS ondate,`kid` AS dept,STATUS');
$this->db->from('kids');
$query2 = $this->db->get_compiled_select();
$this->db->select('ID,views,title,img,version AS year,ondate,`software` AS dept,STATUS');
$this->db->from('soft');
$query3 = $this->db->get_compiled_select();

$this->db->select('ID,section,title,img,'PC Game' AS year,ondate,`game` AS dept,STATUS');
$this->db->from('games');
$query4 = $this->db->get_compiled_select();
$this->db->select('ID,season,title,img,type AS year,lastupdatedon AS ondate,`tvshow` AS dept,STATUS');
$this->db->from('tvshows');
$query5 = $this->db->get_compiled_select();
$this->db->like('title', $title , 'after');
$this->db->order_by('ondate', 'DESC');
$this->db->limit(10);

$query = $this->db->query($query1 . ' UNION ' . $query2. ' UNION ' . $query3. ' UNION ' . $query4. ' UNION ' . $query5);
return $this->db->get($query)->result();