MySQL 如何使用 dql 从数据表中获取唯一值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2148620/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can get unique values from data table using dql?
提问by Mohit Jain
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:::: DISTINCTis on 'id' column where as i want Distinct on city column. Anybody know how to fix this.
现在检查生成的 sql:::: DISTINCT在 'id' 列上，因为我想要在 city 列上 Distinct。任何人都知道如何解决这个问题。
EDIT2
编辑2
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:
编辑3：
Anybody who can tell me how to figure out this query..???
谁能告诉我如何找出这个查询..???
回答by cb0
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();
回答by m14t
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();
回答by Max
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')
回答by AJ.
You can use the Raw_Sqlclass 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";
}
?>
回答by Stefan Haberl
DISTINCT is an aggregation function. As such Doctrine cannot hydrate your result into objects. Use a different hydration strategy, like bartman has suggested.
DISTINCT 是一个聚合函数。因此，Doctrine 无法将您的结果水合为对象。使用不同的补水策略，就像 bartman 建议的那样。
$q = Doctrine_Query::create()
->select('DISTINCT rec.city')
->from('Records rec')
->execute(array(), Doctrine_Core::HYDRATE_SCALAR);
worked fine for me
对我来说很好用
回答by bartman
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 进行转义而不是自己挣扎是一种很好的做法。
回答by npetrovski
$query = $this->getEntityManager()->createQueryBuilder()
->select('DISTINCT(p.document)')
->from('Products', 'p');
This one works for me.
这个对我有用。
回答by Darmen Amanbayev
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 keyreferenced to list of cities on citycolumn instead of plain text for flexibility and better performance.
提示
我建议您使用引用列上城市列表的外键city而不是纯文本，以获得灵活性和更好的性能。
