Ruby Activerecord IN clause
From §3.3.3 Subset Conditions of the Rails Guides:
If you want to find records using the
IN
expression you can pass an
array to the conditions hash:Customer.where(orders_count: [1,3,5])
This code will generate SQL like this:
SELECT * FROM customers WHERE
(customers.orders_count IN (1,3,5))
You can also use the arel syntax:
Client.where(Client.arel_table[:order_count].in([1,3,5]))
will generate the same SQL.
includes with where clause in ActiveRecord
You want to select where groups.shop_id is 1 or where there is no groups.shop_id
You can do that with a test for nil
Organisation.where(name: 'my organisation without groups').includes(:groups).where(groups: { shop_id: [nil, 1] })
ActiveRecord Where clause using items of array in OR operator
Looks like you need not array but hash like this
more_than = {
ios: 13,
android: 14
}
You can build SQL condition string with sanitize_sql_for_conditions from array with placeholders
sql_condition_array =
more_than.each_with_object([]) do |(atr, value), sql_array|
if sql_array.empty?
sql_array[0] = "#{atr} > ?"
else
sql_array[0] << " OR #{atr} > ?"
end
sql_array << value
end
# => ["ios > ? OR android > ?", 13, 14]
sql_condition = Control.sanitize_sql_for_conditions(sql_condition_array)
# => "ios > 13 OR android > 14"
or may be directly
sql_condition = more_than.map { |atr, value| "#{atr} > #{value}" }.join(" OR ")
# => "ios > 13 OR android > 14"
And then
Control.where(company_id: 12345).where(sql_condition)
The query will be like this:
SELECT "controls".* FROM "controls"
WHERE "controls"."company_id" = 12345
AND (ios > 13 OR android > 14);
If that hash will consist of just one element, there will be not OR
used like this:
SELECT "controls".* FROM "controls"
WHERE "controls"."company_id" = 12345
AND (ios > 13);
Active Record .includes with where clause
This is how includes is intended to work. When you add a where clause it applies to the entire query and not just loading the associated records.
One way of doing this is by flipping the query backwards:
columns = Ticket.eager_load(:columns)
.where(sprint_id: 10, columns: { board_id: 1 })
.map(&:column)
.uniq
Activerecord query with array in where clause
You can also try this:
@ids = [1,2,3]
@locations = @ids.map {|id| [Location.where(user_id: id).last.location,User.find(id).name]}
#This would give you something like this: [["US",XYZ],["IND","ABC"]]
Active Record Where Clause For Relation In Model
I want to select sites of premium user
This will do
User.includes(:sites).where('users.category = ?', 'premium')
Update
If sites also have categories like 'wordpress or joomla', how do i
apply where clause to select only wordpress sites of premium users
For that you need to tweak the query like this
User.includes(:sites).where('users.category = ? and sites.category = ?', 'premium','wordpress')
ActiveRecord joins throwing Column in where clause is ambiguous error - Rails 5.1
When you join two tables and both tables have a name
column then you have to tell the database which name
column you want to match.
def search
@projects = Project.all
@projects = @projects.where('projects.name LIKE ?', "%#{params[:name]}%") if params[:name].present?
@projects = @projects.joins(:categories).where(categories: { id: params[:category_id] }) if params[:category_id].present?
end
Note the projects.name
in the LIKE
query.
Dynamic where clause in active record avoiding sql injection
A simple but secure way would be to whitelist the allowed property names:
PROPERTIES = ["foo", "bar", "baz"].freeze
def find_greater_than(property, value)
raise "'#{property}' is not a valid property, only #{PROPERTIES.join(", ")} are allowed!" if !PROPERTIES.include?(property)
SomeObject.where("#{property} > ?", value)
end
You can (as @engineersmnky pointed out) dynamically check for available columns:
raise "Some Message" if SomeObject.column_names.include?(property)
but I don't like this aproach as having columns searchable should be a decission, not automated.
Another aproach is to use the sanitizing provided by Rails.
def find_greater_than(property, value)
sanitized_property = ActiveRecord::Base.connection.quote_column_name(property)
SomeObject.where("#{sanitized_property} > ?", value)
end
The quoting logic is implemented by the DB specific connection adapters.
Related Topics
Iterate Every Month with Date Objects
Confusing Behaviour of Const_Get in Ruby
Ruby on Rails, Including a Module with Arguments
How to Get Request.Uri in Model in Rails
What's the Variable Scope Within 'Class_Eval' String
Creating Categories on Jekyll Driven Site
How Are Symbols Used to Identify Arguments in Ruby Methods
Finding All the Users That Have Duplicate Names
How to Read the Body Text of an Email Using Ruby's Net/Imap Library
How to Uninstall Ruby on Rails and Do a Clean Install
Ruby Regex What Does the \1 Mean for Gsub
Ruby Classes: Initialize Self VS. @Variable
MAC Os X Mountain Lion "Rails Is Not Currently Installed on This System."
How to Run Ruby Tasks That Use My Rails Models
How to Use Nokogiri to Parse an Xml File
How to Use Rails and Paperclip to Store Photos on Google Cloud Storage