应用左连接使用外键从多个表中提取数据

发布于 2024-11-16 17:00:26 字数 846 浏览 2 评论 0原文

我有一个表 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 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

久而酒知 2024-11-23 17:00:26

你确定你用的是蛋糕吗? O_o 如此简单的查询不需要原始 sql。如果您的表和键名称遵循 Cake 的约定,Cake 会为您完成这一切,只需在控制器中使用几个链接方法。

我什至不太确定您想要完成什么,但我认为您只是想获取用户所有未履行的工具订单的所有状态,或者其他什么?

在 Cake 中完全不需要手动构造查询,更不用说像这样准备字符串了。让框架来完成繁重的工作。如果需要,$find_filled_orders 可以编写为嵌套 SELECT 语句(Cake 为其提供了优雅的支持)。否则,您所需要做的就是重新调整您的表格/模型,以便您可以正确关联它们,然后让 Cake 完成剩下的工作。

您没有展示您想要如何调用工具,所以我不能更具体。但是,如果您定义了关联模型并正确命名了所有内容,Cake 将自动生成关联模型的连接。通过在订单中添加名为 user_id 的列来暗示订单和用户之间的关系。与 Instruments_id 和 status_id 相同,如果需要,对于表 Users - order_id 等也是如此。Cake“外键”列实际上不必在数据库中定义外键约束,甚至不必包含任何数据。

$current_order = $this->Order->find('all', array(
    'conditions'=>array(
        'Order.status_id != 12',
        'Order.status_id'=>'Status.id', 
        'Order.instrument_id'=>'Instrument.id'
        ),
    'fields'=>array('DISTINCT (Order.number) as number', 'name', 'other'),
    'group'=> 'Order.number',
    'order'=>'Order.fld_sl_no DESC'
    )
);

(组和 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.

$current_order = $this->Order->find('all', array(
    'conditions'=>array(
        'Order.status_id != 12',
        'Order.status_id'=>'Status.id', 
        'Order.instrument_id'=>'Instrument.id'
        ),
    'fields'=>array('DISTINCT (Order.number) as number', 'name', 'other'),
    'group'=> 'Order.number',
    'order'=>'Order.fld_sl_no DESC'
    )
);

(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. :)

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文