CODEWITHSUNDEEP
ruby-on-railsruby-on-rails-3
Gaurav
Gaurav

Reputation: 535

SQL Query in Rails

I have table with 3 columns : tableno,itmname, itmtype. The itmtype can be red or blue only. This table may have rows like: table01,item1,red ; table01,item2,blue; table02,item1,blue; table02,item4,red; table03,item1,red; table03,item5,red. Now I need to extract tableno for which all itmtypes is red. So from above rows I need output as table03 as itmtype is red for both items in it. Normally if I put where condition for itmtype as red, it may fetch table01 & table02 as well coz both has one item with itmtype red. But I need only table03 as my output coz all itmtype is red for this table.

I am struggling for quite long time writing different types of queries in rails but none of them is working.

I have written a sql query for that unable to write a similar one in rails.

select tableno,itmstatus,count(itmstatus) from lists l1 group by l1.tableno,itmstatus
having itmstatus = 'red' and count(itmstatus) = (select count(itmstatus) from lists l2  where l1.tableno = l2.tableno group by tableno)

Since I am unable to convert this in rails, couldn't test on rails console to check if it will work. I understand that this may sound a basic question but being new to rails I have only used normal select only. Please advise. Thanks.

Upvotes: 0

Views: 62

Answers (1)

Sabyasachi Ghosh
Sabyasachi Ghosh

Reputation: 2785

if you have correct sql syntax then put it in a string and use the find_by_sql method like Model.find_by_sql("your query") please check the links

http://apidock.com/rails/ActiveRecord/Base/find_by_sql/class find_by_sql with array format in Rails 3

Upvotes: 4

Related Questions