首页 \ 问答 \ 无法使用Rails中的find_by_sql方法正确构建查询(Not able to build query correctly using the find_by_sql method in Rails)

无法使用Rails中的find_by_sql方法正确构建查询(Not able to build query correctly using the find_by_sql method in Rails)

我有以下查询,我试图使用IN子句基于多个部门ID获取部门表数据,但我在Rails服务器日志中看到的查询不正确,因此数据获取失败。 报价问题。

我在这里尝试了两种不同的选项,但由于缺少引号或额外的引号,它们都失败了。

选项1(在CSV转换期间添加引号)

@depts = current_user.depts             
puts @depts                 # prints    IT Account Finance

# converting to CSV string here
@dept_ids_csv = @depts.gsub(" ", "','")     
puts @dept_ids_csv          # prints    IT','Account','Finance


@dept_data = Department.find_by_sql(["select * from departments a where a.dept_id in (?)", @dept_ids_csv])

预期的查询日志:

select * from departments a where a.dept_id in ('IT','Account','Finance')

生成的实际查询日志(因为soem原因会自动附加额外的引号) - FAILS:

select * from departments a where a.dept_id in ('IT'',''Account'',''Finance')

选项2(在CSV转换期间删除引号)

@depts = current_user.depts             
puts @depts                 # prints    IT Account Finance

# converting to CSV string here 
@dept_ids_csv = @depts.gsub(" ", ",")   
puts @dept_ids_csv          # prints    IT,Account,Finance


@dept_data = Department.find_by_sql(["select * from departments a where a.dept_id in (?)", @dept_ids_csv])

预期的查询日志:

select * from departments a where a.dept_id in ('IT','Account','Finance')

生成实际查询日志(IN子句中缺少引号) - FAILS:

select * from departments a where a.dept_id in ('IT,Account,Finance')

我该如何解决这个问题?


I have the following query where I am trying to fetch department table data based on multiple department ids using IN clause but the query that I see in the Rails server log is incorrect and hence the data fetching fails. Problem with quotes.

I tried two different options here but both of them fails because of either missing quotes or extra quotes.

OPTION 1 (ADDING QUOTES DURING CSV CONVERSION)

@depts = current_user.depts             
puts @depts                 # prints    IT Account Finance

# converting to CSV string here
@dept_ids_csv = @depts.gsub(" ", "','")     
puts @dept_ids_csv          # prints    IT','Account','Finance


@dept_data = Department.find_by_sql(["select * from departments a where a.dept_id in (?)", @dept_ids_csv])

Expected Query Log:

select * from departments a where a.dept_id in ('IT','Account','Finance')

Actual Query Log Generated (for soem reason appends extra quotes automatically) - FAILS:

select * from departments a where a.dept_id in ('IT'',''Account'',''Finance')

OPTION 2 (REMOVING THE QUOTES DURING CSV CONVERSION)

@depts = current_user.depts             
puts @depts                 # prints    IT Account Finance

# converting to CSV string here 
@dept_ids_csv = @depts.gsub(" ", ",")   
puts @dept_ids_csv          # prints    IT,Account,Finance


@dept_data = Department.find_by_sql(["select * from departments a where a.dept_id in (?)", @dept_ids_csv])

Expected Query Log:

select * from departments a where a.dept_id in ('IT','Account','Finance')

Actual Query Log Generated (missing quotes in the IN clause) - FAILS:

select * from departments a where a.dept_id in ('IT,Account,Finance')

How do I fix this issue?


原文:https://stackoverflow.com/questions/36020515
更新时间:2019-12-03 09:46

最满意答案

尝试将字符串拆分为数组并将其传递给where子句。

@dept_data = Department.where(dept_id: @depts.split(" "))

Try splitting the string into an array and passing it to a where clause.

@dept_data = Department.where(dept_id: @depts.split(" "))
2016-03-15

相关问答

更多

从列表中创建数组并初始化它 - Java(Make array from a list and initialize it - Java)

您无法创建参数化类型的数组。 请参阅链接了解原因。 无论如何,替代方法是创建List<List<XYZ>> : List<List<XYZ>> algoList = Arrays.asList(algo1, algo2, algo3); 顺便说一句,记得在将它们放入列表之前正确初始化algo 1-3! 否则你将把空值放入列表中! 如果你想从algoList添加/删除东西,你需要一个ArrayList<List<Algo>> : ArrayList<List<XYZ>> algoList = n

什么时候用c ++创建对象?(when are objects created in c++?)

由于object是一个静态(即全局)变量,因此在运行全局构造函数时,即在main()开始执行之前,会执行其构造函数。 Since object is a static (i.e. global) variable, its constructor is executed when the global constructors are run, i.e. before main() starts executing.

Mac从邮件中提取HTML(Mac to extract HTML from Mail messages)

您将获得具有source属性的原始源 set end of mailContentList to source of selectedMail 但你必须“手动”提取HTML部分。 You get the raw source with the source property set end of mailContentList to source of selectedMail but you have to extract the HTML portion "manually".

在SQLite和Android中使用列作为选择参数(Use a column as a selection Argument in SQLite and Android)

我相信你的问题可能是你可能正在尝试将字符串转换为指定了Integer.parseInt(DocumentEntry.COLUMN_WARN_IN) 的整数 ( 除非DocumentEntry.COLUMN_WARN_IN解析为整数,否则它可能会出现决心警告 ) 此外,您已经省略了第二个参数的包含,因此无法包含并可能导致参数不匹配问题,因为您提供了两个,但只要求使用一个。 我建议使用: - String selection = DocumentEntry.COLUMN_EXPIRED + " <>

使用tidyverse创建一个以恒定速率增加的列(Create a column that increases at a constant rate using the tidyverse)

为什么我们需要purrr::accumulate使用简单的公式可以实现同样的事情: library(tidyverse) growth_rate <- 0.05 # percent df %>% mutate(Calculated = first(estimated)*((1+growth_rate)^(row_number()-1))) # # A tibble: 6 x 4 # year population estimated Calculated # <dbl> <dbl>

搜索不工作Magento 1.9 rwd主题(Search not working Magento 1.9 rwd theme)

我找到了很多解决方案,并创建了一个博客来解决这个问题。 您可以在Magento Search解决方案中找到解决方案 I have found out many solutions to it and created a blog to how I solved this issue. You can find solutions here at Magento Search solution

将所有输出放在JavaScript中的while循环中的特定文本框中(Putting all the output in a specific textbox in a while loop in JavaScript)

你可以这样做: 给答案输入一个id: <input id="answerTextBox" type="text" name="ans" disabled> while循环后填充answer文本框: while (x>y) { document.getElementById("answerTextBox").value += ("" + x); x=x+1; } 这将把x和y放在你的答案文本框中。 UPDATE ****** <html> <body> <script type="tex

如何在AsyncTask或Fragment中处理Null?(How to handle Null in AsyncTask or Fragment?)

在onPost()方法中写一个文档检查 if (document!=null){ Elements elementsId = document.select("id"); Elements elementsCheckin = document.select("checkin"); Elements elementsCheckout = document.select("checkout"); Elements ele

相关文章

更多

最新问答

更多
  • 根据Woocommerce中的自定义字段计算自定义购物车商品价格(Custom cart item price calculation based on dimentions custom fields in Woocommerce)
  • 分开foreach项目并单独打印(Separate foreach items and print them individually)
  • 写模式管道是否同步?(Is write mode pipe synchronous?)
  • 发现可用的Windows Phone强调色和本地化名称(Discover available Windows Phone accent colors and localized names)
  • 使用多线程和WPF更新集合(Update Collection with multithreading and WPF)
  • 如何在字符串的情况下使用同步?(How should the synchronization be used in case of strings?)
  • 打印唯一的行,比较不超过N个字符(Print unique lines, compare no more than N characters)
  • Javascript / jQuery - 如何调用switch case从另一个函数执行(Javascript/jQuery - how to call a switch case to execute from another function)
  • Hbase超时错误不断发生(Hbase timeout errors keep occuring)
  • 如何在Delphi中更改TabControl中活动TAB的颜色(How to change the color of active TAB in a TabControl, in Delphi)
  • 正则表达式:在sublime文本中替换一些PHP代码(Regex: replace some pieces of php code in sublime text)
  • 在带有Scene2D的LibGDX中,如何在按下按钮时连续向右走?(In LibGDX with Scene2D, how can I continuously walk to the right when a button is pressed?)
  • 累加器如何在Haskell中工作?(How do accumulators work in Haskell?)
  • 使用开关检查市场和前缀与正确的货币符号(using switch to check market and prefix with correct currency symbol)
  • 在哪里分配一次使用类?(Where to allocate one time use class?)
  • 如何从两个DateTime / NaiveDateTime获取持续时间?(How do I get Duration from two DateTime / NaiveDateTime?)
  • 解析TimeSpan大于24小时?(Parse a TimeSpan greater than 24 hours? [duplicate])
  • 如何在球拍中本地更改阅读规则?(How to locally change reading rules in racket?)
  • 数据库应该由DI注入时的模拟存储(通过构造函数)(Mock storage when database should be injected by DI (through constructor))
  • 使用maven集成2个eclipse项目(Integrate 2 eclipse projects using maven)
  • 角度ng-repeat不检测变化(Angular ng-repeat not detecting changes)
  • Xaml组件在Silverlight + XNA应用程序的多个页面中可见(Xaml component visible in multiple pages in Silverlight+XNA applications)
  • 如何将`var`变量等同于另一个查询(How to equate `var` variable to another query)
  • 如何设计hyperledger链代码以适合您的业务?(how to design your chaincode of hyperledger to fit for your business?)
  • PHP readfile错误(PHP readfile error)
  • 在Date之后排序列表然后是时间(Sorting list after Date then time)
  • Android内部版本号(Android build number)
  • 在没有预设退出条件的情况下停止无限循环(stopping an infinite loop with no preset exit condition)
  • Phonegap应用程序全屏通过html页面中的按钮(Phonegap Application fullscreen through a button in html page)
  • PAA是否适合在门户网站中自动执行wcm库部署和设置?(Is PAA a good candidate for automating wcm library deployment and setup in portal?)