6
6

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

DoctrineのDISTINCTは正しく動作しない

Last updated at Posted at 2014-06-09

DISTINCT / LEFT JOIN

Doctrine_Query::create()->from('HOGE a')->distinct(true)->select('a.fuga');

// Expected result : SELECT DISTINCT fuga FROM hoge.
// Actual result : SELECT DISTINCT id, fuga FROM hoge.

見ての通り、発行されるSQLに勝手にID(主キー)が入るので正しく動作しない。
DISTINCTをselect()内に持ってきたとしても勝手に書き換えられるので駄目。

http://stackoverflow.com/questions/7188219/how-to-select-distinct-query-using-symfony2-doctrine-query-builder
http://www.developpez.net/forums/d793147/php/php-sgbd/orm/doctrine/doctrine-select-distinct/

回避方法は「GROUP BYを使え」らしい。
ふざけてるの?

Doctrine_Query::create()->from('HOGE a')->select('a.fuga')->groupBy(a.fuga);

// SELECT id, fuga FROM hoge group by fuga.

一応想定と同じ結果は取得できるのだが、意味が全く異なるので正しい動作とは言いかねる。

結論
「DoctrineでDISTINCTするときは直接SQL書こう」

Doctrine::getConnectionByTableName('HOGE')->execute('SELECT DISTINCT fuga FROM hoge')->fetchAll();

ところでDISTINCTよりEXISTSの方が早いという記事をよく見かけるんだけど、
結合しないfugaテーブルでEXISTSを使うにはどうすればいいんだろう?

6
6
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
6
6

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?