How to Convert mysql query into Rails ORM?

Rails
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”:“[email protected]”,“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

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 .

4

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

Em sex, 31 de jul de 2015 às 13:33, Elizabeth McGurty
[email protected]
escreveu:

5

On Jul 31, 2015, at 11:35 AM, [email protected] 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 R.
[email protected]
http://www.elevated-dev.com/
https://www.linkedin.com/in/scottribe/
(303) 722-0567 voice

6

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…

–
Scott R.
[email protected]
http://www.elevated-dev.com/
https://www.linkedin.com/in/scottribe/
(303) 722-0567 voice

7

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 .