Workaround for selecting multiple references

Ask the community
,
1

Hello,
I’m trying to find the best workaround for the fact that we can’t select multiple “references” in a database field.

In my real-life example, I have a database of songs and am using the Genre field to ALSO contain Themes. So a Genre field might look like:
Country;ClrSng;DayBnd
The genre is Country, and the song has themes Color Song and Day Band (there’s a color in the song title and a day of the week in the band name)

You can find the existing songs list here: https://www.seanarenas.com/songs

I could make a database called Themes and add Country, ClrSng, and DayBnd to it, but there’s no way to select all of those as references.

My purpose is for a user to be able to select a theme, and see all songs that fit that theme. If they click DayBnd, they should see a list like this

728×424 48.8 KB

And if they click Kissing Songs they should see a list like this:

728×424 48.8 KB

But I took these screenshots from my Virtual DJ software, not from the Web site. All of these songs are in the database on the Wix site. I just don’t know a way to set it up to achieve my goals here; any advice?

2

Hi Sean,
what you are trying to mode is a textbook case of a “many-to-many” relationship.

  • each song has many themes
  • each theme is relevant for many songs

modeling this isn’t too hard…
I will update this post later today with a basic guide for how to achieve this.

Thanks and keep on wix-coding!

3

Many-to-many - I’m learning a lot doing this, and I’ve been working with databases since dBase II ! Looking forward to your update on this!

4

This will also be useful for the list of board games that I’m working on. See how they have different “Categories” and also different “Mechanics” ? I’ll want to have a list of Mechanics like: Co-operative Play, Action Point Allowance System, Variable Phase order - when someone clicks on one of those, they can see a list of games that have the same Mechanics. You can find this page here: https://www.seanarenas.com/games

856×1076 647 KB

5

OK, as promised, here’s an outline of how to model many-to-many relationships via wixcode.

in our specific example, we have two entities: songs, and themes, that are in a many-to-many relationship.
so we are going to create three collections:

  1. Songs: contains data relevant for each song (title, length, release date, image, …)
  2. Themes: contains data relevant for the theme (title, description, image, …)
  3. Songs_Themes: contains song-theme pairs, by using a reference to the song and another reference to the theme.
    in this collection, a song may appear more than once, and a theme may appear more than once.

now let’s discuss how to fetch the relevant data to the client so we can create a dynamic page for a song or for a theme.

let’s assume I want to create a “song” dynamic page, where I display the songs properties, as well as the list of themes it’s relevant for.

I start off regularly, creating a dynamic song page.
I get a dataset that fetches that song, and can now use databinding to design the page’s display.
so I add components for the title, duration, image, etc.

now for the interesting part - getting the themes.

I add another dataset to the page, this time bound to the songs_themes collection.
Now, I configure it to be filtered by the current song the page displays.
I do this by adding a filter condition on the song reference field, to be “the same as” the _id field value of the song in the song dataset.
setting this filter means that now the songs_themes dataset will only retrieve {song, theme} pairs that are relevant for the current song.

now that I have this second dataset configured correctly, I can use it to bind components to it.
note that this dataset now exposes the fields of both the song and the theme.

I can use a repeater, for example, to display the list of themes in a nice repeating layout.

that’s it!
the same logic can be applied for the themes dynamic page, of course.

hope this helps!

6

I like this idea because it allows me to re-import the songs list from a spreadsheet file without overwriting any data (provided the referenced song titles and database IDs remain the same, right?)

I already had the Songs database with Title, Artist, Remix, Genre (which is genre + themes and I can’t use because it combines them with semicolons), Length, BMP, and Year.
I added another datatbase called MusicThemes with ThemeShort and ThemeFull - PlcBnd and Place Band as the two fields
Then I added another database called SongThemes with Song Title and Music Theme fields. I select Song Title “(Kissed You) Good Night” and the Music Theme “KissSng”
Then selected the Song “#1 Crush” and the Music Theme “NmbrSng” (songs with numbers in the titles)

Then I went to add a song, “(God Must Have Spent) A Little More Time On You” with the intention of tagging it as “PlcBnd” (a band with a place in their name) because it’s by Alabama, but the same song title is also by N Sync. When I pull down the list to choose, I see this:


Is there a way to tell which song I’m selecting? I suspect the Alabama one is listed first, but is there a way that I can know as I add it?

Something that just occurred to me - there’s no easy way to know what song is tagged with what just from looking at the Songs database. Is there a way to display an incoming reference while viewing a database? It would be quite difficult to tell what’s tagged with what, especially since I can’t sort a database view by a reference field…

Even then, I’d have to have two windows open to databases at once, one to the Songs database, and one to the SongThemes database, and scroll each little by little and compare what songs have what references. It seems like quite the daunting task. Any ideas on how to manage this?