I have a Grid that loads as a search with a checkbox selection for a list of groups. In those groups are lots of people who can be members of multiple groups. My search works great but I get duplicate listings for users if groups are checked and they are a member of more than one group. So I tried to add do a little down and dirty hack and use the sc_select_where(add) to add a GROUP BY UserID
This did work, however when I do this the pagination becomes very bugged and will only show me 1 of 2 pages but clicking the second page only adds 1 record to the bottom of the list and removes one from the top. I can remove the pagination but with a possibility of over 22k users that result takes way to long to load on the page. Is there another way to add a GROUP BY or fix the pagination issue?
Many Thanks
Re: Grid based on Many to Many using Group By
Is there any way to add a traditional GROUP BY to the SQL SELECT ? I only want to see unique records.
Re: Grid based on Many to Many using Group By
I have been working on this for several days. Is there possibly another approach I can take to this issue. Maybe something someone can think of to try?
Re: Grid based on Many to Many using Group By (SOLVED)
OK I have finally solved this. However, I would like to note that putting an select_where(add) in any of the Grid->Search->Events Does not work. I checked this many times despite the documentation saying it will.
In order to get unique results you must add the GROUP BY to the “SQL Select Statement” In the SQL section. This seemed odd to me as I thought it would not give me a full list of results as the group by would exclude some of the group associations. However, this is not the case. It does indeed give listings for each group individually and also exclude duplicates on a multi-select search without messing up the pagination.