How to Convert mysql query into Rails ORM?

rubyonrails-talk
1

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}]

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

2

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

3

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

4

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 .

5

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 .

6

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.

7

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...