Rails Advance query CASE WHEN method not working

Sadaf_N · July 31, 2015, 7:43pm

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 .

nilayy1234 · July 31, 2015, 8:02pm

On Jul 31, 2015, at 11:43 AM, Nilay S. [email protected] wrote:

SO here is the code which I have tried in active record style :

I’m pretty sure this is different than what you posted last time.

And you might try giving a real answer to Elizabeth’s question–that
looks suspiciously like a bad schema design.

Anyway the answer to your immediate question is obviously in the join.
You have this extremely strange join condition between the 2 tables,
which you specify in that oddball case statement in the where clause.
But you also have some has_many/belongs_to declarations in you model
classes, and so when you try to use AREL, it adds what it believes to be
the correct inner join clause, which restricts to… Well, just read the
generated query and pay attention to the inner join clause to see what
it’s doing

Answer 1 is: fix your schema. Answer 2 is: if you cannot do that, then
stick with find_by_sql because AREL’s join method is never going to be
able to cope with that structure.

nilayy1234 · July 31, 2015, 8:09pm

Fixing a schema is other thing this very straight forward question how
can I generate the query which I am using in find_by_sql or is there any
mechanism where I can select two tables without join and use the case
and when . Or can you tell me how to right exact query where I can get
the result like case when and then .

nilayy1234 · July 31, 2015, 8:14pm

On Jul 31, 2015, at 12:09 PM, Nilay S. [email protected] wrote:

Fixing a schema is other thing this very straight forward question how
can I generate the query which I am using in find_by_sql or is there any
mechanism where I can select two tables without join and use the case
and when . Or can you tell me how to right exact query where I can get
the result like case when and then .

I answered that question; the fact that you don’t like the answer is
your problem, not mine.

nilayy1234 · July 31, 2015, 8:15pm

the query is possible

you need to show us your schema for a better answer (or at least the
parts of the schema that are involved in the question)

and I’m having problems understanding what you write, there are no
commas, no points, no new lines, …
Rodrigo Urubatan Ferreira Jardim
Architect, Developer and Technical Writer
[email protected]
Visite meu blog: http://www.urubatan.com.br

Melhor livro de RoR do Brasil: http://livro.urubatan.com.br

nilayy1234 · July 31, 2015, 8:24pm

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 :

gist.github.com

https://gist.github.com/nwoow/998f353d3803dea2356c

Schema

# encoding: UTF-8
# This file is auto-generated from the current state of the database. Instead
# of editing this file, please use the migrations feature of Active Record to
# incrementally modify your database, and then regenerate this schema definition.
#
# Note that this schema.rb definition is the authoritative source for your
# database schema. If you need to create the application database on another
# system, you should be using db:schema:load, not running all the migrations
# from scratch. The latter is a flawed and unsustainable approach (the more migrations
# you'll amass, the slower it'll run and the greater likelihood for issues).

This file has been truncated. show original

Have a look at the schema tell me if you need more

nilayy1234 · July 31, 2015, 8:25pm

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 :

gist.github.com

https://gist.github.com/nwoow/998f353d3803dea2356c

Schema

# encoding: UTF-8
# This file is auto-generated from the current state of the database. Instead
# of editing this file, please use the migrations feature of Active Record to
# incrementally modify your database, and then regenerate this schema definition.
#
# Note that this schema.rb definition is the authoritative source for your
# database schema. If you need to create the application database on another
# system, you should be using db:schema:load, not running all the migrations
# from scratch. The latter is a flawed and unsustainable approach (the more migrations
# you'll amass, the slower it'll run and the greater likelihood for issues).

This file has been truncated. show original

Have a look at the schema tell me if you need more

nilayy1234 · July 31, 2015, 8:30pm

I am adding the user model and message model have a look and if it is
not
enough let me know .

gist.github.com

https://gist.github.com/nwoow/62f45077f340eb8ffe0b

User model

    class User < ActiveRecord::Base
      # Include default devise modules. Others available are:
      # :confirmable, :lockable, :timeoutable and :omniauthable
      devise :database_authenticatable, :registerable,
             :recoverable, :rememberable, :trackable, :validatable
      has_many :feed_likes
      has_many :feeds, dependent: :destroy #through: :feed_likes
       #has_many :feeds, through: :feed_likes
      has_many :market_places
      has_many :comments

This file has been truncated. show original

message model

class Message < ActiveRecord::Base
	 belongs_to :user , :foreign_key => "user_one"
end

nilayy1234 · July 31, 2015, 8:32pm

I am adding the user model and message model have a look and if it is
not
enough let me know .

gist.github.com

https://gist.github.com/nwoow/62f45077f340eb8ffe0b

User model

    class User < ActiveRecord::Base
      # Include default devise modules. Others available are:
      # :confirmable, :lockable, :timeoutable and :omniauthable
      devise :database_authenticatable, :registerable,
             :recoverable, :rememberable, :trackable, :validatable
      has_many :feed_likes
      has_many :feeds, dependent: :destroy #through: :feed_likes
       #has_many :feeds, through: :feed_likes
      has_many :market_places
      has_many :comments

This file has been truncated. show original

message model

class Message < ActiveRecord::Base
	 belongs_to :user , :foreign_key => "user_one"
end

nilayy1234 · July 31, 2015, 8:25pm

I would add that we also need to see the Message and User Models, eg,
managing primary key outside of Rails convention.
Liz

nilayy1234 · July 31, 2015, 9:00pm

What strikes me immediately is the question, does your User table
contain
the foreign key “user_one”? Otherwise, can I offer some advise? Try
to
calm down. The process in Rails development isn’t always easy,
sometimes
it is down-right difficult. In my experience here, folks are genuinely
interested in helping you.
Liz

nilayy1234 · July 31, 2015, 9:15pm

No user table does not contail foreign key user_one infact message table
contain user_one when I am using this has_many :messages,class_name:
“Message”,
foreign_key: “user_one” I in user model
I
ma getting the error what should I do to get the exact result help me .

On Saturday, August 1, 2015 at 12:28:49 AM UTC+5:30, Elizabeth McGurty