Rails find_by_SQL with Rails

Question

I'm using a find_by_sql method to search users in my userstable.

is there a possibility to use rails code in the select statement?

 User.find_by_sql ["SELECT DISTINCT
                    users.*
                  FROM
                    users
                  JOIN
                    clients_courses cc
                  ON
                    cc.client_id = users.client_id
                  LEFT JOIN
                    memberships m
                  ON
                    m.user_id = users.id AND m.course_id = cc.course_id
                  WHERE
                    cc.course_id = ?
                    AND
                    m.user_id IS NULL
                    AND
                    users.active = ?
                    AND
                    users.firstname LIKE ? or users.lastname LIKE ?
                    AND NOT IN ( RAILS CODE )", self.id, true, "#{search}%", "#{search}%"]
  end

I Marked the position with RAILS CODE

I want to do someting linke this:

Membership.where("course_id = ?", self.id).users

is there a way to do this?

Why are you using a `select_by_sql` anyway? This can be done with activerecord `uniq`, `join` and `where` methods — Yury Lebedev, Dec 11 '15 at 15:05
Do you need ability to select users for multiple `mambership`s or just for single id each time? — Vasfed, Dec 11 '15 at 15:47

score 1 · Accepted Answer · edited May 23 '17 at 12:31

1

You can do this -

    member_user_ids  = []
    Membership.where("course_id = ?", self.id).map{|membership| membership.users.map{|user| member_user_ids << user.id}}

    # you might want to put a uniq! on member_user_ids

    User.find_by_sql ["SELECT DISTINCT
                users.*
              FROM
                users
              JOIN
                clients_courses cc
              ON
                cc.client_id = users.client_id
              LEFT JOIN
                memberships m
              ON
                m.user_id = users.id AND m.course_id = cc.course_id
              WHERE
                cc.course_id = ?
                AND
                m.user_id IS NULL
                AND
                users.active = ?
                AND
                users.firstname LIKE ? or users.lastname LIKE ?
                AND users.id NOT IN ( #{member_user_ids.join(',')} )", self.id, true, "#{search}%", "#{search}%"]

You can also have a look at link which explains how to put array of strings in where clause.

edited May 23 '17 at 12:31

Community

1
1

answered Dec 11 '15 at 15:39

Sudipta Mondal

2,550
1
19
20

In dev mode it works perfectly. But in test mode It runs into an error: undefined method `users' for # What could be the reason – Felix Dec 12 '15 at 09:47
Thanks for update, but I got this error again: undefined method `users' for # – Felix Dec 12 '15 at 09:57
is your db migrated properly for test environment ? and data available in them. – Sudipta Mondal Dec 12 '15 at 09:59
Can you post your model files, for your associations. If Membership belongs_to user, then you would need to use membership.user.id and push it into the array variable. – Sudipta Mondal Dec 12 '15 at 10:03
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/97705/discussion-between-felix-and-sudipta-mondal). – Felix Dec 12 '15 at 10:06
Membership looks like this: class Membership < ActiveRecord::Base belongs_to :user class User < ActiveRecord::Base belongs_to :client has_many :memberships, :dependent => :destroy – Felix Dec 12 '15 at 10:21

Rails find_by_SQL with Rails

1 Answers1