问题描述
我正在研究 search
功能.我创建了一个搜索表单,用户可以在其中根据 Type
,ope
& 搜索应用程序.格式化
.
我在我的 join 查询中使用了 subquery 以获得所需的结果.我已经在 MySQL Workbench
中测试了我的查询,并且它工作正常.
但是当我使用查询构建器技术在 Codeigniter 中尝试相同的查询时,我遇到了一个问题.
这是在工作台中运行良好的查询:
SELECT (*)从`应用程序`左加入`App_type`ON `App_type`.`app_id` = `App`.`id`左加入`App_formate`ON `App_formate`.`app_id` = `App`.`id`WHERE `App`.`id` IN(select app_id FROM App_type WHERE type_id in (3,2,6) group by app_id HAVING COUNT(*) = 3)AND `App_formate`.`formate_id` IN('1', '3')AND `jobs`.`ope_min` <='3'AND `jobs`.`ope_max` >='3'GROUP BY `jobs`.`id`;
这是我使用的连接查询:
$subquery = "select app_id FROM App_type WHERE type_id in ($selected_type) group by app_id HAVING COUNT(*) = $type_count";$search_app_query = $this->db-> 选择('*')-> from('应用程序')->join('App_type', 'App_type.app_id = App.id', '左外')->join('App_formate', 'App_formate.app_id = App.id', '左外')->where_in('App.id',$subquery)//<-- 问题来了->where_in('App_format.format_id',$data['selected_formates'])->where('App.ope_min <=',$data['ope_value'])->where('App.ope_max >=',$data['ope_value'])->group_by("App.id", "desc")->get();
当我调试这个问题时,它显示了
我发现问题出在查询的这一部分:"WHERE `App`.`id` IN('选择 app_id发件人 App_typeWHERE type_id in (3,2,6)按 app_id 分组 HAVING COUNT(*) = 3')"
这个子查询中的单引号造成了问题.
到目前为止我尝试过的:
要删除我尝试过的这个单引号
REPLACE($subquery, '''', '')
->where_in('App.id',trim($subquery,"'"))
$subquery_improved = substr($subquery, 1, -1);
但是所有这些解决方案都不起作用.他们没有删除单引号.
注意:我知道 $this->db->query()
但不想使用它.
你的任务看起来很简单
代替
->where_in('App.id',$subquery)//<-- 问题来了
你可以试试下面的
->where("App.id IN (".$subquery.")",NULL, false)
您可以在 Codeigniter 文档中找到此确切信息 此处(第 4 点和以下部分).
I am working on a search
functionality.I have created a search form in which user can search an Application based on Type
,ope
& Formate
.
I have used a subquery in my join query to get the desired result. I have tested my query in MySQL Workbench
nad it's working fine.
But when I tried that same query in Codeigniter using query builder technique then I am facing a problem.
Here is the query which is worked fine in workbench:
SELECT (*)
FROM `App`
LEFT JOIN `App_type`
ON `App_type`.`app_id` = `App`.`id`
LEFT JOIN `App_formate`
ON `App_formate`.`app_id` = `App`.`id`
WHERE `App`.`id` IN(select app_id FROM App_type WHERE type_id in (3,2,6) group by app_id HAVING COUNT(*) = 3)
AND `App_formate`.`formate_id` IN('1', '3')
AND `jobs`.`ope_min` <= '3'
AND `jobs`.`ope_max` >= '3'
GROUP BY `jobs`.`id`;
This is the join query which I use:
$subquery = "select app_id FROM App_type WHERE type_id in ($selected_type) group by app_id HAVING COUNT(*) = $type_count";
$search_app_query = $this->db
->select('*')
->from('App')
->join('App_type', 'App_type.app_id = App.id', 'left outer')
->join('App_formate', 'App_formate.app_id = App.id', 'left outer')
->where_in('App.id',$subquery) //<-- Here is the problem
->where_in('App_formate.formate_id',$data['selected_formates'])
->where('App.ope_min <=',$data['ope_value'])
->where('App.ope_max >=',$data['ope_value'])
->group_by("App.id", "desc")
->get();
While I am debugging this problem it shows the
I have found the problem is in this part of the query:
"WHERE `App`.`id` IN('select app_id
FROM App_type
WHERE type_id in (3,2,6)
group by app_id HAVING COUNT(*) = 3')"
that single quote in this subquery is creating a problem.
What I have tried so far:
To remove this single quote I have tried
REPLACE($subquery, '''', '')
->where_in('App.id',trim($subquery,"'"))
$subquery_improved = substr($subquery, 1, -1);
But all this solution is not working. They are not removing the single quote.
Note: I am aware of $this->db->query()
but do not want to use that.
Your task looks pretty simple
instead of
->where_in('App.id',$subquery) //<-- Here is the problem
you can try the following
->where("App.id IN (".$subquery.")",NULL, false)
这篇关于从 codeigniter 中的 where_in 中删除单引号的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!