天道酬勤,学无止境

squeel

Ordering nested has_many :through by count of associations

I'm trying to order Tags by order of the descending frequency of their association with Users of a specific Group. (ActiveRecord & Rails 3.2 - I also have Squeel installed if that helps!) Users have Tags, Groups have Users. Groups thus have Tags through Users. class Group < ActiveRecord::Base has_many :users_groups # join table has_many :users, through: :users_groups has_many :tags, through: :users class User < ActiveRecord::Base has_many :users_groups # join table has_many :groups, through: :users_groups has_many :taggings # join table has_many :tags, through: taggings class Tag <

2021-11-27 02:53:32    分类:问答    ruby-on-rails   ruby-on-rails-3   activerecord   arel   squeel

使用 SET 将复杂的 SQL 查询转换为 Arel 或 Squeel(Translate complex SQL query using SET to Arel or Squeel)

问题 如何使用 AREL 或 Squeel 翻译给定的查询: SET @start = '2013-05-14'; SET @end = '2013-11-01'; SET @days = DATEDIFF(@end, @start); SET @UnusedDays = 0; SELECT @UnusedDays := DATEDIFF(end_at,@end) FROM PERIODS WHERE (@end > start_at AND @end <= end_at); SELECT @UnusedDays := @UnusedDays + DATEDIFF(@start, start_at) FROM PERIODS WHERE (@start >= start_at AND @start < end_at); SELECT @days + @UnusedDays - SUM(DATEDIFF(end_at, start_at)) AS Shortfall FROM PERIODS WHERE (@start >= start_at AND @start < end_at) OR (end_at < @end AND start_at > @start) OR (@end > start_at AND @end <= end_at); 将它用作原始 SQL

2021-11-09 15:06:15    分类:技术分享    sql   ruby-on-rails   arel   squeel

Translate complex SQL query using SET to Arel or Squeel

How can I translate the given query using AREL or Squeel: SET @start = '2013-05-14'; SET @end = '2013-11-01'; SET @days = DATEDIFF(@end, @start); SET @UnusedDays = 0; SELECT @UnusedDays := DATEDIFF(end_at,@end) FROM PERIODS WHERE (@end > start_at AND @end <= end_at); SELECT @UnusedDays := @UnusedDays + DATEDIFF(@start, start_at) FROM PERIODS WHERE (@start >= start_at AND @start < end_at); SELECT @days + @UnusedDays - SUM(DATEDIFF(end_at, start_at)) AS Shortfall FROM PERIODS WHERE (@start >= start_at AND @start < end_at) OR (end_at < @end AND start_at > @start) OR (@end > start_at AND @end <=

2021-11-08 03:00:01    分类:问答    sql   ruby-on-rails   arel   squeel

Squeel 请求和 Heroku、Postgres:使用 GROUP BY 和 ORDER 时出错(Squeel request and Heroku, Postgres: Error using GROUP BY and ORDER)

问题 我正在用 mysql 开发我的应用程序,但我正在使用 Heroku 来部署它并被迫使用 PG。 我对以下语句有疑问: <% current_user_savings = Saving.where{user_id == my{current_user}} %> <% @latest_savings = Saving.where{product_id.not_in(current_user_savings.select{product_id})}.group{product_id} %> 所以它可以在我的计算机上运行,​​但是在部署到 heroku 时,我遇到了以下问题: ActionView::Template::Error (PGError: ERROR: column "savings.id" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: SELECT "savings".* FROM "savings" WHERE "savings"."product_... SELECT "savings".* FROM "savings" WHERE "savings"."product_id" NOT IN (SELECT "savings"."product_id"

2021-09-23 16:27:09    分类:技术分享    postgresql   heroku   squeel

Squeel request and Heroku, Postgres: Error using GROUP BY and ORDER

I am developing my application with mysql but I am using Heroku to deploy it and am forced to use PG. I have an issue with the following statement: <% current_user_savings = Saving.where{user_id == my{current_user}} %> <% @latest_savings = Saving.where{product_id.not_in(current_user_savings.select{product_id})}.group{product_id} %> So it work on my computer but when deploying to heroku I have the following issue : ActionView::Template::Error (PGError: ERROR: column "savings.id" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: SELECT "savings".* FROM "savings"

2021-09-23 14:16:39    分类:问答    postgresql   heroku   squeel

Squeel 将字符串评估为连接的关键路径(Squeel evaluating a string as a keypath for joins)

问题 这是我真正想做的事情: Outage.joins{eval "unit.plant"} 我将有一个表示squeel keypath的字符串。 我想将此字符串发送到 join 语句中。 但这是我得到的错误: #<Class:0x639e328>: unknown class: Squeel::Nodes::Function 我试过了: Outage.joins{"unit.plant"} 但这不起作用......我错过了什么? 我在 github 页面上没有看到任何关于此的文档:https://github.com/ernie/squeel/ 感谢您的任何帮助! 更新:我发现了一种非常令人困惑的方法来实现这一点: ("unit.plant".split ".").map{ |x| x.to_sym }.reverse.inject{ |acc, x| { x => acc } } 这将输出: {:unit=>:plant} 这可以传递到 joins squeel 函数中。 我确信必须有比构造嵌套符号更好的方法:( 回答1 怎么样 Outage.joins{Squeel::Nodes::KeyPath.new("unit.plant".split("."))} 来源:KeyPath 文档 回答2 替代解决方案,它可以与外部/内部链接: strs = "unit.plant"

2021-09-11 05:48:14    分类:技术分享    ruby-on-rails   ruby   ruby-on-rails-3   closures   squeel

如何在 squeel 中动态处理 where 条件?(How do I handle the where conditions dynamically in squeel?)

问题 从这个输入:{'hearing' => 1} 我需要生成这个查询 Score.joins(:target_disability).where{ (target_disabilities.name == 'hearing') & (round(total_score) >= 1) } 从这个输入是 {'hearing' => 1, 'mobility' => 2},我需要生成这个: Score.joins(:target_disability).where{ (target_disabilities.name == 'hearing') & (round(total_score) >= 1) | (target_disabilities.name == 'mobility') & (round(total_score) >= 2) } 等等... 这如何概括? 因为我的输入有时有 3 或 4 个键...有时是 1... 回答1 假设您的哈希在my_params : @scores = Score.joins(:target_disability).where do my_params.map{|k,v| (target_disabilities.name==k) & (round(total_score)>=v) }.inject(:|) end

2021-09-11 01:02:57    分类:技术分享    ruby-on-rails   squeel

Squeel evaluating a string as a keypath for joins

Here's what I'd really like to do: Outage.joins{eval "unit.plant"} I'm going to have a string that represents a squeel keypath. I'd like to send this string into a join statement. But here's what I get as an error: #<Class:0x639e328>: unknown class: Squeel::Nodes::Function I've tried: Outage.joins{"unit.plant"} But that does not work... what am I missing? I don't see any documentation on this on the github page: https://github.com/ernie/squeel/ Thank you for any help! Update: I've found a really confusing way to pull this off: ("unit.plant".split ".").map{ |x| x.to_sym }.reverse.inject{ |acc

2021-09-03 14:30:22    分类:问答    ruby-on-rails   ruby   ruby-on-rails-3   closures   squeel

How do I handle the where conditions dynamically in squeel?

From this input: {'hearing' => 1} I need to generate this query Score.joins(:target_disability).where{ (target_disabilities.name == 'hearing') & (round(total_score) >= 1) } From this input is {'hearing' => 1, 'mobility' => 2}, I need to generate this: Score.joins(:target_disability).where{ (target_disabilities.name == 'hearing') & (round(total_score) >= 1) | (target_disabilities.name == 'mobility') & (round(total_score) >= 2) } And so on... How can this be generalized? Because my input sometimes has 3 or 4 keys... sometime 1...

2021-09-02 02:55:35    分类:问答    ruby-on-rails   squeel

使用 Squeel 时,您可以有条件地在 where 块中添加子句吗?(Can you add clauses in a where block conditionally when using Squeel?)

问题 首先,我使用 Rails v3.2.9 和 Squeel 1.0.13,这是我想要做的: 我想使用三个识别信息中的任何一个来搜索客户 - 姓名、出生日期 (dob) 和社会保险号 (sin)。 结果集必须包括具有任何标识符的任何记录 - 条件的 OR。 我之前在 Squeel 中做过这个,它看起来像: scope :by_any, ->(sin, name, dob){ where{(client.sin == "#{sin}") | (client.name =~ "%#{name}%") | (client.dob == "#{dob}")} } 只要我提供所有标识符,这就可以正常工作。 但是如果我只有一个名字呢? 上述范围导致: SELECT "clients".* FROM "clients" WHERE ((("clients"."sin" IS NULL OR "clients"."name" ILIKE '%John Doe%') OR "clients"."dob" IS NULL)) 这包括 sin 为 null 的客户端集和 dob 为 null 的客户端集以及名称为“John Doe”的请求客户端集。 所以输入我尝试有条件地向where块添加子句。 起初,我尝试使用 nil 来检查值? 方法: def self.by_any (sin, name

2021-08-11 06:03:37    分类:技术分享    ruby-on-rails   ruby   squeel