如何使用dql从数据表中获取唯一值？

I am having a table in which there is a column in which various values are stored.i want to retrieve unique values from that table using dql.

我有一个表，其中存在一个列，其中存储了各种值。我想使用dql从该表中检索唯一值。

         Doctrine_Query::create()
                    ->select('rec.school')
                    ->from('Records rec')                   
                    ->where("rec.city='$city' ")                                    
                    ->execute();

Now i want only unique values. Can anybody tell me how to do that...

现在我只想要唯一的价值观。任何人都可以告诉我该怎么做......

Edit

编辑

Table Structure:

表结构：

CREATE TABLE IF NOT EXISTS `records` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`state` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `city` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `school` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=16334 ;

This is the Query I am using:

这是我正在使用的查询：

   Doctrine_Query::create()
          ->select('DISTINCT rec.city')
          ->from('Records rec')                   
          ->where("rec.state = '$state'")                                    
             // ->getSql();
           ->execute();

Generting Sql for this gives me:

为此创建Sql给了我：

SELECT DISTINCT r.id AS r__id, r.city AS r__city FROM records r WHERE r.state = 'AR'

Now check the sql generated:::: DISTINCT is on 'id' column where as i want Distinct on city column. Anybody know how to fix this.

现在检查生成的sql :::: DISTINCT在'id'列上，我想在城市列上区分。任何人都知道如何解决这个问题。

EDIT2

EDIT2

Id is unique cause its an auto incremental value.Ya i have some real duplicates in city column like: Delhi and Delhi. Right.. Now when i am trying to fetch data from it, I am getting Delhi two times. How can i make query like this:

Id是独特的，因为它是一个自动增量值。我在城市列中有一些真正的重复：德里和德里。对..现在当我试图从中获取数据时，我正在两次获得德里。我该如何进行这样的查询：

  select DISTINCT rec.city where state="xyz";

Cause this will give me the proper output.

因为这会给我正确的输出。

EDIT3:

EDIT3：

Anybody who can tell me how to figure out this query..???

任何人都可以告诉我如何弄清楚这个查询.. ???

8 个解决方案

#1

Depends on what version you are using, but I had the same issue and ->distinct() worked for me.

取决于你使用的是什么版本，但我遇到了同样的问题 - > distinct（）对我有用。

Doctrine_Query::create()
      ->select('rec.city')->distinct()
      ->from('Records rec')                   
      ->where("rec.state = '$state'")                                    
       ->execute();

#2

Could you use a GROUP BY?

你能用GROUP BY吗？

Doctrine_Query::create()
    ->select('rec.school')
    ->from('Records rec')                   
    ->where("rec.city='$city' ")                                    
    ->groupBy('rec.school')   
    ->execute();

#3

There is no need in RawSql
In place of ->select('DISTINCT rec.city')
Use ->select('DISTINCT(rec.city) as city')

在RawSql中没有必要代替 - > select（'DISTINCT rec.city'）使用 - > select（'DISTINCT（rec.city）as city'）

#4

You can use the Raw_Sql class to accomplish this. Here is a test I just did on my own database:

您可以使用Raw_Sql类来完成此任务。这是我在我自己的数据库上做的测试：

<?php

set_include_path(get_include_path() . PATH_SEPARATOR . 'library');

require('Doctrine.php');
spl_autoload_register(array('Doctrine', 'autoload'));

Doctrine::loadModels('application/models/generated');
Doctrine::loadModels('application/models');

$dm=Doctrine_Manager::getInstance();
$conn = $dm->openConnection("mysql://dbuser:dbpass@localhost/database");  //changed actual values...
$q = new Doctrine_RawSql($conn);

$q->select('{c.name}')
   ->distinct()
   ->from('contactlist c')
   ->addComponent('c', 'Contactlist');

//this outputs:  SELECT DISTINCT c.name AS c__name FROM contactlist c
echo $q->getSqlQuery() . "<br>\n"; 

$contacts = $q->execute();
foreach($contacts->toArray() as $contact){
    echo $contact['name'] . "<br>\n";
}

?>

#5

The reason Doctrine is always adding the primary key to the fields list lies inside the Hydration. When Doctrine fetches rows from the Database it hydrates (=converts) them into an object hierarchy and references the model objects using the primary key. In your case, this behaviour is not wanted, since just the city names are of interest.

Doctrine总是将主键添加到字段列表中的原因在于Hydration。当Doctrine从数据库中获取行时，它会将它们水合（=转换）为对象层次结构，并使用主键引用模型对象。在您的情况下，不需要此行为，因为只有城市名称是有意义的。

I suggest two solutions, unfortunately I cannot test them right now.

我建议两种解决方案，遗憾的是我现在无法测试它们。

Try using Doctrine_RawSql. RawSql has special handling for DISTINCT Queries.
尝试使用Doctrine_RawSql。 RawSql对DISTINCT查询有特殊处理。

$q = Doctrine_RawSql::create() ->select('DISTINCT {rec.city}') ->from('Records rec') ->where('rec.state = ?', $state) ->addComponent('rec', 'Record');

$ q = Doctrine_RawSql :: create（） - > select（'DISTINCT {rec.city}'） - > from（'Records rec'） - > where（'rec.state =？'，$ state） - > addComponent（ 'rec'，'Record'）;

$cities = $q->execute()

$ cities = $ q-> execute（）

Use a non-object-hierarchy based Hydrator. This might keep Doctrine from fetching the primary key field to initialize the model class. See the documentation (can't post the link - new user. sorry.) for more information.
使用基于非对象层次结构的Hydrator。这可能使Doctrine不会获取主键字段来初始化模型类。请参阅文档（无法发布链接 - 新用户。抱歉。）了解更多信息。

$q = Doctrine_Query::create() ->select('DISTINCT rec.city') ->from('Records rec') ->where("rec.state = ?", $state);

$ q = Doctrine_Query :: create（） - > select（'DISTINCT rec.city'） - > from（'Records rec'） - > where（“rec.state =？”，$ state）;

$cities = $q->execute(array(), Doctrine_Core::HYDRATE_SCALAR);

$ cities = $ q-> execute（array（），Doctrine_Core :: HYDRATE_SCALAR）;

$cities should contain an array of arrays with keys like 'rec_city'.

$ cities应该包含一个带有'rec_city'之类的键的数组。

Please note the use of the ? placeholder in the where statements, it's good practice to let Doctrine do the escaping and not struggle with it yourself.

请注意使用？占位符在where语句中，最好让Doctrine进行转义而不是自己挣扎。

#6

DISTINCT is an aggregation function. As such Doctrine cannot hydrate your result into objects. Use a different hydration strategy, like bartman has suggested.

DISTINCT是一个聚合函数。因为这样的学说不能将你的结果融合成物体。使用不同的水合策略，如bartman建议的那样。

$q = Doctrine_Query::create()
->select('DISTINCT rec.city')
->from('Records rec')
->execute(array(), Doctrine_Core::HYDRATE_SCALAR);

worked fine for me

对我来说很好

#7

$query = $this->getEntityManager()->createQueryBuilder()
                    ->select('DISTINCT(p.document)')
                    ->from('Products', 'p');

This one works for me.

这个对我有用。

#8

Re-answered:

再回答：

Checked this on my local computer - didn't work. So let me advice to use PDO until this will be enhanced or fixed:

在我的本地计算机上检查过 - 没有用。所以，让我建议使用PDO，直到这将得到增强或修复：

$dbh = Doctrine_Manager::connection()->getDbh();
$stmt = $dbh->prepare('SELECT DISTINCT(rec.city) FROM <tablename> WHERE rec.state = :state');
$stmt->bindParam(':state', $state);
$stmt->execute();
$result = $stmt->fetchAll();

Tip
I'd recommend you to use foreign key referenced to list of cities on city column instead of plain text for flexibility and better performance.

提示我建议您使用引用城市列上的城市列表而不是纯文本的外键来获得灵活性和更好的性能。

#1