Jump to content
  • 0

Removing duplicates when searching many-to-many joining tables


Ed727

Question

I can't figure out how to search based on many-to-many relationships without getting duplicate results.

To create a many-to-many relationship in Caspio, you have to use a joining table which connects the two other tables. Each of the other tables connects to the joining table via a one-to-many relationship.

Here's an example of the issue below, based on an Events (i.e. conferences) database that uses Subject Tags.   I have an Events table (list of conferences) and a Subject table (list of Subject Tags).

Each Event will have multiple Subject Tags assigned to it. I create a joining table, and each entry in the joining table consists of an Event and one of its Subject Tags. Therefore each Event is listed multiple times, once for each Subject Tag.   An example table and relationship are below:

image.png.3828302df05c7472c3d09cce1b9033d9.png

To allow a user to search for an Event based on Subject Tags, I then create a view based on the joining table. The view pulls in all the other information for the Event (location, date, size, etc.). The search form for the user then refers to that view. However, problems arise:

1) Since each Event is listed multiple times in the joining table & view (once for each Subject Tag it has), I can't list all the Events, or all the Events filtered by something other than a subject tag, because an Event will show up multiple times.

2) I can't allow the user to search for Events using multiple Subject Tags, because if the Event has those same multiple subject tags, it will show up multiple times in the results.

3) If I use any other many-to-many relationships for Events, I need to create a separate joining table, view and search page. Thus I can't offer the user a unified searching experience.

 

I've searched all over for an answer to this issue without any luck.

 

 

 

Link to comment
Share on other sites

3 answers to this question

Recommended Posts

  • 0

Thank you for taking a look.  I've tried the grouping and pivot table features -- they provide a fix in terms of organizing all the results by distinct results, however they still list each result as part of the grouping.  And all of the data I also need to show (event location, date, etc.) show up in the sublist of the duplicates, not on the main line. This approach would work OK when each result is meaningful in and of itself (for example invoices), but in a use case of subject tags it's not OK. 

I've asked around a lot and I think that unfortunately this is not an area Caspio can handle without some extensive custom coding which can be tricky even for outside developers.

From doing some research, it appears that other platforms handle this by allowing users to directly create many-to-many relationships between tables, and the platform deals with the linking table in the background.  Perhaps this will be something Caspio will address down the road, either directly or through expanding the functionality of the List datatype.

 

 

 

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...