Rails SQL Query with Find

How can I get SQL statement created by ActiveRecord#find without actually executing it?

For Rails 3:

Check out the ActiveRecord::Relation docs at the Rails 3 docs.

# get the relation
rel = User.complex_scope.chained_complex_scope

# get the SQL
# this does not execute the query
sql = rel.to_sql

# find out how many records
# this executes the query behind the scenes
count = rel.size

Rails search SQL query

You can use the keyword "or" in your SQL queries:

Guide.where('description LIKE :search OR name LIKE :search', search: "%#{params[:search]}%")

About the doc, you can try this website which seems to offer interactive courses about SQL in general: http://www.sqlcourse.com/

This website is about PostgreSQL: http://www.tutorialspoint.com/postgresql/postgresql_overview.htm

Also, ILIKE might be usefull in this case (it is case-insensitive).

How to show SQL queries run in the Rails console?

Rails 3+

Enter this line in the console:

ActiveRecord::Base.logger = Logger.new(STDOUT)

Rails 2

Enter this line in the console:

ActiveRecord::Base.connection.instance_variable_set :@logger, Logger.new(STDOUT)

How to write a Rails SQL query for finding an object where all children have an equal value

class Member < ActiveRecord::Base
  has_many :courses, through: :course_members
  has_many :course_members

  has_many :completed_courses,
    -> { joins(:quizzes).where.not(quizzes: {completed: [false, nil]}) },
    through: :course_members,
    source: :course
end

If your completed boolean column is NOT NULL, then change [false, nil] above to just simply false

Usage Example

irb(main):002:0> Member.first.completed_courses
  Member Load (0.2ms)  SELECT "members".* FROM "members" ORDER BY "members"."id" ASC LIMIT 1
  Course Load (0.1ms)  SELECT "courses".* FROM "courses" INNER JOIN "sections" ON "sections"."course_id" = "courses"."id" INNER JOIN "quizzes" ON "quizzes"."section_id" = "sections"."id" INNER JOIN "course_members" ON "courses"."id" = "course_members"."course_id" WHERE (NOT (("quizzes"."completed" = 'f' OR "quizzes"."completed" IS NULL))) AND "course_members"."member_id" = ?  [["member_id", 1]]

Rails SQL Query with find

Simple way:

ids = LevelsQuestion.all(:select => "question_id", 
        :conditions => "level_id = 15").collect(&:question_id)
Question.all(:select => "id, name", :conditions => ["id not in (?)", ids])

One shot:

Question.all(:select => "id, name",
:conditions => ["id not in (select question_id from levels_questions where level_id=15)"])

Rails: Query on a Method

ActiveRecord is solely responsible for getting information from the database for you - and because region is a method on the Airport model, and not a database column, you don't get the result you expect. This is easily seen in the generated SQL statement for your query - it's looking for a column called region:

SELECT "airports".* FROM "airports" WHERE "airports"."region" IS NULL

Your intuition about select being the proper way to do this is correct. Using select shouldn't generate a bundle of SQL queries. Try:

Airport.all.select { |a| a.region.nil? }

And check out the generated SQL. You should see something like:

Airport Load (7.1ms)  SELECT "airports".* FROM "airports"

That is, unless #region has other ActiveRecord queries inside of it, at which point we need to see the method definition to make any other judgements.

Create Rails query using LIKE that searches over association tables

And version

User.joins(:products).where("users.description LIKE ? AND products.description LIKE ?", "%happy%", "%happy%")

Or version

User.joins(:products).where("users.description LIKE ? OR products.description LIKE ?", "%happy%", "%happy%")

Rails is breaking SQL query when modifying order

Finally found a way to make it work using preload instead of includes. We wanted to avoid having seperate queries to load posts and categories but since performance is not affected by it, we don't mind it.

Here is how it look like:

Conversation.preload(post: :category)
            .from(distinct, :sub)
            .select('sub.*, profiles.status AS interlocutor_status')
            .joins('LEFT OUTER JOIN users ON interlocutor_id = users.id')
            .joins('LEFT OUTER JOIN profiles ON interlocutor_id = profiles.user_id')
            .where.not('profiles.status' => :pending)
            .order('sub.unread_counter DESC, sub.last_activity_on DESC')

Which generates 3 queries:

-- Query 1
SELECT sub.*, profiles.status AS interlocutor_status
FROM (
  SELECT DISTINCT
  ....

-- Query 2
SELECT "posts".* FROM "posts" WHERE "posts"."id" IN (............)

-- Query 3
SELECT "categories".* FROM "categories" WHERE "categories"."id" IN (..........)

Thanks to everyone for the help in comments (max and Sebastian Palma)

---

Related Topics