Created
January 20, 2012 15:24
-
-
Save semind/1647875 to your computer and use it in GitHub Desktop.
ArelとかRailsのSQLまわりのメモ
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#////////// Arel /////////// | |
### whereを使って完全一致の比較を行う ### | |
Model.where(:column => param) | |
### Arelは常に結果を配列で返すようなので一つだけとりだしたい場合(ユニークキーで検索する時とか)は.firstを使うと良い ### | |
Model.where(:column => param).first | |
### whereを使って大小の比較を行う ### | |
Model.where('column > param') | |
### groupby してカウント ### | |
Model.count(:group => :column) | |
### datetimeをDATE_FORMATを使ってgroupbyしてカウント (年でgroupby) ### | |
Model.count(:group => "DATE_FORMAT(colmun, '%Y')") | |
### ModelAをModelBと(inner)join ### | |
ModelA.joins(:modelb) | |
ModelA.joins(:modelb).to_sql | |
# => "SELECT `modelas`.* FROM `modelas` INNER JOIN `modelbs` ON `modelbs`.`id` = `modelas`.`modelb_id`" | |
#////////// mysql2 /////////// | |
### 接続してselect | |
client = Mysql2::Client.new(:host => host, :username => user_name, :password => password, :database => database) | |
client.query("SELECT * FROM example") | |
### クエリに与える変数を事前にエスケープ | |
escaped = client.escape(param) | |
client.query("SELECT * FROM example WHERE param='#{escaped}'") |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment