How to define column that is not actually related to another table by keys, but by keywords

Question

I am using JPA to see how helpful it will be, rather than using straight SQL, and I realized today that one of my columns is incorrect.

I have a playlist application. Each playlist has a title and a list of keywords that signify the songs to include.

So, I may have this as one row, where there are three keywords. Exercise steady-beat_country hard_rock classic_rock

If I add a new song that has one of these keywords applied to it, I want it to be pulled up when I select this playlist.

I could just go through all the playlists that have this keyword and relate the new song with the playlist, but that seems to be very inefficient, if I have several users each with many playlists, if the song can be played by all the users.

I am curious if I should just do this in my @Entity model, which is written in Scala:

@transient
var songs : java.util.List[Song] = new java.util.ArrayList[Song]()

@OneToMany{val cascade=Array(CascadeType.ALL), val mappedBy="playlist"}
var keywords : java.util.List[Snippet] = new java.util.ArrayList[Snippet]()

Then I could just load the keywords that are actually related to the playlist and then call a named query that can load up the songs by a list of keywords.

Or, is there a way to define something like this in JPA so I don't have to make an extra call?

UPDATE:

Tonight I test this, but I am wondering if I could set up my named query to do this query, so it would be something like:

SELECT Playlist p, Songs s WHERE p.user = :user AND s.keywords IN (p.keywords)

This code is a bit flawed as the IN I need to look up how I can do it in JPA, I may need to store the keywords as comma-delimited, but I am not certain yet how I can have the songs fill in the song property for playlist.

Answer 1

You have a many-to-many relationship between your songs and keywords.
I believe your query should be select p from Playlist p, Songs s where p.user = :user and s.keywords member of p.keywords although I'm not 100% sure you can do a member of operation between two collections. You might have to use a subquery to grab all the p.keywords and the ANY expression to do that. See this article for more.

After your answer, I realized that a playlist was not the list of songs you're getting from the JPQL. You're trying to get a field set in the playlist object.

If you're thinking that JPA is just an easy way to do DB work. You're sorta right. You can't really do what I believe your doing in any OO language. You can't define a field as the result of a query.

What you can do, however is get the playlist definition from the user, name, and keywords that are part of the playlist. Then run a query looking for songs with those keywords, and then in java do something like curPlaylist.addAll( queryResult); em.merge(curPlaylist);. This assumes that you have your cascades set right of course. Now, when you get the playlist from the entity manager, you'll also pull back the playlist.

If your looking for something more dynamic, where the list is automatically updated, then you can run the query every time you need a list matching the playlist criteria.