How can get unique values from data table using dql?

Question

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..???

Answer 1

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:

$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.

Answer 2

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.

1. Try using Doctrine_RawSql. RawSql has special handling for DISTINCT Queries.

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

$cities = $q->execute()

1. 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.

$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.

Answer 3

http://trac.symfony-project.org/ticket/1644

Its a bug dude..

Answer 4

Hi piemesons,

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

<?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";
}

?>

Answer 5

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

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

worked fine for me

Answer 6

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

Answer 7

Could you use a GROUP BY?

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