Counting Tags method?

asked111 · 2024-12-08T19:48:30.347Z

I would appreciate any suggestion to count/order tags in the most efficient way possible.

I have a songs table and each song contains tags, which is a list field. These tags simply describe the song vibe (smooth, relaxed, downtempo etc).

Users can favourite songs.

I would like to scan the users favourite songs and return the “top 3 overall tags” for the user. This would then form the basis of a recommendations system.

Hope that makes sense.
Thank you

adamhholmes · 2024-12-08T20:20:36.914Z

Have a datatype of song_tag that connects a Song to a Tag.

Have a datatype of favourite_song that connects a Song to a User.

To establish the top 3 Tags for the user, first load all of the User’s favourite songs, then do a search for song_tags [where the Song is in the list of users favourite songs]: grouped by ‘Tag’, with an aggregation of ‘Count’: sorted by Count, items until 3.

Search for song_tags
[Song is in users fav songs)

Group by Tag
[with aggregation of Count]

Sorted by Count
[descending ‘yes’]

Items until 3

asked111 · 2024-12-09T12:14:01.763Z

Hey Adam, thanks for your reply.

Is there anyway we can achieve this using the current database structure rather than having to create new datatypes? Our app is fairly mature now and got a few thousand users so don’t particularly want to reinvent the wheel (although we are planning to move the favourites system off to supabase at some point). For now though we have this very simple structure:

Songs Table : Has a “tags” LIST field on it.
Users Table: Has a “favourite songs” LIST field on it.

The users favourited songs are also already stored on a page ‘fav-list’ state (a list variable) as well, if that helps, so it’s already in memory.

Is there any decent way of returning the most used tags, via a search, without having to create new data tables?

Many thanks

adamhholmes · 2024-12-09T12:35:07.441Z

asked111:

Is there any decent way of returning the most used tags, via a search, without having to create new data tables?

There’s no way to establish it directly with a database query without having a separate datatype, but you could do the exact same thing client-side - it won’t be scalable, but neither is using lists for this in the first place, so assuming the amount of data involved here is not large, it should be ok (not ideal, but saves having to restructure your database).

User’s Favourite songs: each item’s tags

Grouped by Name
[with an aggregation of Count]

Sorted by Count
[Descending ‘yes’]

Items until 3

asked111 · 2024-12-09T16:45:26.763Z

Hey Adam, thanks again. What you have suggested is what i was originally trying, but unfortunately “grouped by” isn’t an option that I can select. See image:

Screenshot 2024-12-09 at 16.39.35664×310 19.3 KB

[also just to say, i agree that my schema isn’t the best for scalability and it’s going to get reworked at some point in the future. for now the lists are limited to small amounts and it’s all ok]

adamhholmes · 2024-12-09T22:53:07.191Z

Does that mean ‘Filter_VibeMoodTags’ is a list of texts? (you can’t use grouped by on list texts).

asked111 · 2024-12-09T23:07:14.408Z

The tags are from an option set, so i guess it’s a ‘list of options’…

code-escapee · 2024-12-09T23:11:15.870Z

asked111:

Our app is fairly mature now and got a few thousand users so don’t particularly want to reinvent the wheel

As @adamhholmes pointed out, you need a new dataset or OS. Making this change doesn’t require reinventing the wheel, and with an API workflow on a list you can convert all songs’ tags in a few minutes…

adamhholmes · 2024-12-09T23:44:26.633Z

Groupings can only be done on database objects, so you’ll need to convert your list of Tags from an option set to a Datatype.

Alternatively you could use some javascript to establish this, seeing as it’s happening on the client-side anyway.

asked111 · 2024-12-10T10:22:42.858Z

code-escapee:

As @adamhholmes pointed out, you need a new dataset or OS. Making this change doesn’t require reinventing the wheel, and with an API workflow on a list you can convert all songs’ tags in a few minutes…

That is fine but wouldn’t all the many workflows & expressions that use the existing OS method then also need to be changed? Not a task i would relish considering the way the expression currently behaves. But alas this is a different conversation for another day.

adamhholmes:

Alternatively you could use some javascript to establish this, seeing as it’s happening on the client-side anyway.

This sounds maybe like the best idea. Simply put then, I should easily be able to convert the list of tags used into a single TEXT, so then it’s just a case of counting the individual words and sorting them by amount of times they appear. I have no JS experience but would appreciate it if you can you point me in the right direction then i’ll dig deeper and try to work it out for myself. Many thanks.

adamhholmes · 2024-12-10T10:40:43.000Z

asked111:

I have no JS experience but would appreciate it if you can you point me in the right direction then i’ll dig deeper and try to work it out for myself. Many thanks.

You’e got the right idea…

I wouldn’t use a single text… but rather an array (list) of texts (strings).

Then use the ‘reduce’ method on the array to iterate over them and count the occurrence of each.