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

Gaulish

Try changing select('img.*') to select('DISTINCT img.*') – Andonis

DISTINCT applies to all columns you're selecting. IOW SELECT DISTINCT a, b, c can be seen as SELECT DISTINCT(a, b, c). Is that a problem for you? Do you have duplicities in rec.city? Are they real duplicities? (City name would make for a terrible key, they're not unique by any stretch.) – Yuki

@ just somebody check the text under EDIT2 – Gaulish

Answer 1

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

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

Idomeneus

Please don't ever use unescaped query parameters like ->where("rec.state = '$state'") unless you want your web hacked really easily (SQL Injection). Instead use safe parameters: ` ->where('rec.state = :state') ->setParameter('state', $state) ` – Leverrier

Answer 2

Could you use a GROUP BY?

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

Royden

Answer 3

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

Weever

ita the brackets round the rec.city that get rid of the id. without it Doctrine brings id and city back and it's not distinct. Ta. Save me creating another table. – Carlettacarley

Answer 4

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

?>

Thalamus

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

Glyptograph

Answer 6

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()

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

Commissure

Answer 7

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

This one works for me.

Giamo

Answer 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:

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

Jablon

SELECT DISTINCT r.id AS r__id, r.school AS r__school FROM records r WHERE r.city = ? This is the sql generated by the query.. But check the DISTINCT keyword location. its for id column not for the school column. Check the Edit portion in the question. Thanks – Gaulish

dude thats not the solution. I am not able to understand why the hell this query cant be figured out ..??? – Gaulish

Answer 9

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.

    $sql =
      "
      SELECT 
        DISTINCT d.fieldname 
        
      FROM 
        Oracle:Dmessage d
      "
    ;
    $query = $this->getEntityManager()->createQuery($sql);
    $results = $query->getResult();

Municipal