3

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
FROM 
{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
LIMIT 10 OFFSET 0 

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 Jul 22 '13 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' – Potney Switters Jul 22 '13 at 13:03
5

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.
        unset($fields['score']);
        $query->groupBy('nid');
      }
    }
  }
}

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

3

Hope this will help you,

In Drupal 7, 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.
  • 1
    "Both Distinct or Aggregation do not work at all in my case." :) – Potney Switters Jul 22 '13 at 13:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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