Take the 2-minute tour ×
Drupal Answers is a question and answer site for Drupal developers and administrators. It's 100% free, no registration required.

I understand that you can specify ->distinct() on the db_select statement so that it only returns distinct values when looking at ALL fields. But what I want is to return distinct values by only looking at ONE field. Here's my code:

$event_table = db_select('my_table', 'e')
    ->distinct()
    ->orderBy('e.time', 'ASC');//ORDER BY
$event_table->join('node', 'n', 'e.nid = n.nid'); //JOIN node with events
$event_table->groupBy('e.time');//GROUP BY time
$event_table->fields('e')//SELECT the fields from events
    ->fields('n',array('type','status','title'))//SELECT the fields from node
    ->orderBy('e.time', 'ASC');//ORDER BY

$result_event_table = $event_table->execute();
$result_event_table = $result_event_table->fetchAllAssoc('time');

Suppose I want the distinct column to be e.nid. You'd think ->distinct('e.nid') would work but it still returns distinct values based on all fields (i.e. distinct(columns1, column2, column3, etc).

share|improve this question
    
Any chance you could give a sample of the output SQL you're looking for? That would make it pretty easy to work out how to coax db_select into doing the same –  Clive Jul 21 at 17:56

3 Answers 3

Assuming you're trying to get to roughly this query:

SELECT DISTINCT e.nid AS nid, e.time AS time, n.type AS type, n.status AS status, n.title AS title
FROM 
{my_table} e
INNER JOIN {node} n ON n.nid = e.nid
GROUP BY e.time
ORDER BY e.time ASC

You would use:

$query = db_select('my_table', 'e')
  ->distinct()
  ->fields('e', array('nid', 'time', 'foo', 'bar'))
  ->fields('n', array('type', 'status', 'title'))
  ->groupBy('e.time')
  ->orderBy('e.time');

$query->join('node', 'n', 'n.nid = e.nid');
share|improve this answer
    
This just gives me all fields distinct, not just nid being distinct –  CHRIS Jul 21 at 18:34
    
I'm not sure what you mean, the above is a distinct query...what SQL are you trying to produce? –  Clive Jul 21 at 18:36
    
suppose there is '4' '4' '5' '5' in the nid column with a corresponding column named 'type' with 'a' 'b' 'c' 'd'. well, if I select distinct on both columns (nid,type), the result will have 4 rows. but if i select distinct on JUST nid, the result will have 2 rows. I'm trying to select distinct on JUST nid –  CHRIS Jul 21 at 18:55
    
I think you're misunderstanding how grouping works in SQL...to do what you're describing you'd need to remove the type column from the selected fields –  Clive Jul 21 at 19:19

DISTINCT is actually a global post-modifier for SELECT, that is, as opposed to SELECT ALL (returning all answers) it is SELECT DISTINCT (returning all unique answers). So a single DISTINCT acts on ALL the columns that you give it.

This makes it real hard to use DISTINCT on a single column, while getting the other columns, without doing major extremely ugly backflips.

The correct answer is to use a GROUP BY on the columns that you want to have unique answers:

share|improve this answer
    
Exactly. That's why DBTNG has it on the query level. The only time DISTINCT is column level (or vaguely looks like it) is when you do COUNT(DISTINCT foo) but even then it's a modifier of the aggregator function. –  chx Jul 22 at 21:04

Remove ->distinct() and replace it with $event_table->AddExpression('distinct e.nid', 'nid');

Like so:

$event_table = db_select('my_table', 'e');
$event_table->AddExpression('distinct e.nid', 'nid')
$event_table->orderBy('e.time', 'ASC');//ORDER BY
$event_table->join('node', 'n', 'e.nid = n.nid'); //JOIN node with events
$event_table->groupBy('e.time');//GROUP BY time

// you need to outline all fields here, can't use e.*
$event_table->fields('e')//SELECT the fields from events

    ->fields('n',array('type','status','title'))//SELECT the fields from node
    ->orderBy('e.time', 'ASC');//ORDER BY

$result_event_table = $event_table->execute();
$result_event_table = $result_event_table->fetchAllAssoc('time');
share|improve this answer
    
PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'distinct e.nid AS nid FROM mcc_notify_event_queue e INNER JOIN node n ON e.nid' at line 1: SELECT e.*, n.type AS type, n.status AS status, n.title AS title, distinct e.nid AS anid FROM {mcc_notify_event_queue} e INNER JOIN {node} n ON e.nid = n.nid GROUP BY e.time ORDER BY e.time ASC; Array ( ) –  CHRIS Jul 21 at 16:57
    
can't use e.* and distinct e.nid, throws an error. You'll need to write out your fields as I've noted above. –  Scott Joudry Jul 21 at 17:19
    
I'm still getting an error. All I've done is change your code to $event_table->fields('e',array('nid','time','event_type'))//SELECT the fields from events –  CHRIS Jul 21 at 17:36
    
you're still selecting nid twice in that case which explains the error. Try $event_table->fields('e',array('time','event_type')) –  Scott Joudry Jul 21 at 17:39
    
still an error distinct e.nid AS nid FROM my_module_event_queue e INNER JOIN node n ON e.nid ' at line 1: SELECT e.time AS time, e.event_type AS event_type, n.type AS type, n.status AS status, n.title AS title, distinct e.nid AS nid FROM {my_module_event_queue} e INNER JOIN {node} n ON e.nid = n.nid GROUP BY e.time ORDER BY e.time ASC; –  CHRIS Jul 21 at 17:42

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.