7

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

Improve this question
  • 1
    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, 2014 at 17:56

4 Answers

12

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');
Improve this answer
  • This just gives me all fields distinct, not just nid being distinct 
    – CHRIS
     Jul 21, 2014 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, 2014 at 18:36
  • 1
    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, 2014 at 18:55
  • 3
    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, 2014 at 19:19
6

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:

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. 
    – user49
     Jul 22, 2014 at 21:04 
3

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');
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, 2014 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, 2014 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, 2014 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, 2014 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, 2014 at 17:42 
-1

If you use this query then, its provide proper distinct query.

<?php  

$select = db_select('service_payment_transaction','o');
$select->distinct('o.transaction_id');
$select->innerJoin('users', 'u', 'u.uid = o.user_id');
$select->fields('o');
$select->fields('u');
$select->condition('o.service_gv_code','','!=');
$select->range($start,$page_count);
$select->groupBy('o.service_gv_code');
$select->orderBy('transaction_id', 'DESC');
$result_query = $select->execute();
Improve this answer

Your Answer

来自  https://drupal.stackexchange.com/questions/124198/how-to-db-select-distinct-on-a-particular-field-in-drupal-7