Skip to content

Instantly share code, notes, and snippets.

@bonyiii
Created March 6, 2012 10:21
Show Gist options
  • Save bonyiii/1985562 to your computer and use it in GitHub Desktop.
Save bonyiii/1985562 to your computer and use it in GitHub Desktop.
How to make an activerecord model use a tempory table or view dynamically per query
# Creating view or temp table
ActiveRecord::Base.connection.execute("CREATE TEMPORARY TABLE users#{args.offset} SELECT * FROM users LIMIT #{limit} OFFSET #{(args.offset - 1) * limit}")
# or
ActiveRecord::Base.connection.execute("CREATE VIEW users#{args.offset} AS SELECT * FROM users LIMIT #{limit} OFFSET #{(args.offset - 1) * limit}")
### Giving relation back, suitable for find_in_batches
#User.arel_table.name ='users2'
# This changes table name in FROM part of the query
# User.scoped.find_in_batches
# ActiveRecord::StatementInvalid: Mysql2::Error: Unknown table 'users': SELECT `users`.* FROM `users2` WHERE (`users2`.`id` >= 0) ORDER BY users.id ASC LIMIT 1000
#User.table_name = 'users2'
# This changes the SELECT part
# 1.9.2p290 :022 > User.all.count
# ActiveRecord::StatementInvalid: Mysql2::Error: Unknown table 'users2': SELECT `users2`.* FROM `users`
# So both needed, this way queries will use temp table or view.
User.arel_table.name ='users2'
User.table_name = 'users2'
User.all
# Joins may behave strangely afterwards
###
### Not giving relation back ###
User.find_by_sql('SELECT * FROM USERS')
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment