应用左连接使用外键从多个表中提取数据
我有一个表 TblOrders,其中有两个字段,如 FldOrderStatusId 和 FldInstrumentID,分别作为 TblOrderStatus 和 TblInstrumentMasters 表中的外键。是否可以对表进行左连接。代码如下:
$find_filled_orders = $this->UserOrder->query(
"Select distinct(FldOrderNumber) from TblOrders where FldOrderStatusId =12 ");
$res_order="";
$i=0;
foreach($find_filled_orders as $order_arr)
{
if($i!=0)
{
$res_order.=",";
}
$res_order.="'".$order_arr['TblOrders']['FldOrderNumber']."'";
$i++;
}
$where_not_in="";
if($i>=1)
{
$where_not_in = "AND FldOrderNumber NOT IN (".$res_order.")";
}
//debugbreak();
$current_order = $this->UserOrder->query(
"Select * from TblOrders where 1 ".$where_not_in.
" group by FldOrderNumber order by FldSlNo desc");
我想在最后一行查询应用左连接。请大家帮帮我。 提前致谢。
I have a table TblOrders which have two fields like FldOrderStatusId and FldInstrumentID as a foreign key from the tables called TblOrderStatus and TblInstrumentMasters respectively. Is that possible to left join for the table. The code is given below:
$find_filled_orders = $this->UserOrder->query(
"Select distinct(FldOrderNumber) from TblOrders where FldOrderStatusId =12 ");
$res_order="";
$i=0;
foreach($find_filled_orders as $order_arr)
{
if($i!=0)
{
$res_order.=",";
}
$res_order.="'".$order_arr['TblOrders']['FldOrderNumber']."'";
$i++;
}
$where_not_in="";
if($i>=1)
{
$where_not_in = "AND FldOrderNumber NOT IN (".$res_order.")";
}
//debugbreak();
$current_order = $this->UserOrder->query(
"Select * from TblOrders where 1 ".$where_not_in.
" group by FldOrderNumber order by FldSlNo desc");
I want to apply left join at the last line query. Please guys help me out.
Thanks in advance.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
你确定你用的是蛋糕吗? O_o 如此简单的查询不需要原始 sql。如果您的表和键名称遵循 Cake 的约定,Cake 会为您完成这一切,只需在控制器中使用几个链接方法。
我什至不太确定您想要完成什么,但我认为您只是想获取用户所有未履行的工具订单的所有状态,或者其他什么?
在 Cake 中完全不需要手动构造查询,更不用说像这样准备字符串了。让框架来完成繁重的工作。如果需要,$find_filled_orders 可以编写为嵌套 SELECT 语句(Cake 为其提供了优雅的支持)。否则,您所需要做的就是重新调整您的表格/模型,以便您可以正确关联它们,然后让 Cake 完成剩下的工作。
您没有展示您想要如何调用工具,所以我不能更具体。但是,如果您定义了关联模型并正确命名了所有内容,Cake 将自动生成关联模型的连接。通过在订单中添加名为 user_id 的列来暗示订单和用户之间的关系。与 Instruments_id 和 status_id 相同,如果需要,对于表 Users - order_id 等也是如此。Cake“外键”列实际上不必在数据库中定义外键约束,甚至不必包含任何数据。
(组和 DISTINCT ——在现实生活中,不需要两者。我将它们都放入是为了演示。)
http://book.cakephp.org/view/1002/Creating-Database-Tables
http://book.cakephp.org/view/1030/Complex-Find-Conditions
HTH。 :)
Are you sure you're using Cake? O_o There's no need for raw sql for such a simple query. If your tables and key names followed Cake's conventions, Cake would do it all for you with just a couple chained methods in your controller.
I'm not even really sure what you're trying to accomplish, but I think you just want to pull all the status of all of a user's unfilled instrument orders, or something?
There is exactly 0 need to manually construct queries in Cake, let alone prep strings like that. Let the framework do the heavy lifting. If needed, $find_filled_orders could be written as a nested SELECT statement (for which Cake provides elegant support). Otherwise, all you need is to retool your tables / models so you can associate them properly and let Cake do the rest.
You don't show how you want to call instruments, so I can't be more specific. But Cake will automatically generate joins for associated models if you define them and name everything properly. Imply a relationship between Orders and Users by adding a column in Orders called user_id. Same for instruments_id and status_id, and if desired, for table Users - order_id, etc. Cake "foreign key" columns don't have to actually be defined foreign key constrained in the db, or even contain any data. Make them all null if it pleases you.
(Both group and DISTINCT -- in real life, no need for both. I put them both in for the sake of demonstrating.)
http://book.cakephp.org/view/1002/Creating-Database-Tables
http://book.cakephp.org/view/1030/Complex-Find-Conditions
HTH. :)