如何使用 dql 从数据表中获取唯一值?
我有一个表,其中有一列存储了各种值。我想使用 dql 从该表中检索唯一值。
Doctrine_Query::create()
->select('rec.school')
->from('Records rec')
->where("rec.city='$city' ")
->execute();
现在我只想要独特的价值。谁能告诉我该怎么做...
编辑
表结构:
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 ;
这是我正在使用的查询:
Doctrine_Query::create()
->select('DISTINCT rec.city')
->from('Records rec')
->where("rec.state = '$state'")
// ->getSql();
->execute();
为此生成 Sql 给我:
SELECT DISTINCT r.id AS r__id, r.city AS r__city FROM records r WHERE r.state = 'AR'
现在检查生成的 sql:::: DISTINCT 位于“id”列,因为我希望在城市列上具有不同。任何人都知道如何解决这个问题。
EDIT2
Id 是唯一的,因为它是一个自动增量值。是的,我在城市列中有一些真正的重复项,例如:德里和德里。是的..现在,当我尝试从中获取数据时,我得到了德里两次。我怎样才能进行这样的查询:
select DISTINCT rec.city where state="xyz";
因为这会给我正确的输出。
EDIT3:
任何人都可以告诉我如何弄清楚这个查询..???
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.
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:
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.
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:
select DISTINCT rec.city where state="xyz";
Cause this will give me the proper output.
EDIT3:
Anybody who can tell me how to figure out this query..???
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(9)
取决于您使用的版本,但我遇到了同样的问题,并且 ->distinct() 对我有用。
Depends on what version you are using, but I had the same issue and ->distinct() worked for me.
你能使用 GROUP BY 吗?
Could you use a GROUP BY?
RawSql 中不需要
代替 ->select('DISTINCT rec.city')
使用 ->select('DISTINCT(rec.city) as city')
There is no need in RawSql
In place of ->select('DISTINCT rec.city')
Use ->select('DISTINCT(rec.city) as city')
您可以使用 Raw_Sql 类来完成此操作。这是我刚刚在自己的数据库上进行的测试:
You can use the Raw_Sql class to accomplish this. Here is a test I just did on my own database:
DISTINCT 是一个聚合函数。因此,Doctrine 无法将你的结果水化为对象。使用不同的补水策略,就像巴特曼建议的那样。
对我来说效果很好
DISTINCT is an aggregation function. As such Doctrine cannot hydrate your result into objects. Use a different hydration strategy, like bartman has suggested.
worked fine for me
Doctrine 总是将主键添加到字段列表中的原因在于 Hydration 内部。当 Doctrine 从数据库中获取行时,它将它们合并(=转换)为对象层次结构,并使用主键引用模型对象。就您而言,这种行为是不需要的,因为只对城市名称感兴趣。
我建议两种解决方案,不幸的是我现在无法测试它们。
$q = Doctrine_RawSql::create()
->select('DISTINCT {rec.city}')
->from('记录rec')
->where('rec.state = ?', $state)
->addComponent('rec', 'Record');
$cities = $q->execute()
$q = Doctrine_Query::create()
->select('不同的rec.city')
->from('记录rec')
->where("rec.state = ?", $state);
$cities = $q->execute(array(), Doctrine_Core::HYDRATE_SCALAR);
$cities应包含带有“rec_city”等键的数组数组。
请注意在 where 语句中使用
?
占位符,最好让 Doctrine 进行转义,而不是自己处理它。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.
I suggest two solutions, unfortunately I cannot test them right now.
$q = Doctrine_RawSql::create()
->select('DISTINCT {rec.city}')
->from('Records rec')
->where('rec.state = ?', $state)
->addComponent('rec', 'Record');
$cities = $q->execute()
$q = Doctrine_Query::create()
->select('DISTINCT rec.city')
->from('Records rec')
->where("rec.state = ?", $state);
$cities = $q->execute(array(), Doctrine_Core::HYDRATE_SCALAR);
$cities should contain an array of arrays with keys like '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.这对我有用。
This one works for me.
重新回答:
在我的本地计算机上检查了这一点 - 不起作用。因此,我建议您使用 PDO,直到该问题得到增强或修复:
提示
我建议您使用外键引用
city<上的城市列表/code> 列而不是纯文本,以获得灵活性和更好的性能。
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:
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.如果您想在存储库中使用 Doctrine 查询,以下内容对我有用:
注意:我使用的是 Oracle 11G,但相同的查询应该适用于 MySQL。
If you want to use a Doctrine query in a repository the following worked for me:
Note: I am using Oracle 11G but this same query should work on MySQL.