在 CakePHP 的查找函数中使用 DISTINCT [英] Using DISTINCT in a CakePHP find function

问题描述

我正在编写一个 CakePHP 1.2 应用程序.我有一个我希望用户能够过滤不同字段的人员列表.对于每个可过滤的字段，我都有一个下拉列表.选择过滤器组合，点击过滤器，页面只显示匹配的记录.

I am writing a CakePHP 1.2 app. I have a list of people that I want the user to be able to filter on different fields. For each filterable field, I have a drop down list. Choose the filter combination, click filter, and the page shows only the records that match.

在 people_controller 中，我有这么一段代码:

In people_controller, I have this bit of code:

$first_names = $this->Person->find('list', array(
    'fields'=>'first_name',
    'order'=>'Person.first_name ASC',
    'conditions'=> array('Person.status'=>'1')
));
$this->set('first_names', $first_names);

(状态 = 1，因为我使用的是软删除.)

(Status = 1 because I am using a soft delete.)

这会创建一个所有名字的有序列表.但是里面有重复.

That creates an ordered list of all first_names. But duplicates are in there.

在 Cookbook 中挖掘，我找到了一个使用 DISTINCT 关键字的示例，并修改了我的代码以使用它.

Digging around in the Cookbook, I found an example using the DISTINCT keyword and modified my code to use it.

$first_names = $this->Person->find('list', array(
    'fields'=>'DISTINCT first_name',
    'order'=>'Person.first_name ASC',
    'conditions'=> array('Person.status'=>'1')
));

这给了我这样的 SQL 错误:

This gives me an SQL error like this:

Query: SELECT `Person`.`id`, DISTINCT `Person`.` first_name` FROM `people` AS `Person`   WHERE `Person`.`status` = 1   ORDER BY `Person`.`first_name` ASC

问题很明显.该框架正在将 Person.id 添加到查询中.我怀疑这来自使用列表".

The problem is obvious. The framework is adding Person.id to the query. I suspect this comes from using 'list'.

当点击过滤器按钮时，我将使用选定的过滤器创建一个 SQL 语句.我不需要is字段，但无法摆脱它.

I will use the selected filter to create an SQL statement when the filter button is clicked. I don't need the is field, but can't get rid of it.

谢谢，弗兰克·卢克

推荐答案

你说得对，看来你不能将 DISTINCT 与 list 一起使用.由于您不需要 id 而只需要名称，您可以像上面一样使用 find all 然后 $first_names = Set::extract($first_names, '/Person/first_name');.这将为您提供一个具有不同名字的数组.

You're right, it seems that you cannot use DISTINCT with list. Since you don't need id but only the names, you can use find all like above and then $first_names = Set::extract($first_names, '/Person/first_name');. That will give you a array with distinct first names.

这篇关于在 CakePHP 的查找函数中使用 DISTINCT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！