CODEWITHSUNDEEP
hibernategrails
Ryan Grow
Ryan Grow

Reputation: 149

Grails: How do I fetch a sorted list of domain objects in a many-to-many relationship with sort attribute?

My example model is a playlist, where a playlist can have many songs and a song can be in many playlists. Each mapping between playlist and song therefore has a position parameter to define the order. I have tried to create this mapping with only two domain objects as is the standard Grails many-to-many approach, but I was not able to figure out how to access the position attribute in the join table that way. Here is my latest version of the domain objects:

class Song {
    String title
    static hasMany = [ playlistSongs: PlaylistSong ]
    static constraints = { title blank: false, unique: true }
}

class Playlist {
    String name
    static hasMany = [ playlistSongs: PlaylistSong ]
    static constraints = { name blank: false, unique: true }
}

class PlaylistSong {
    Playlist playlist
    Song song
    integer position
}

The query that I have been trying to write, would be to fetch the songs in a given playlist, using the position attribute to order the result. For example, something like this:

result = Song.executeQuery("""
SELECT s.id, s.title from Song s
inner join playlistSongs pls on s.id = pls.song.id
where pls.playlist.id = (:playlist_id)""", [playlist_id: params.playlistId])

For this particular query attempt, I get an error unexpected token: on line 3 near column 45.

And while there is probably more than one way to do this, I'd like to find out which way would be preferred and is more common for Grails. Thanks!

Upvotes: 1

Views: 405

Answers (1)

grantmcconnaughey
grantmcconnaughey

Reputation: 10689

I would do the query on PlaylistSong, not Song. Here's how you could do it with a where query:

def playlistSongs = PlaylistSong.where {
    playlist.id == params.playlistId
}.list(sort: 'position', order: 'asc')

Then in your view, you can display the songs like this:

<g:each in="${playlistSongs.collect { it.song }}" var="song">
    <p>${song.title}</p>
</g:each>

Upvotes: 2

Related Questions