I am attaching the schema a full schema of my code there are three tables involved first one is messages and other one users and next one is message_replies these three table he I am trying to get the result of all messages using case and when query . I am attaching a link for schema :
https://gist.github.com/nwoow/998f353d3803dea2356c Have a look at the schema tell me if you need more On Friday, July 31, 2015 at 11:14:41 PM UTC+5:30, Ruby-Forum.com User wrote: > > Is there anyone who can suggest me the solution for a Find_by_sql query > here when I am using mysql that work perfect . But when I converted that > into active record It is not showing the exact result . First I have > tried this code below using find_by_sql: > > @message3 = Message.find_by_sql(["SELECT u.id,c.m_id,u.name,u.email > FROM messages c, users u > WHERE (CASE > WHEN c.user_one = :id > THEN c.user_two = u.id > WHEN c.user_two = #{current_user.id} > THEN c.user_one= u.id > END ) > AND ( > c.user_one ='1' > OR c.user_two ='1' > ) > Order by c.m_id DESC Limit 20", {:id => current_user.id}]) > When I am using this query I am getting the exact value which I want but > when I am doing this thing in active record way it is not working well > SO here is the code which I have tried in active record style : > > @m1 = > Message.joins(:user).select("users.id,messages.m_id,users.name,users.email").where("CASE > > > WHEN messages.user_one = #{current_user.id} THEN messages.user_two = > users.id WHEN messages.user_two = #{current_user.id} THEN > messages.user_one = users.id END").where("messages.user_one = 1 OR > messages.user_two = 1 ").order("messages.m_id DESC") > When I am running this query I am getting this generate query : > > SELECT users.id,messages.m_id,users.name,users.email FROM `messages` > INNER JOIN `users` ON `users`.`id` = `messages`.`user_one` WHERE (CASE > WHEN messages.user_one = 1 THEN messages.user_two = users.id WHEN > messages.user_two = 1 THEN messages.user_one = users.id END) AND > (messages.user_one = 1 OR messages.user_two = 1 ) ORDER BY > messages.m_id DESC > Both these query look same but when I am using find_by_sql method I am > getting two results but when I am reunnig query of active record it is > only showing one when I paste the generated sql in mysql there also I am > getting same result IF anyone can help me to understand the difference > between these two queries and how can I fix this I mean I need the > result of find_by_sql in active record . > > -- > Posted via http://www.ruby-forum.com/. > -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To unsubscribe from this group and stop receiving emails from it, send an email to rubyonrails-talk+unsubscr...@googlegroups.com. To post to this group, send email to rubyonrails-talk@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/5779d63e-358d-4715-8bdd-8eeedba421e6%40googlegroups.com. For more options, visit https://groups.google.com/d/optout.
