Mapping materialized views to models

[muellerj]

I've recently had to map a materialized view to a model and was wondering, if there's a better alternative. Here's what I came up with:

The view:

DB.create_view(:track_search, <<~EOS, materialized: true)
    SELECT
      tracks.id AS id,
      tracks.title AS title,
      tracks.track_no AS track_no,
      albums.id AS album_id,
      albums.name AS album_name,
      artists.id AS artist_id,
      artists.name AS artist_name,
      CONCAT_WS(' ', tracks.title, albums.name, artists.name) AS search_str
    FROM tracks
    LEFT JOIN albums ON tracks.album_id = albums.id
    LEFT JOIN artists ON albums.artist_id = artists.id
  EOS

The search_str is the reason I'm doing the view, but it has no relevance for the question at hand.

Fetching the rows is then done via tracks = DB[:track_search].map { |hsh| Track.from_hsh(hsh) }, since I would like work with full models in the view. The alternative intializer Track.from_hsh is defined as such:

class Track < Sequel::Model

  many_to_one :album
  many_to_one :artist
  one_to_many :playlist_memberships

  def self.from_hsh(hsh)
    without_restrictions do
      new \
        title: hsh[:title],
        album: Album.new(
          id: hsh[:album_id],
          name: hsh[:album_name],
          artist: Artist.new(
            id: hsh[:artist_id],
            name: hsh[:artist_name]
          )
        )
    end
  end

  def self.restricted_models
    [Track, Album, Artist]
  end

  def self.without_restrictions
    restricted_models.each(&:unrestrict_primary_key)
    result = yield
    restricted_models.each(&:restrict_primary_key)
    result
  end
  
  # ...
end

Especially the part about unrestricting/restricting the primary key feels clunky. Does anyone know a cleaner solution that allows initializing models from materialized view hashes?

[jeremyevans]

You don't want to use Track.new, Album.new, and Artist.new, since these are for new records, not for records that are already in the database. You can switch from new to load. However, load doesn't handle associations. So you'll probably need something like:

  def self.from_hsh(hsh)
    track = load(title: hsh[:title])
    album = track.associations[:album] = Album.load(id: hsh[:album_id], name: hsh[:album_name])
    album.associations[:artist] = Artist.load(id: hsh[:artist_id], name: hsh[:artist_name])
    track
  end

[muellerj]

Thanks for the pointer. Wouldn't that trigger three queries for each track? How would the track know its id?

[jeremyevans]

Well, it should probably be track = load(id: hsh[:id], title: hsh[:title]). I guess I missed that as your example didn't include it.

from_hsh as defined in my example should not trigger any queries.

[muellerj]

Indeed, it was just there in my head :)

Thanks for the help!

[rgalanakis]

Instead of querying the dataset for hashes and creating models, can you back the model with a view? I have not had any trouble using view names instead of table names for models. Then it's something like class Track < Sequel::Model(:track_search). Maybe I'm missing something though.

[rgalanakis]

Additionally, in this case it probably wouldn't be a good idea, because of the use of associations.

Which option does "it" refer to?

[jeremyevans]

it in this context refers to your suggestion of using the view as the model's dataset.

[rgalanakis]

I have used views with (readonly) associations in a number of projects, I haven't seen any issues- can you elaborate what you're alluding to?

[jeremyevans]

In this case, @muellerj wants to automatically populate associations from a particular query. That's not a good fit for a model dataset, which has the assumption that the values returned are columns for the table.

[rgalanakis]

Oh, I see! Thanks, I missed that. In my normal use case I wouldn't have those JOINs in the view, I would have them as normal associations, so I overlooked it here.