Search in a many-to-many. How to get distinct list?

Considering the below many-to-many relationship, what is the best (performing) way to search by Case.Name and Tags.Name?

Is it possible in an Aggregate or only Advanced Query?

This does not work because it returns duplicate Case records if a Case has multiple tags

mvp_badge
MVP

Hi Christoph,


You can group your aggregate by the distinct attributes you want to have, like this:


The group by will make the distinct you need.


Kind Regards,
João

Thanks Joao,

yes thats a good way. However, the Cases table has a lot of attributes and when I add all of them to the "group by" the performance suffers a lot.

Also what I missed to say, on the UI I want to show Cases + list of their tags. I think this comes back to the nested list, so my approach would be to use webblocks which is also causing a lot of queries and performance degradation.

As this is a common use-case I was just curious if there is a best-practice for it.

mvp_badge
MVP

Hi Christoph,


Group by indeed affects the performance of query preview in Service Studio, but it is not a reason for the query to perform considerably slower at runtime so that wouldn't be a problem. You should not notice any difference at runtime.


Having queries in every line of the list however, can be really bad for performance because a list with 20 records means 20 + 1 round-trips to database and this yes can be very costly performance wise.


Being that said, I would go with the grouping by.


Kind Regards,
João

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.