Using parameterized views with Hanami repositories

Question

I prepared view in my PostgresDB called user_details. I created UserDetail entity and UserDetailRepository. Here you have shortened version of my view to visualize my problem:

CREATE VIEW user_details AS
SELECT id, user_name FROM users WHERE user_name like '$1#%'

My question is how to inject parameter using Hanami repository?

I can use raw sql in my repos which is described here http://hanamirb.org/guides/models/repositories/ but I prefer to create view in postgres using migrations. I don't want to improve query but to know if I can user parameterized queries with Hanami. Thanks for all answers

score 1 · Answer 1 · answered Jul 27 '17 at 19:53

PostgreSQL doesn't support parameterized views in the way you described. It can have views that call a function which in order can access the current session state and you can set that state just before selecting data from the view. However, I'd recommend you define a method with an argument instead

class UserRepo
  def user_details(prefix)
    root.project { [id, user_name] }.where { user_name.ilike("#{ prefix }%") }.to_a
  end
end

What you get with this is basically the same. If you want to use user_details as a base relation you can define a private method on the repo and call it from other public methods

class UserRepo
  def user_details_filtered(user_name, min_user_id = 0)
    user_details(prefix).where { id > min_user_id }.to_a
  end

  def user_details_created_after(user_name, after)
    user_details(prefix).where { created_at > after }.to_a
  end

  private 

  # Don't call this method from the app code, this way
  # you don't leak SQL-specific code into your domain
  def user_details(prefix)
    root.project { [id, user_name] }.where { user_name.ilike("#{ prefix }%") }
  end
end

Using parameterized views with Hanami repositories

1 Answers1