rails 和 sql where IS NOT NULL 不能与 join 正常工作。 Rails 2.3.5 和 Ruby 1.8.7 - mysql 5
我有 3 张桌子和 3 张桌子。型号： 品牌 品牌数据记录 和 brand_data_records_brands - 连接表
在rails中，我想要给定品牌的给定日期范围内的所有brand_data_records，其中给定属性在数据库中不为空。
所以我有：
BrandDataRecord.find(:all, :select => column_match, :joins => :brands, :conditions => ["brand_data_records_brands.brand_id = ? and date_retrieved >= ? AND date_retrieved <= ? and ? IS NOT NULL",brand.id,start_date,end_date,column_match])
这会生成这个sql：
SELECT sentiment FROM `brand_data_records` INNER JOIN `brand_data_records_brands` ON `brand_data_records_brands`.brand_data_record_id = `brand_data_records`.id INNER JOIN `brands` ON `brands`.id = `brand_data_records_brands`.brand_id WHERE (brand_data_records_brands.brand_id = 330516084 and date_retrieved >= '2011-05-02' AND date_retrieved <= '2011-06-01' and 'sentiment' IS NOT NULL)
这通常可以工作，但它会返回一堆具有空值的额外记录。我认为这与连接有关，如果我用 sql 删除它们，它就可以正常工作，但我不确定如何在 Rails 中修复（甚至在 sql 中修复这一事实）
I have 3 tables & models:
brands
brand_data_records
and
brand_data_records_brands - the join table
In rails i want all brand_data_records for a given date range for a given brand where a given attribute is not null in the db.
So I have:
BrandDataRecord.find(:all, :select => column_match, :joins => :brands, :conditions => ["brand_data_records_brands.brand_id = ? and date_retrieved >= ? AND date_retrieved <= ? and ? IS NOT NULL",brand.id,start_date,end_date,column_match])
This generates this sql:
SELECT sentiment FROM `brand_data_records` INNER JOIN `brand_data_records_brands` ON `brand_data_records_brands`.brand_data_record_id = `brand_data_records`.id INNER JOIN `brands` ON `brands`.id = `brand_data_records_brands`.brand_id WHERE (brand_data_records_brands.brand_id = 330516084 and date_retrieved >= '2011-05-02' AND date_retrieved <= '2011-06-01' and 'sentiment' IS NOT NULL)
Which generally works, but it gives back a bunch of extra records that have a null value. I think its something to do with the joins, if I remove them with sql only it works fine, but im not sure how to fix in rails (or even in sql for that fact)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您可能想引用该专栏：
您无意中所做的是断言字符串
'sentiment'不为空，当然它永远不会为空。在条件中传入:sentiment或'sentiment'.to_sym'应该可以解决此问题，因为符号在转换时会使用反引号进行转义。You probably mean to reference the column:
What you're doing inadvertently is asserting that the string
'sentiment'is not null, which of course it will never be. Passing in:sentimentor'sentiment'.to_sym'in your conditions should fix this as symbols get escaped with backquotes on conversion.