本文介绍了CakePHP在每个表上查找具有条件的连接记录的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我想查看加入的所有记录,在连接的每一侧设置 WHERE 条件。

I want to see all the records from a join, setting a WHERE condition on each side of the join.

例如,我有 LOAN BORROWER (加入 borrower.id = loan.borrower_id )。我想要的记录,其中LOAN.field = 123和 BORROWER.field ='abc'

For example, I have LOAN and BORROWER (joined on borrower.id = loan.borrower_id). I want the records where LOAN.field = 123 and BORROWER.field = 'abc'.

(例如,)似乎说我应该使用Containable。

The answers here (this one, for example) seem to say that I should use Containable.

我试过。这是我的代码:

I tried that. Here's my code:

$stuff = $this->Borrower->find('all', array(
    'conditions' => array(
        'Borrower.email LIKE' => $this->request->data['email'] // 'abc'
    ),
'contain'=>array(
    'Loan' => array(
        'conditions' => array('Loan.id' => $this->request->data['loanNumber']) // 123
        )
    )
));

我希望有一个结果,因为在我的数据中,这些条件。相反,我得到两个结果:

I expected to have a single result because in my data, there is only one joined record with both of those conditions. Instead, I get two results,

结果1是 {借款人:{field:abc,LOAN:{field:123}} //正确

结果2是 {借款人:{field:abc,LOAN:{NULL}} //不正确

当我看到CakePHP使用的SQL时,我看不到连接。我看到的是两个单独的查询:

When I look at the SQL that CakePHP used, I don't see a join. What I see is two separate queries:

查询1: SELECT * from BORROWER //(产生2个ID)

查询2: SELECT * FROM LOAN WHERE(ID)中的borrower_id

这不是我想要的。我想加入表,然后应用我的条件。我可以很容易地编写SQL查询,但是我们尝试使用Cake方法,因为我们已经采用了这个框架。

This is not what I want. I want to join the tables, then apply my conditions. I could easily write the SQL query, but am trying to do it the Cake way since we've adopted that framework.

有可能吗?

推荐答案

尝试这样做:

    $options['conditions'] = array(
           'Borrower.email LIKE' => $this->request->data['email'] // 'abc',
           'loan.field' => '123' )

    $options['joins'] = array(
        array('table' => 'loans',
              'alias' => 'loan',
              'type' => 'INNER',
              'conditions' => array(
                    'borrower.id = loan.borrower_id')
                )
            );

    $options['fields'] = array('borrower.email', 'loan.field');

    $test = $this->Borrower->find('all', $options);

您应该看到如下的SQL语句:

You should see a SQL statement like:

SELECT borrower.email, loan.field
FROM borrowers AS borrower
INNER JOIN loans AS loan
    ON borrower.id = loan.borrower_id
    AND loan.field = '123'
WHERE borrower.email = 'abc'

您的结果将显示在数组中

Your results will be in an array

{Borrower: {field:abc} LOAN: {field: 123} }

您可以在此。

这篇关于CakePHP在每个表上查找具有条件的连接记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

06-25 04:49