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

1
  • 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 4

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');
4
  • 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:

1
  • 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');
5
  • 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. 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')) 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();
1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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