CakePHP使用选择条件从多个表中检索数据 - CakePHP Retrieving data from multiple tables with select conditions

CakePHP Retrieving data from multiple tables with select conditions

我正在使用CakePHP构建应用程序，但在使用一系列联接检索数据时遇到了问题。在下面的简化示例中，别名为Delivery的联接可能有多个记录，我想在该表的特定字段中带回具有最大值的记录。

$inv_conditions = array(    'Invoice.invoice_date >=' => $DateFormatter->dateForDB($dateFrom), 
                 'Invoice.invoice_date <=' => $DateFormatter->dateForDB($dateTo),
                 'Invoice.id >' => 385380 );    
$join   =   array(array(
                'table' => 'jobs',
                'alias' => 'Jobs',
                'type'  => 'LEFT',
                'conditions'    => array('Invoice.job_id = Jobs.JOB_ID' ) 
              ),    
            array(
                'table' => 'functional',
                'alias' => 'Delivery',
                'type'  => 'LEFT'
                'conditions'=> array('AND ' => array('Invoice.job_id = Delivery.JOB',
                                                     'Delivery.TYPE_STAGE = 1') 
                            )
                     )
        );    
$invoices = $this->Invoice->find("all", array(
                "fields"    =>  array(
                            'Invoice.id',
                            'Invoice.job_id',
                            'Invoice.invoice_no',
                            'Invoice.consolidated_type',
                            'Invoice.customer_id_tbc',
                            'Invoice.invoice_date',
                            'Invoice.invoice_reference',
                            'Invoice.invoice_req',
                            'Jobs.PAYMENT_TYPE',
                            'Jobs.CUSTOMER',
                            'Jobs.MOST_RELEVANT_LINE',
                            'Delivery.DEPARTURE_DATE',
                            'Delivery.CNOR_CNEE_NAME',
                            'Delivery.TOWN_NAME',
                              ),
                "conditions"    =>  $inv_conditions,
                "joins"     => $join
                )
            );
}

我可以用SQL做这件事，没有问题，如下所示：

SELECT 
    jobs.JOB_ID, 
    jobs.CUSTOMER, 
    functional.JOB_LINE_ORDER, 
    functional.CNOR_CNEE_NAME, 
    functional.TOWN_NAME 
FROM jobs JOIN functional ON
            jobs.JOB_ID = 'M201409180267'
        AND 
            functional.JOB =  jobs.JOB_ID
        AND 
            functional.TYPE_STAGE = 0 
        AND 
            functional.JOB_LINE_ORDER = 
               (SELECT MAX(JOB_LINE_ORDER) FROM functional 
                WHERE functional.JOB = 'M201409180267' AND functional.TYPE_STAGE = 0)

我已经尝试使用以下条件数组：

'conditions'    =>    array('AND ' => 
                           array( 'Invoice.job_id = Delivery.JOB',
                                  'Delivery.TYPE_STAGE = 1'
                                  'Delivery.JOB_LINE_ORDER = MAXIMUM(Delivery.JOB_LINE_ORDER)' )
                            )

这确实会返回结果，但不会返回正确的结果，Cake生成的SQL在where子句中确实有一个select。当在cake中检索数据时，有没有一种方法可以做到这一点，其中创建的sql语句将在where子句中有一个select。

如有任何建议，我们将不胜感激。谢谢Bas

您需要使用子查询在where子句中生成select。您可以在模型中创建一个执行此操作的方法，并从控制器中调用它。

$subQuery = $db->buildStatement(
            array(
                'fields'     => array(''),
                'table'      => $db->fullTableName($this),
                'alias'      => 'aliasName',
                'limit'      => null,
                'offset'     => null,
                'joins'      => array(),
                'conditions' => $conditionsArray,
                'order'      => null,
                'group'      => null
            ),
            $this
        );
        $subQuery = ' <<YOUR MAIN QUERY CONDITION (' . $subQuery . ') >>';
        $subQueryExpression = $db->expression($subQuery);
        $conditions[] = $subQueryExpression;
        return $this->Model->find('list', compact('conditions'));