go-music-player/gmus-backend/pkg/repository/player.go

package repository

import "github.com/jmoiron/sqlx"

func GetNextSongId(db *sqlx.DB, prevSongId int64) (nextSongId int64, err error) {
	err = db.QueryRowx(
		`
    select coalesce(id_next, 0) as id from (
      select
        prev.track_number, prev.title, prev.artist, prev.album
          ,coalesce(next_artist.id, prev.id_next) as id_next
      from (
        select
          prev.track_number, prev.title, prev.artist, prev.album
            ,coalesce(next_album.id, prev.id_next) as id_next
        from (
          select
            prev.track_number, prev.title, prev.artist, prev.album
              ,coalesce(next_title.id, prev.id_next) as id_next
          from (
            select
              prev.track_number, prev.title, prev.artist, prev.album
                ,next_track_number.id as id_next
            from (
              select track_number, title, artist, album from songs
              where id = $1
            ) prev

            left join songs next_track_number on 1=1
              and next_track_number.artist = prev.artist
              and next_track_number.album = prev.album
              and next_track_number.track_number > prev.track_number

            order by next_track_number.track_number
            limit 1

          ) prev

          left join songs next_title on 1=1
            and prev.id_next is null
            and next_title.artist = prev.artist
            and next_title.album = prev.album
            and next_title.track_number is null
            and next_title.title > prev.title

          order by next_title.title
          limit 1

        ) prev

        left join songs next_album on 1=1
          and prev.id_next is null
          and next_album.artist = prev.artist
          and next_album.album > prev.album

        order by next_album.album, next_album.track_number, next_album.title
        limit 1
      ) prev

      left join songs next_artist on 1=1
        and prev.id_next is null
        and next_artist.artist > prev.artist

      order by next_artist.artist, next_artist.album, next_artist.track_number, next_artist.title
      limit 1
    ) result
    `,
		prevSongId,
	).Scan(&nextSongId)

	return
}