cakephp：使用连接搜索结果 [英] cakephp: using a join for search results

问题描述

我现在完全迷惑自己。

我有三张表：申请人， applicant_qualifications 和资格。

有一个下降的资格的形式。结果应该是有资格的申请人的名单。

In the index view for applicants, I have a form with a dropdown of qualifications. The results should be a list of applicants with that qualification.

所以我需要索引视图中的申请人表是基于加入，对吗？

So I need the table of applicants on the index view to be based on a join, right?

如果我将它添加到我的applicants_controller：

If I add this to my applicants_controller:

$options['joins'] = array(  
    array(  
        'table' => 'applicants_qualifications',  
        'alias' => 'q',  
        'type' => 'left outer', // so that I get applicants with no qualifications too   
        'conditions' => array('Applicant.id = q.applicant_id',)  
    )  
);
$this->Applicant->find('all', $options);

我在页面底部有一个额外的sql语句，

I get an additional sql statement at the bottom of the page, with the left outer join but the sql without the join is there too.

我认为这行：

$this->set('applicants', $this->paginate());

调用不带连接的sql语句。

calls the sql statement without the join.

看起来像我需要结合连接$选项与分页调用。是正确的吗？

Looks like I need to combine the join $options with the paginate call. Is that right?

如果我使用搜索表单，我会得到：where clause'中的未知列'Qualifications.qualification_id'

If I use the search form, I get: Unknown column 'Qualifications.qualification_id' in 'where clause'

所以页面显然还没有使用我的sql加入。

So the page is obviously not yet 'using' my sql with the join.

对不起 - 我还是一个noob。任何帮助赞赏...

Sorry - I'm still a noob. Any help appreciated...

推荐答案

为了设置 conditions joins 等您的模型分页，您必须按照以下步骤操作：

In order to set conditions, joins, etc for your model's pagination, you must do it as follows:

function admin_index() {
    $this->Applicant->recursive = 0;
    $this->paginate = array(
        'Applicant' => array(
            // 'conditions' => array('Applicant.approved' => true),
            'joins' => array(
                array(
                    'table' => 'applicants_qualifications',  
                    'alias' => 'ApplicationsQualification',  
                    'type' => 'left outer',
                    'conditions' => array('Applicant.id = ApplicationsQualification.applicant_id')  
                )
            )
            // 'order' => array('Applicant.joined DESC')
        )
    );
    $this->set('applicants', $this->paginate());
}

我注释掉了一些可以在以后包含的示例密钥

I've commented out some sample keys that you can include later on - just to give you an idea of how it works.

希望有所帮助！

这篇关于cakephp：使用连接搜索结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！