How do I convert a MySQL database view to Rails using Active Record Query Interface

Question

I have a Rails 3.2 app with a model to a MySQL database view. It works fine, but when going through migrations to AWS and Heroku, I'm realizing having database structures like views outside of Rails is not a good thing since Rails can't create views ( as far I know ) as part of the migration process, which I want to start leveraging.

Anyway, I don't understand how to replicate the view in Rails using the Active Record Query Interface.

For my example, the view joins a historys table and an items table, where the item id is a foreign key in the history table.

So my Rails app has a model for items, and historys, and am looking for a way to create a query that joins the table, much like a database view does.

Eyeslandic · Accepted Answer · 2014-04-26 22:19:59Z

2

You can create views in your migrations quite easily. See more here http://api.rubyonrails.org/classes/ActiveRecord/Migration.html

def up
    execute 'create view my_view as select .... '
end
def down
    execute 'drop view my_view'
end

You can also replicate views by creating a model class inheriting from ActiveRecord::Base and set the table_name

class MyView < ActiveRecord::Base
  self.table_name = 'my_view'
end

You can also just use the ActiveRecord query interface to replicate what your view is doing by doing something like...

items = User.joins(:history).all.order(:some_column)

See more here http://guides.rubyonrails.org/active_record_querying.html#joining-tables

answered Apr 26, 2014 at 22:19

Eyeslandic

14.9k13 gold badges44 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mark Over a year ago

Thanks Iceman. And if I want attributes/columns from history as well as items, I would need to add the following into the join, right? select("items.*, historys.*") Without this, it appears to only return the items attributes.

Eyeslandic Over a year ago

You can also look into the find_by_sql method if you need something special. apidock.com/rails/ActiveRecord/Base/find_by_sql/class

Collectives™ on Stack Overflow

How do I convert a MySQL database view to Rails using Active Record Query Interface

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related