在'where'子句中获取列'country_id'是不明确的 - 在cakephp 1.3中的搜索查询中出错

Question

I am working with CakePHP 1.3 version for search functionality using Search Plugin.

I have three models:

• Demo,
• Country
• State

Demo has two foreign keys, country_id and state_id. State has the foreign key country_id.

What I am doing is, I have search form which have country & state drop down which fetch all data from countries & states table. When i search any of country from dropdown & submit the form it will show me below error. If i search using only state dropdown i get the correct result.

When I execute the search query, I get the error

'Column 'country_id' in where clause is ambiguous'

My query is:

SELECT `Demo`.`id`, `Demo`.`demo2`, `Demo`.`desc`, `Demo`.`subject`, `Demo`.`gender`, `Demo`.`country_id`, `Demo`.`state_id`, `Demo`.`image_url`, `Country`.`id`, `Country`.`name`, `State`.`id`, `State`.`country_id`, `State`.`description` FROM `demos` AS `Demo` LEFT JOIN `countries` AS `Country` ON (`Demo`.`country_id` = `Country`.`id`) LEFT JOIN `states` AS `State` ON (`Demo`.`state_id` = `State`.`id`) WHERE `country_id` = 2

Model relationships in Demo table:

var $belongsTo = array(
    'Country' => array(
        'className' => 'Country',
        'foreignKey' => 'country_id',
        'conditions' => '',
        'fields' => '',
        'order' => ''
    ),

    'State' => array(
        'className' => 'State',
        'foreignKey' => 'state_id',
        'conditions' => '',
        'fields' => '',
        'order' => ''
    ),
);

The controller query to fetch all Country in dropdown is:

$country=$this->Country->find('list'); //just display the list of country in dropdown

The query search the data from all fields except Country (country_id), because it will not know which country_id it is looking for from table Demo or table State. I need the country_id from the demo table to get the correct result.

Answer 1

Try this:

SELECT
Demo.id,
Demo.demo2,
Demo.desc,
Demo.subject,
Demo.gender,
Demo.country_id,
Demo.state_id,
Demo.image_url,
Country.id,
Country.name,
State.id,
State.country_id,
State.description

FROM demos AS Demo
LEFT JOIN countries AS Country ON (Demo.country_id = Country.id)
LEFT JOIN states AS State ON (Demo.state_id = State.id) WHERE Demo.country_id = 2