Ruby Activerecord in Clause

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



Leave a reply



Submit