在CakePHP find函数中使用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.)

这会创建所有first_names的有序列表。

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。我怀疑这是来自使用'list'。

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.

谢谢你，
弗兰克卢克

Thank you, Frank Luke

推荐答案

您是对的，似乎不能使用列表使用 DISTINCT 。因为你不需要 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 find函数中使用DISTINCT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！