How to Convert mysql query into Rails ORM?

[nilayy...@gmail.com]

I have a very simple question how can I convert mysql query as Raisl active record I am using find_by_sql but it only work for mysql when I move to heroku it doesn't work . Here is mysql query below .

   @message3 = User.find_by_sql("SELECT u.id,c.m_id,u.name,u.email
         FROM messages c, users u
         WHERE CASE 
         WHEN c.user_one = #{current_user.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")

I have tried different rules to run it and no success . I have used this code below :

   @m1 = Message.joins(:user).select("users.id,messages.m_id,users.name,users.email
,CASE WHEN messages.user_one =#{current_user.id} THEN messages.user_two =users.id WHEN messages.user_one =users.id THEN messages.user_two =1 END").where("messages.user_one = 1 OR messages.user_two = 1 ").limit(20).order(:m_id)

But above code is not generating the correct result here I have two models user and message their relation is user has_many messages and messages belons_to user When I am using this code I am getting this result in json format with no error .

[{"m_id":55,"id":55,"name":"Example  User","email":"ni...@jumpbook.in","CASE WHEN messages.user_one =1 THEN messages.user_two =users.id WHEN messages.user_one =users.id THEN messages.user_two =1 END":0}]

How can I use this query perfectly and why I am getting this result when I am using select function in this query is there anyone help me

[Elizabeth McGurty]

Message fields user_one and user_two concern me.  Before I respond to your question, would you mind sharing the structure of your message and user tables?
Thanks, Liz

[Rodrigo Urubatan Ferreira Jardim]

There is something wrong with your query, your case statement is changing values in a select?
AFAIK it should not be working in mysql either

--
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-ta...@googlegroups.com.
To post to this group, send email to rubyonra...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/11fe1baf-1c14-45c9-8a18-8d1862d49e10%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[nilayy...@gmail.com]

The table structure of the message table had these fileds m_id as primary key and user_one and user_two and created at and updated at these are the fields in my Table When I am using find_by_sql it work perfect but when I am using active record only then I am getting the problem I need a proper result how can I convert this find_by_sql query in Active record .

On Friday, July 31, 2015 at 11:23:08 AM UTC+5:30, nilayy...@gmail.com wrote:

I have a very simple question how can I convert mysql query as Raisl active record I am using find_by_sql but it only work for mysql when I move to heroku it doesn't work . Here is mysql query below .

   @message3 = User.find_by_sql("SELECT u.id,c.m_id,u.name,u.email
         FROM messages c, users u
         WHERE CASE 
         WHEN c.user_one = #{current_user.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")

I have tried different rules to run it and no success . I have used this code below :

   @m1 = Message.joins(:user).select("users.id,messages.m_id,users.name,users.email
,CASE WHEN messages.user_one =#{current_user.id} THEN messages.user_two =users.id WHEN messages.user_one =users.id THEN messages.user_two =1 END").where("messages.user_one = 1 OR messages.user_two = 1 ").limit(20).order(:m_id)

But above code is not generating the correct result here I have two models user and message their relation is user has_many messages and messages belons_to user When I am using this code I am getting this result in json format with no error .

[{"m_id":55,"id":55,"name":"Example  User","email":"nilay@jumpbook.in","CASE WHEN messages.user_one =1 THEN messages.user_two =users.id WHEN messages.user_one =users.id THEN messages.user_two =1 END":0}]

[nilayy...@gmail.com]

Hi Urubatan  My find_by_sql query works perfect but when I am using it as active relation case and when and then is not working properly it is only giving me one result .

[Scott Ribe]

On Jul 31, 2015, at 11:35 AM, nilayy...@gmail.com wrote:
>
> Hi Urubatan My find_by_sql query works perfect but when I am using it as active relation case and when and then is not working properly it is only giving me one result .

In one case, your case when is in the select clause, which makes sense, in the other it is in the where clause, which does not make sense.

--
Scott Ribe
scott...@elevated-dev.com
http://www.elevated-dev.com/
https://www.linkedin.com/in/scottribe/
(303) 722-0567 voice

[Scott Ribe]

Actually, you can probably stuff the condition into the "joins" of the AREL. But there is NO POINT. You have a working solution to using a very non-standard join, trying to split it up into pieces in order to force it into AREL accomplishes nothing. So like I said previously, fix the schema, or stick with find_by_sql.

And if you think the schema is fine, step back a second and look at how much confusion it is causing you right now... You were unable to look at the SQL of two different queries and determine why they returned different results, precisely because of the convolute join...