Group by users.name

Posted by mp on January 15, 2009 at 4:00pm

hello

D6, views2

to explain my problem will take some time, so be patient ;)

i have a taxonomy and it ́s about fish ---> genus, species, subspecies, population.
on one site i want people to search after fishkeepers, so i made exposed filters where you can enter one or all of the terms (genus, species, subspecies, population).
if you view the site (without searching) then you get the exposed filters and below all fishkeepers. the problem is, if someone has 2 fish, then he is listed 2 times ... 10 fish - his name is 10x in the list

a guy, who i know wrote a code to remove double entries:
$users = array();
$usernames = array();
echo count($rows).'
';
foreach ($rows as $key => $row) {
if (!in_array($row['name'], $usernames)) {
$users[$key] = $row;
$usernames[] = $row['name'];
}
}
unset($usernames);

this works and you see every fishkeeper only one time, BUT :)

if you set in views, that you want 25 users / page then you get the wrong amount users per page, because views get a "wrong number". the user is listed 1 time, if he has 10 fish, then it counts 10 and this is what the "pageviewer" gets

the solution is to add the mySQL command "Group by users.name", but there is no way to set this in views.

can anyone help? i hope you understand my bad english :)

best wishes
markus

Categories: group by, mysql, views

Comments

Bump...

Posted by DeeZone on January 15, 2009 at 4:52pm

DeeZone's picture

Just leaving a comment in order to track this thread as I too am interested in how to inject additional custom SQL into a View entry.

When you edit your View in

Posted by Garrett Albright on January 15, 2009 at 10:54pm

Garrett Albright's picture

When you edit your View in the GUI, look in the Basic Settings area and try setting the Distinct value to Yes. That might be what you're looking for.

The Boise Drupal Guy!

Distinct is set to YES

Posted by mp on January 16, 2009 at 7:25am

mp's picture

distinct is set to yes, doesn ́t work
it is set in both displays - default and page

www.multivitamedia.at
www.ding-dong.at
www.breeders-international.com

Your last comment about

Posted by dwees on January 19, 2009 at 1:44pm

dwees's picture

Your last comment about "there is no way to do this in views" is actually incorrect. You can use hook_views_query_alter to add a GROUP BY clause before the query is run in a custom module.

Implement hook_views_query_alter in your module (do a Google search for the function arguments).
print out some debugging information about the $query variable.
Use #2 to inform you as to how to add your "GROUP BY users.name" clause.

I've done something similar to this before.

Dave

Bingo!

Posted by DeeZone on January 19, 2009 at 5:08pm

DeeZone's picture

Thanks dwees, I knew the wisdom was out there somewhere :)

thanks, worked for me too

Posted by mp on February 2, 2009 at 8:08am

mp's picture

thanks, worked for me too :)

www.multivitamedia.at

www.multivitamedia.at
www.ding-dong.at
www.breeders-international.com

little problem since

Posted by mp on March 4, 2009 at 1:01pm

mp's picture

little problem since views-update to 2.3

i had a little script running for groupbyuser

<?php

function groupbyuser_views_query_alter(&$view, &$query) {
 // add groupby
 if ($view->name == 'myview') {
  $query->groupby[] = 'users_name';
 }
}

and after the update the script doesn ́t work - does anyone know why?

greets

www.multivitamedia.at
www.ding-dong.at
www.breeders-international.com

Maybe the alias for the user

Posted by dwees on March 4, 2009 at 8:59pm

dwees's picture

Maybe the alias for the user name field changed somehow? Try debugging the view and see if that's what happened.

Dave

hi how can i debug this?

Posted by mp on March 7, 2009 at 12:22pm

mp's picture

how can i debug this? i ́m a screendesigner and not a coder, i got this script from a coder

greets

www.multivitamedia.at
www.ding-dong.at
www.breeders-international.com

Try using

Posted by dwees on March 8, 2009 at 12:11am

dwees's picture

Try using this:

<?php

function groupbyuser_views_query_alter(&$view, &$query) {
 // add groupby
 if ($view->name == 'myview') {
  drupal_set_message('<pre>' . print_r($query, TRUE) . '</pre>');
  $query->groupby[] = 'users_name';
 }
}

?>

This should spit out the entire query in a reasonably formatted way, which you can try and analyze yourself, or post back here.

Dave

hi thanks, this works

Posted by mp on March 8, 2009 at 2:57pm

mp's picture

thanks, this works great
you can see the output at http://drupalbin.com/8360
users_name still exists ... and i saw, that the groupby array is empty

greets

www.multivitamedia.at
www.ding-dong.at
www.breeders-international.com

Yeah if you reorder the two

Posted by dwees on March 9, 2009 at 2:13am

dwees's picture

Yeah if you reorder the two lines inside the if() then statement, the group_by array should have your new addition. Not sure why it won't work then, seems like a bug to me? Try searching the issue queue for Views 2.3 and if you don't see your problem, describe it in detail there. I'm out of ideas.

Dave