enter image description hereI went through all solutions found on the web.

Both Distinct or Aggregation do not work at all in my case. Is there a way to remove the duplicates using hook_query_alter() ?

My sql query run in the view is the following:

SELECT node.changed AS node_changed, node.nid AS nid, SUM(search_index.score * search_total.count) AS score
{node} node
LEFT JOIN {search_index} search_index ON node.nid = search_index.sid
LEFT JOIN {search_total} search_total ON search_index.word = search_total.word
INNER JOIN {search_dataset} search_dataset ON search_index.sid = search_dataset.sid AND (search_index.type = search_dataset.type)
WHERE ((( (search_index.type = 'node') AND (search_dataset.data LIKE '% saadan %' ESCAPE '\\') AND (search_dataset.data LIKE '% goer %' ESCAPE '\\') AND (search_dataset.data LIKE '% du %' ESCAPE '\\') ))AND(( (node.status = '1') AND (node.type NOT IN  ('nodetype')) )))
GROUP BY search_index.sid, score, node_changed, nid
HAVING (( (COUNT(*) >= '2') ))
ORDER BY node_changed DESC

Last note:

I am also aware of the solution the removes content by hooking into hook_views_pre_render() but I do not really consider this a solution since it cracs up your results.

The query works perfectly if I replace 'GROUP BY search_index.sid, score, node_changed, nid' with GROUP BY nid. How do I achieve this?

  • Rather than removing the duplicates, I would ask, why are the results duplicated in the first place? If you have an overly complex query that fetches things twice, making it even more complicated (and slow) to remove the same results again, seems like an incorrect solution.
    – Letharion
    Commented Jul 22, 2013 at 12:50
  • @Letharion that is what I am trying to do here. Find a proper query that does not provide the duplicates. I will also be attaching a screen of my setting in 1' Commented Jul 22, 2013 at 13:03

2 Answers 2


Hope this will help you,

Using Distinct or Aggregation you can solve your problem. You can apply GROUP BY or DISTINCT to remove duplicate records.

Just check it out the steps.

Using Distinct

  • The simple way to achieve distinct values in view is to make follow:
    • Go to your view
    • In Advanced » OTHER section click on "Query setting"
    • Check "Distinct" check-box.

Using Aggregation

  • You can apply GROUP BY by enabling views aggregation settings to remove duplicate records.
    • Go and edit your view
    • In Advanced » OTHER section by enabling Use aggregation: Yes
    • In FIELDS or FILTER CRITERIA section, select and apply Aggregation settings for which fields you want to group by or distinct.

What actually worked is the following

function mymodule_query_alter($query) {

  if (isset($query->alterMetaData)) {
    if (isset($query->alterMetaData['view'])) {
      if($query->alterMetaData['view']->name == 'node_search') {
        $fields =& $query->getGroupBy();
        // Tried various fields to check which was the field creating the problem.

Answer found at http://www.brenthartmann.com/blog/how-use-hookqueryalter-drupal-7

PS: In order to check the sql query that produces your view, you need to go to admin/structure/views/settings and enable "Show the SQL query".


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.