Rails 5: Activerecord or Query

Rails 5: ActiveRecord OR query

The ability to chain or clause along with where clause in ActiveRecord query will be available in Rails 5. See the related discussion and the pull request.

So, you will be able to do the following things in Rails 5:

To get a post with id 1 or 2:

Post.where('id = 1').or(Post.where('id = 2'))

Some other examples:

(A && B) || C:

    Post.where(a).where(b).or(Post.where(c))

(A || B) && C:

    Post.where(a).or(Post.where(b)).where(c)

ActiveRecord OR query

Use ARel

t = Post.arel_table

results = Post.where(
  t[:author].eq("Someone").
  or(t[:title].matches("%something%"))
)

The resulting SQL:

ree-1.8.7-2010.02 > puts Post.where(t[:author].eq("Someone").or(t[:title].matches("%something%"))).to_sql
SELECT     "posts".* FROM       "posts"  WHERE     (("posts"."author" = 'Someone' OR "posts"."title" LIKE '%something%'))

Rails 5: ActiveRecord AND followed by OR condition: A && (B || C)

To combine using AND (intersection), merge as mentioned by Tom Lord worked.

Requirement.where(no: 0).merge(Requirement.yes_no.or(Requirement.where.not(parent_req: nil)))

Rails 5 where or active record query

use sum() instead of count(), then select case so we can ignore your where clause.

SUPPLIER_GROUP_ATTRS = [
  "id",
  "sum(case when products.state='completed' then 1 else 0 end) as completed_products_count"
]
SupplierGroup.left_joins(:products).select(SUPPLIER_GROUP_ATTRS).group(:id)

Rails 5 Active record query not showing left_outer_joins attributes

the issue is if the column in select is not one of the attributes of the model on which the select is called on then those columns are not displayed. All of these attributes are still contained in the objects within AR::Relation and are accessible as any other public instance attributes. Ex:

products = Product.left_joins(:product_feature).select("products.id, products.name, product_features.pfeature_id").where("products.id > 8").take(2)
products.first.pfeature_id

For verification, just run these two lines in your console. You'll get your desired output. As it is a LEFT JOIN, you might get output as nil when one product has no product_features.

If you want to sort your output, then keep in mind your sorting column should contain NOT NULL values. Ex:

products = Product.left_joins(:product_feature).select("products.id, products.name, product_features.pfeature_id").where("products.id > 8")

# sort with your desired column
sorted_products = products.sort_by(&:name)
# or
sorted_products = products.sort_by(&:pfeature_id) # make sure `pfeatured_id` is not null for any product

create active record query for virtual attribute in Rails 5 or 6

User.select("users.*", "CONCAT(users.first_name, ' ', users.last_name) AS full_name")
    .where(full_name: "John Whoosiwhatsit")

This will work on MySQL, Postgres and SQLite (and probally more). ActiveRecord will map any aliased columns in the resulting rows from the query as attributes in the model.

Some dbs (like Oracle) don't let you use aliases in the WHERE clause so you would have to repeat the concatenation:

# Use an Enterprise DB they said. It will be fun they said.
User.select("users.*", "CONCAT(users.first_name, ' ', users.last_name) AS full_name")
    .where("CONCAT(users.first_name, ' ', users.last_name) = 'John Whoosiwhatsit'")

This has probally worked in almost every version since Rails AFAIK has always allowed you to revert to raw SQL if needed.

ActiveRecord query where with id discards the content after dash

The id field is probably of an integer type, so ActiveRecord basically casts the parameter to an integer by calling to_i:

 '121-fake'.to_i # -> 121

---

Related Topics