CakePHP：如何使用内部联接从两个表检索数据？ [英] CakePHP: How to retrieve data from two tables using an inner join?

问题描述

我在数据库中有两个表，一个是 user（id，first_name，last_name），另一个是 location c。

I have two tables in a database, one as user(id,first_name,last_name), and the other one as location(id,country).

我需要根据条件对这两个表执行一个内部连接 user.id = location.id ，查询结果应包含 first_name ， last_name code> country 。

I need to perform an inner join with these two tables based on the condition user.id = location.id, and the query result should contain the columns first_name, last_name and country.

我在CakePHP中尝试了以下查询：

I have tried the following query in CakePHP:

$this->set('users', $this->User->find('list', array(
    'fields' => array(
        'User.id',
        'User.first_name',
        'location.country'
    ),
    array(
        'joins' => array(
            array(
                'table' => 'location',
                'alias' => 'location',
                'type' => 'INNER',
                'conditions' => array('User.id = location.id')
            )
        )
    )
)));

但是会产生此错误：

字段列表中的未知列location.country

Unknown column 'location.country' in 'field list'

p>

推荐答案

我认为你的语法错误，因为options数组应该有一个键。您似乎有一个额外的数组。尝试：

I think your syntax is wrong because the options array should have a key for the joins. You appear to have an extra array. Try:

$this->set('users',$this->User->find('list', 
  array(
       'fields' => array('User.id', 'User.first_name','location.country'),
       'joins' => array(array('table' => 'location',
                               'alias' => 'location',
                               'type' => 'INNER',
                               'conditions' => array('User.id = location.id')
                         ))
         )
  ));

这篇关于CakePHP：如何使用内部联接从两个表检索数据？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！