Ruby on Rails Active Record数据库常用操作
文档地址：
https://freed.gitee.io/rails-guides/active_record_querying.html
创建
## 记录日志
Log.create(logtype: 2, email: current_user.email, user_id: current_user.cas_uid,
url: '/api/exploit/rule_list',
info: "product: #{products} records: #{ret_rule.nil? ? 0 : ret_rule.size}",
ip: env["HTTP_X_REAL_IP"] || env["REMOTE_ADDR"])
批量插入
# 批量插入数据库
black_ips = ['127.0.0.1','127.0.0.2']
begin
# 批量插入
time = Time.now
BlackIp.bulk_insert(:ip, :created_at, :updated_at) do |black_ip|
black_ip.set_size = 1000
black_ips.each do |ip|
black_ip.add [ip, time, time]
end
end
rescue Exception => e
puts "#{Time.now.strftime('%Y-%m-%d %H:%M:%S')} #{self.jid} save blackip Error: #{e.message}"
end
判断是否存在
IpList.exists?(ip: "#{env["HTTP_X_REAL_IP"] || env["REMOTE_ADDR"]}")
Ruby on Rails 日期查询方法
查询近超过1个小时的数量
Order.where(' created_at <= ? ', DateTime.now - 1.hours).count
生成sql:
SELECT COUNT(*) FROM order WHERE ( created_at <= '2022-05-20 17:28:10.111545' )
查询近三个月的数量
Order.where(' created_at >= ? ', DateTime.now - 3.month).count
生成sql:
SELECT COUNT(*) FROM order WHERE ( created_at >= '2022-02-20 18:26:57.407358' )
查询上个月的数量
Order.where(created_at: (DateTime.now - 1.month).beginning_of_month..DateTime.now.beginning_of_month).count
生成sql:
SELECT COUNT(*) FROM order WHERE (order.created_at BETWEEN '2022-04-01 00:00:00' AND '2022-05-01 00:00:00')
查询本月的数量
Order.where(' created_at >= ? ', DateTime.now.beginning_of_month).count
生成sql:
SELECT COUNT(*) FROM order WHERE (order.created_at BETWEEN '2022-04-01 00:00:00' AND '2022-05-01 00:00:00')
近一周
Order.where(' created_at >= ? ', DateTime.now - 7.day).count
生成sql:
SELECT COUNT(*) FROM tasks WHERE ( created_at >= '2022-05-13 18:21:58.804635' )
修改超过一个小时的数据
# 修改超过一个小时的任务
# past_time = (n_time - 1.hours).strftime("%Y-%m-%d %H:%M:%S")
# => "2021-08-05 20:55:31"
CategoryStatistic
.where("state = 'init' and end_at IS NULL ")
.where("begin_at<=?", DateTime.now - 1.hours)
.where("start_computing_time IS NULL")
.update_all(state: FAILED, end_at: n_time, updated_at: n_time)
运行结果：
UPDATE `category_statistics` SET `category_statistics`.`state` = 'failed', `category_statistics`.`end_at` = '2021-08-05 22:27:45', `category_statistics`.`updated_at` = '2021-08-05 22:27:45' WHERE (state = 'init' and end_at IS NULL ) AND (begin_at<='2021-08-05 21:27:45.684015')
first / last 查询一条
ret = client = Client.find(10)
ret = Client.where("product = ? and published = 1", products).select("producturl").first
ret = Client.where("product = ? and published = 1", products).select("producturl").last
#查列，匹配第一条
res = BlackIp.where(ip:"106.83.249.151").pluck(:is_china).first
(0.7ms) SELECT `black_ips`.`is_china` FROM `black_ips` WHERE `black_ips`.`ip` = '106.83.249.151'
in 查询
client = Client.find([1, 10])
# SELECT * FROM clients WHERE (clients.id IN (1,10))
# 如果所提供的主键都没有匹配记录，那么 find 方法会抛出 ActiveRecord::RecordNotFound 异常。
IpInfo.select(:ip).where(ip: ["114.223.55.93","114.223.55.95"])
IpInfo Load (1.1ms) SELECT `ip_infos`.`ip` FROM `ip_infos` WHERE `ip_infos`.`ip` IN ('114.223.55.93', '114.223.55.95')
distinct_rules = client.select(:id, :name, :age, :level :product).where(published: true).where("product in (:key) or en_product in (:key) ", key: products)
if distinct_rules.present?
distinct_rule_jsons = distinct_rules.map { |rule| { "id" => rule.id, "product" => rule.product, "name" => rule.name, "age" => rule.age } }
data = distinct_rule_jsons.map { |obj| obj["product"] }
else
data
end
puts "data #{data}"
not in 查询
BlackIp.where("ip not in (:key) ", key: ["114.223.55.93","114.223.55.95"]).pluck(:ip)
(32.7ms) SELECT `black_ips`.`ip` FROM `black_ips` WHERE (ip not in ('114.223.55.93','114.223.55.95') )
BlackIp.where.not(ip: ["114.223.55.94","114.223.55.92"]).pluck(:ip)
(47.0ms) SELECT `black_ips`.`ip` FROM `black_ips` WHERE (`black_ips`.`ip` NOT IN ('114.223.55.94', '114.223.55.92'))
or 查询
q_product = 'xxx有限公司' + "%"
ret = Client.where("(product like ? or company like ?) and published = 1", q_product, q_product).limit(5)
or like
@client_title, @other_titles = [], []
clients = Client.where(published: true).where("product like :key or product like :key2 or company like :key or company like :key2", key: "#{q}%", key2: "%#{q}")
client = []
clients.first(3).each do |r|
client << %Q[app="#{r.product}"]
@client_title << r.product
end
clients.offset(3).each do |r|
@other_titles << r.product
end
@keyword = params[:keyword].to_s.strip
@rs = current_user.rules.where("company like :key or product like :key or rule like :key or producturl like :key", key: "%#{@keyword}%").paginate(:page => params[:page],
:per_page => 20).order('id DESC')
ret_rule = Rule.where("(product like ? or company like ?) and published = 1", q_product, q_product).limit(limit.to_i)
total = ret_rule.nil? ? 0 : ret_rule.size
if ret_rule.nil?
{error: true, errmsg: "not found product list"}
else
xproduct_list = []
ret_rule.each { |r|
product_list << r["product"]
}
{error: false, data: product_list}
end
in or in
distinct_rules = client.select(:id, :name, :age, :level :product).where(published: true).where("product in (:key) or en_product in (:key) ", key: products)
if distinct_rules.present?
distinct_rule_jsons = distinct_rules.map { |rule| { "id" => rule.id, "product" => rule.product, "name" => rule.name, "age" => rule.age } }
data = distinct_rule_jsons.map { |obj| obj["product"] }
else
data
end
puts "data #{data}"
sum 相加
list = Client.where("change_coin > 0").order(id: :desc)
in_total_coin = Client.where(category: "in").sum(:change_coin)+Order.where(state: 1, subject: 'F币').sum(:amount)
out_total_coin = Client.where(category: "out").sum(:change_coin)
批量修改
Client.update_all(state: "init")
Client.where(id: init_ip_infos.pluck(:id)).update_all(state: "init")
Client.where(id: @attrs.map{|obj| obj[:rule_record_id]}).update_all(state: "success")
Client.where("isvip=1 and vip_level=0").update_all(vip_level: 1)
批量删除
def self.update_rules
path = "/Users/zcy/Downloads/rule.txt"
new_products = open(path).readlines.map{|ip| ip.strip}
group_rules = Rule.all.in_groups_of(5000).map{|obj| obj.compact}
group_rules.each do |rules|
rule_products = rules.map{|rule| rule.product}
delete_products = rule_products - new_products
Rule.where(product: delete_products).delete_all
end
end
puts "restart_task66666677------------>"
region = ["湖北", "山西", "福建","海南"]
sheet_category = 'wangluo'
Record.where(region: region, sheet_category: sheet_category).delete_all
join
Rule.joins(:categories).select("categories.title, rules.id, rules.product, rules.rule").where(rules: {published: true})
total = et_rule.nil? ? 0 : ret_rule.size
titles = ret_rule.group_by(&:title)
exists
IpWhitelist.exists
[Haima的博客]
http://www.cnblogs.com/haima/
