How do I escape a MySql reserved name in ActiveRecord Relations?

huangapple go评论70阅读模式
英文:

How do I escape a MySql reserved name in ActiveRecord Relations?

问题

I'm working with an older (rails 4) code base and I've been tasked with updating the mysql2 gem to support the latest db version. With our current version of the gem and code, everything works fine, but when upgrading I'm running into a naming conflict with the reserved word, 'groups'.

I've managed to escape it with backticks in all but one query. We call .send(name, args), and when name is :groups, we get an error. The send returns an active record relation. The relation.to_sql shows me it's all escaped but for one thing, the final 'groups' which appears to be tacked onto the end of a select * from a union of selects:

SELECT `groups`.* 
  FROM 
( 
   (SELECT `groups`.* 
      FROM `groups` 
      WHERE ...) 
   UNION ALL 
   (SELECT `groups`.* 
      FROM `groups` 
      WHERE ...) 
) groups

I've been trying to find a way to escape that last groups reference. The only way I can think of is to just turn this into Arel sql and run the query that way. Is there some kind of 'name' I can pass to the ActiveRecord relation so it gives me an escaped 'groups'?

英文:

I'm working with an older (rails 4) code base and I've been tasked with updating the mysql2 gem to support the latest db version. With our current version of the gem and code, everything works fine, but when upgrading I'm running into a naming conflict with the reserved word, 'groups'.

I've managed to escape it with backticks in all but one query. We call <modelname>.send(name, args), and when name is :groups, we get an error. The send returns an active record relation. The relation.to_sql shows me it's all escaped but for one thing, the final groups which appears to be tacked onto the end of a select * from a union of selects:

SELECT `groups`.* 
  FROM 
( 
   (SELECT `groups`.* 
      FROM `groups` 
      WHERE ...) 
   UNION ALL 
   (SELECT `groups`.* 
      FROM `groups` 
      WHERE ...) 
) groups

I've been trying to find a way to escape that last groups reference. The only way I can think of is to just turn this into Arel sql and run the query that way.
Is there some kind of 'name' I can pass to the ActiveReccord relation so it gives me an escaped groups?

Thank you so much for helping!

答案1

得分: 1

我采用创新解决方案:

  • 重命名表。如果您的应用是唯一使用该表的,则这将是理想的选择。
  • 制作表的视图并使用它。如果有其他应用程序使用该表,则这可能是一种解决方案。制作一个视图,并通过视图进行所有Rails访问。您既能拥有您的需求,又能实现它。这只是增加了一点复杂性,您可以通过简单地重命名表来避免。
英文:

I go for out of the box solutions:

  • Rename the table. If yours is the only application then this would be ideal.
  • Make a view of the table and use that. If you have other applications using the table then this might be the way to go. Make a view of it and do all rails access through the view. You get to have your cake and eat it. It just adds a little complexity that you could avoid by simply renaming the table.

huangapple
  • 本文由 发表于 2023年5月18日 04:34:24
  • 转载请务必保留本文链接:https://go.coder-hub.com/76276010.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定