执行上轨原始查询红宝石 [英] Executing raw query ruby on rails
问题描述
我希望去下面的查询执行：
I want to execute de following query:
query = "select v.price, v.quantity, p.title, v.product_id from variants v join products p on (v.product_id = p.id) where (v.price,v.product_id) in (select min(price),product_id from variants group by product_id);"
当我这样做的结果=的ActiveRecord :: Base.connection.execute（查询）在轨控制台我得到：
When I do results = ActiveRecord::Base.connection.execute(query) in the rails console I get:
ActiveRecord::StatementInvalid: SQLite3::SQLException: near ",": syntax error: select v.price, v.quantity, p.title, v.product_id from variants v join products p on (v.product_id = p.id) where (v.price,v.product_id) in (select min(price),product_id from variants group by product_id)
我相信，所有的逗号到位的说法是pretty的写得很好，至少在甲骨文。哪里是我的错？
I am sure that all the commas are in place and the statement is pretty well written at least for Oracle . Where is my mistake ?
推荐答案
这似乎sqlite3的不宵夜日的运营商有多个值（其中（v.price，v.product_id）在（选择分钟（价格），PRODUCT_ID ... ）
It seems sqlite3 doesn't supper th in-operator with multiple values (where (v.price,v.product_id) in (select min(price),product_id...)
这是讨论问题的背后是：
From discussion the question behind is:
我需要相处的每一件产品的最低价格（它在变体） 与名称（它在产品）和数量（它在变体）
I need to get the lowest price(it's in Variants) of every product along with the name(it's in Products) and quantity(it's in Variants)
在此基础上，我建议这条SQL语句：
Based on this I recommend this SQL-statement:
SELECT min(`v`.`price`) AS 'min_price', `v`.`quantity`, `p`.`title`, `v`.`product_id` FROM `variants` AS 'v' INNER JOIN `products` AS 'p' ON (`v`.`product_id` = `p`.`id`) GROUP BY `product_id`
或人类可读的版本相同的语句：
or same statement in a human readable version:
SELECT
min(`v`.`price`) AS 'min_price',
`v`.`quantity`, `p`.`title`, `v`.`product_id`
FROM `variants` AS 'v'
INNER JOIN `products` AS 'p'
ON (`v`.`product_id` = `p`.`id`)
GROUP BY `product_id`
我不使用导轨和活动记录，我用续集得到这个解决方案。这里是我完整的脚本来测试code：
I don't use rails and active record, I used sequel to get this solution. Here is my complete script to test the code:
require 'sequel'
DB = Sequel.sqlite('test.db')
Sequel.extension :core_extensions
Sequel.extension :pretty_table
#if called 2nd time with a db-file
DB.drop_table(:variants) if DB.table_exists?(:variants)
DB.drop_table(:products) if DB.table_exists?(:products)
DB.create_table :products do |t|
primary_key :id
t.string :title
t.string :description
t.timestamps null: false
end
DB.create_table :variants do |t|
primary_key :id
t.boolean :is_active
t.integer :price
t.integer :quantity
t.timestamps null: false
foreign_key :product_id, :products
end
class Product < Sequel::Model; end
class Variant < Sequel::Model; end
10.times do |g|
Product.create(title: "Prod %02i" % g, description: "Who Cares..")
end
100.times do |c|
Variant.create(
price: (rand(100)).ceil, quantity: (rand(10).ceil).floor ,
product_id: Product[rand(1..10).floor].id
)
end
puts DB.tables
sel = DB[:variants.as(:v)]
.select(Sequel.function(:min,:v__price).as(:min_price), :v__quantity, :p__title, :v__product_id)
.join(:products.as(:p), :v__product_id => :p__id)
.group_by(:product_id)
puts sel.sql
Sequel::PrettyTable.print(sel)
结果是：
SELECT min(`v`.`price`) AS 'min_price', `v`.`quantity`, `p`.`title`, `v`.`product_id` FROM `variants` AS 'v' INNER JOIN `products` AS 'p' ON (`v`.`product_id` = `p`.`id`) GROUP BY `product_id`
+---------+----------+--------+-------+
|min_price|product_id|quantity|title |
+---------+----------+--------+-------+
| 1| 1| 5|Prod 00|
| 12| 2| 9|Prod 01|
| 21| 3| 6|Prod 02|
| 1| 4| 0|Prod 03|
| 34| 5| 8|Prod 04|
| 11| 6| 8|Prod 05|
| 14| 7| 9|Prod 06|
| 7| 8| 8|Prod 07|
| 11| 9| 8|Prod 08|
| 21| 10| 8|Prod 09|
+---------+----------+--------+-------+
（值可能不同的执行中，有在数据生成随机值）。
(Values may differ for your execution, there are random values in the data creation).
这篇关于执行上轨原始查询红宝石的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
