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
– Yury Lebedev, Commented Dec 11, 2015 at 15:05
Do you need ability to select users for multiple mamberships or just for single id each time? — Vasfed
– Vasfed, Commented Dec 11, 2015 at 15:47

Community · Accepted Answer · 2017-05-23 12:31:33Z

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, 2017 at 12:31

CommunityBot

11 silver badge

answered Dec 11, 2015 at 15:39

Sudipta Mondal

2,5721 gold badge20 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Felix Over a year ago

In dev mode it works perfectly. But in test mode It runs into an error: undefined method `users' for #<Membership::ActiveRecord_Relation:0x007fa0119afe08> What could be the reason

Felix Over a year ago

Thanks for update, but I got this error again: undefined method `users' for #<Membership:0x00000007984110>

Sudipta Mondal Over a year ago

is your db migrated properly for test environment ? and data available in them.

Sudipta Mondal Over a year ago

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.

Felix Over a year ago

Let us continue this discussion in chat.

|

Collectives™ on Stack Overflow

Rails find_by_SQL with Rails

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related