查询生成器Codeigniter 3 [英] Query Builder Codeigniter 3

问题描述

我是这个mysql查询

I this mysql query

SELECT project.title project_title, category.category, project_details.description description, SUM(project_details.quantity) total_quantity, project_details.unit, SUM(project_details.price) total_price, SUM(project_details.jan) total_jan, SUM(project_details.feb) total_feb, SUM(project_details.mar) total_mar, SUM(project_details.apr) total_apr, SUM(project_details.may) total_may, SUM(project_details.jun) total_jun, SUM(project_details.jul) total_jul, SUM(project_details.aug) total_aug, SUM(project_details.sep) total_sep, SUM(project_details.oct) total_oct, SUM(project_details.nov) total_nov, SUM(project_details.dec) total_dec
FROM project_details
JOIN project ON project_details.project_id = project.id
JOIN category ON project_details.category_id = category.id
JOIN user ON project.user_id = user.id
RIGHT JOIN office ON user.office_id = office.id
WHERE EXISTS 
    (
        SELECT t1.id id1, t2.id id2, t3.id id3, t4.id id4, t5.id id5
        FROM user t1
        LEFT JOIN user t2 ON t2.reports_to = t1.id
        LEFT JOIN user t3 ON t3.reports_to = t2.id
        LEFT JOIN user t4 ON t4.reports_to = t3.id
        LEFT JOIN user t5 ON t5.reports_to = t4.id
        WHERE t1.id = 81
    )
    AND project.approval_status = 22 AND project.reason_for_rejection IS NULL
GROUP BY project.id, project_details.description
ORDER BY project_details.category_id

但是我在Codeigniter 3.0中找不到任何文档，在这里我可以找到存在的地方。我已经检查过此 https://www.codeigniter.com/userguide3/database/query_builder.html 但没有。

However I cannot find any documentation in Codeigniter 3.0 where I can find WHERE EXISTS. I already checked this https://www.codeigniter.com/userguide3/database/query_builder.html but there are non.

这是我现在的代码

$this->db->select('project.title project_title, category.category, project_details.description description, SUM(project_details.quantity) total_quantity, project_details.unit, SUM(project_details.price) total_price, SUM(project_details.jan) total_jan, SUM(project_details.feb) total_feb, SUM(project_details.mar) total_mar, SUM(project_details.apr) total_apr, SUM(project_details.may) total_may, SUM(project_details.jun) total_jun, SUM(project_details.jul) total_jul, SUM(project_details.aug) total_aug, SUM(project_details.sep) total_sep, SUM(project_details.oct) total_oct, SUM(project_details.nov) total_nov, SUM(project_details.dec) total_dec');
$this->db->from('project_details');
$this->db->join('project', 'project_details.project_id = project.id');
$this->db->join('category', 'project_details.category_id = category.id');
$this->db->join('user', 'project.user_id = user.id');
$this->db->join('office', 'user.office_id = office.id', 'right');
// THIS IS WHERE I WANT TO PUT THE WHERE EXISTS
$this->db->where('project.approval_status', $user_id);
$this->db->where('project.reason_for_rejection IS NULL', NULL, TRUE);
$this->db->group_by('project.id');
$this->db->group_by('project_details.description');
$this->db->order_by('project_details.category_id');
$query = $this->db->get();
return $query->result();

有人可以帮我如何在codeigniter中实现吗？

Can someone please help how can I achieve this in codeigniter? Advanced thanks.

推荐答案

当您有非常大的查询时，或者在查询构建器上没有内置函数用于您可以始终使用以下目的：

When you have a really large query, or the is no built-in function on the query builder made for your purpose you can always use:

$query = $this->db->query('your large query');

您还应该注意注入情况，因此也应该阅读以下发布

You also should be aware of the injections so you should also read this post

这很好地解释了如何使用 $ this-> db-> query 方法来防止SQL注入。

This explains pretty well how to use $this->db->query method against sql injections.

希望这会有所帮助

这篇关于查询生成器Codeigniter 3的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！