我需要转换为 codeigniter3 活动记录语法

Question

我需要联接主 (4-5( 表并从内部联接表中获取最新数据以获取项目当前状态。

投资者有很多投资

投资有很多investment_details

investment_details通过项目状态具有许多状态

Select 
    siv.company_name
    , siv.full_name
    , si.permit_number
    , si.project_name
    , sid.investment_detailed_id
    , sis.project_status_id
    , sps.project_status_name 
    From sma_investors siv 
        Join sma_investments si 
            On siv.investor_id = si.investment_id 
        Join sma_investment_details sid 
            On si.investment_id = sid.investment_id 
        Inner Join sma_investment_status sis 
            On sis.investment_status_id = (
                Select investment_status_id 
                From sma_investment_status s 
                Where s.investment_detailed_id = sid.investment_detailed_id 
                Order BY investment_status_id DESC LIMIT 1) 
        Join sma_project_status sps 
            On sis.project_status_id = sps.project_status_id

这工作正常，但我无法将其转换为 CI3。

Answer 1

使用查询生成器 (QB( 没有任何优势，除非您由于某些条件需要以不同的方式编写查询的某些部分。如果您希望自动转义用户输入，QB 也很有用。否则，您只需运行大量额外的代码，这些代码会导致您已经拥有的完全相同的 SQL 语句字符串。

在您的情况下，子选择将使转换更加难以完成，并添加大量要执行的额外代码。

我的建议是保持简单并使用db->query()，例如

$sql = "Select siv.company_name, siv.full_name, si.permit_number, si.project_name
    , sid.investment_detailed_id, sis.project_status_id, sps.project_status_name 
    From sma_investors siv 
        Join sma_investments si On siv.investor_id = si.investment_id 
        Join sma_investment_details sid On si.investment_id = sid.investment_id 
        Inner Join sma_investment_status sis On sis.investment_status_id = (
            Select investment_status_id From sma_investment_status s 
            Where s.investment_detailed_id = sid.investment_detailed_id 
            Order BY investment_status_id DESC LIMIT 1) 
        Join sma_project_status sps On sis.project_status_id = sps.project_status_id";
$query = $this->db->query($sql);
if(! $query)  // might be false if query fails
{
    return null;
}
return $query->row();  // one row of data

Answer 2

我终于想出了自己的解决方案并坚持 QB 溺爱标准，它按我预期工作

 $q = $this->db->select('*')
            ->from('investors')
            ->join('investments', `enter code here`'investors.investor_id=investments.investment_id')
            ->join('investment_details as sid', 'investments.investment_id=sid.investment_id')
            ->join('investment_status', 'investment_status.investment_status_id=(select '.$this->db->dbprefix('investment_status').'.investment_status_id from '.$this->db->dbprefix('investment_status').' where  '.$this->db->dbprefix('investment_status').'.investment_detailed_id=sid.investment_detailed_id order by  '.$this->db->dbprefix('investment_status').'.investment_status_id Desc limit 1)', 'inner')
            ->join('project_status', 'investment_status.project_status_id=project_status.project_status_id')
            ->where('sid.company_type_id', 1)
            ->order_by('investments.investment_id', 'desc')
            ->limit(5)
            ->get();