php - Cakephp 中的联接查询缺少结果

Question

我在使用 Cakephp 联接查询时遇到问题。我的数据库是这样构建的:联系属于用户

在我的联系人 Controller 中，我有一个搜索联系人的功能。可以通过 User.first_name 或 User.last_name 搜索联系人。

我像这样构建了查询:

$options['contain'] = array(
    'User' => array(
        'fields' => array('id', 'first_name', 'last_name'),
    )
);

$options['conditions']['OR'] = array(
    array('Contact.id LIKE' => '%'.$search.'%'),
    array('User.first_name LIKE' => '%'.$search.'%'),
    array('User.last_name LIKE' => '%'.$search.'%'),
    array('Building.title LIKE' => '%'.$search.'%'),
    array('City.name LIKE' => '%'.$search.'%'),
    array('Manager.first_name LIKE' => '%'.$search.'%'),
    array('Manager.last_name LIKE' => '%'.$search.'%'),
);

$contacts = $this->Contact->find('all', $options);

这个查询给了我那个sql查询:

'SELECT `Contact`.`id`, `Contact`.`specific_works`, `Contact`.`user_id`, `User`.`id`, `User`.`first_name`, `User`.`last_name`, FROM `pfre`.`contacts` AS `Contact` LEFT JOIN `pfre`.`authake_users` AS `User` ON (`Contact`.`user_id` = `User`.`id`) WHERE `Contact`.`white_label_id` = 18 AND ((`Contact`.`id` LIKE '%search%') OR (`User`.`first_name` LIKE '%search%') OR (`User`.`last_name` LIKE '%search%') OR (`Building`.`title` LIKE '%search%') OR (`City`.`name` LIKE '%search%') OR (`Manager`.`first_name` LIKE '%search%') OR (`Manager`.`last_name` LIKE '%search%'))'

这没有给我任何结果，而我应该得到 2 个结果(它们在我的数据库中)。

有人明白这个查询出了什么问题吗？

有关信息，完整(未简化，包含所有字段)查询:

'SELECT `Contact`.`id`, `Contact`.`specific_works`, `Contact`.`comments`, `Contact`.`follow`, `Contact`.`priority`, `Contact`.`code`, `Contact`.`step`, `Contact`.`intervention_type`, `Contact`.`intervention_precision`, `Contact`.`energy_consumption_before`, `Contact`.`energy_consumption_after`, `Contact`.`ghg_emission_before`, `Contact`.`ghg_emission_after`, `Contact`.`diagnosis_date`, `Contact`.`diagnostician`, `Contact`.`heating_details`, `Contact`.`heater_details`, `Contact`.`equipments_details`, `Contact`.`wall_details`, `Contact`.`floor_details`, `Contact`.`ceiling_details`, `Contact`.`loan_received`, `Contact`.`anah_project`, `Contact`.`labor_cost`, `Contact`.`equipment_cost`, `Contact`.`other_cost`, `Contact`.`cite_amount`, `Contact`.`anah_amount`, `Contact`.`cee_amount`, `Contact`.`eco_amount`, `Contact`.`tva_amount`, `Contact`.`anah_ase_amount`, `Contact`.`ptz_acquisition_amount`, `Contact`.`anah_other_amount`, `Contact`.`other_helps`, `Contact`.`other_loans`, `Contact`.`monthly_payments`, `Contact`.`hide_consumptions`, `Contact`.`abandonment_reason`, `Contact`.`abandonment_reason_other`, `Contact`.`project_source`, `Contact`.`project_source_other`, `Contact`.`renovation_type`, `Contact`.`work_col_1`, `Contact`.`work_col_2`, `Contact`.`considered_energy`, `Contact`.`considered_ges`, `Contact`.`custom_1_energy`, `Contact`.`custom_1_ges`, `Contact`.`custom_2_energy`, `Contact`.`custom_2_ges`, `Contact`.`performed_energy`, `Contact`.`performed_ges`, `Contact`.`favorite_scenario1`, `Contact`.`favorite_scenario2`, `Contact`.`favorite_scenario3`, `Contact`.`archived`, `Contact`.`user_id`, `Contact`.`manager_id`, `Contact`.`white_label_id`, `Contact`.`building_id`, `Contact`.`ceie_id`, `Contact`.`created`, `Contact`.`modified`, `Contact`.`rennes`, (energy_consumption_before - energy_consumption_after) AS  `Contact__energy_consumption_prevented`, (ghg_emission_before - ghg_emission_after) AS  `Contact__ghg_emission_prevented`, `User`.`id`, `User`.`first_name`, `User`.`last_name`, `Manager`.`id`, `Manager`.`first_name`, `Manager`.`last_name`, `Manager`.`email`, `Manager`.`phone`, `Building`.`id`, `Building`.`title`, `Building`.`city_id` FROM `pfre`.`contacts` AS `Contact` LEFT JOIN `pfre`.`authake_users` AS `User` ON (`Contact`.`user_id` = `User`.`id`) LEFT JOIN `pfre`.`authake_users` AS `Manager` ON (`Contact`.`manager_id` = `Manager`.`id`) LEFT JOIN `pfre`.`buildings` AS `Building` ON (`Contact`.`building_id` = `Building`.`id`) INNER JOIN `pfre`.`cities` AS `City` ON (`Building`.`city_id` = `City`.`id`)  WHERE `Contact`.`white_label_id` = 18 AND ((`Contact`.`id` LIKE '%search%') OR (`User`.`first_name` LIKE '%search%') OR (`User`.`last_name` LIKE '%search%') OR (`Building`.`title` LIKE '%search%') OR (`City`.`name` LIKE '%search%') OR (`Manager`.`first_name` LIKE '%search%') OR (`Manager`.`last_name` LIKE '%search%'))'

编辑:我有时会得到一些结果。这取决于我用作“搜索”的字符串，但其中一些字符串给了我正确的结果。

Answer 1

我发现问题了!当我构建查询时，我设置了一个联接:

$options['joins'] = array(
            array(
                'table' => 'cities',
                'alias' => 'City',
                'type' => 'INNER',
                'conditions' => [
                    'Building.city_id = City.id'
                ]
            ),
        );

我的一些建筑物没有 city_id。此参数不包括没有 city_id 或 city_id 与 City.id 不对应的建筑物。

我现在需要知道如何包含没有 city_id 的结果。

感谢所有帮助我找到解决方案的人!