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:
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.
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.
Question
Ed727
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:
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
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.