Ruby rails: queries

From wikinotes
Revision as of 16:17, 3 November 2021 by Will (talk | contribs) (→‎filter)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)

Tutorials

query optimization https://www.speedshop.co/2019/01/10/three-activerecord-mistakes.html

Syntax

raw queries

sql = "Select * from ... your sql query here"
records_array = ActiveRecord::Base.connection.execute(sql)

results

users = User.where(first_name: 'alex').to_sql  # to_sql prints actual query
users.length  # 2 (number of matched users)

batches

A.where(name: 'John').find_in_batches do |batch|
  # ...
end

A.where(name: "John").find_each(batch_size: 1000) do |a|
  # your code
end

combine relations

queries = [Users.where(first_name: "Alex"), Users.where(last_name: "Something")]
final_query = queries.reduce { |result, query| result.or(query) }

query

find (primary_key)

User.find(3)                         # find userid with primary_key 3
User.find([3, 4])                    # find userids with primary_keys 3 and 4
User.find { |u| u.name == "lukes" }  # 'u' is every record in database

find_by

Searches by column value (you may want to verify it is indexed first with SHOW INDEX FROM table.

User.find_by(lastname: "guthrie")
User.find_by(projects: {status: 'active'})  # query through association

take (any column)

User.take     # get any user, or nil if non exist
User.take(2)  # get 2 users

select (object, if column matches)

User
  .where(last_name: "Guthrie")
  .select(first_name: "Alex")   # filter out "Guthries" with firstnames other than alex

aggregates


# load preloads query (so size makes no query)
# size counts number of records
User.load.size  # number of records (prefer to count, which SQL every time)

filter

where


User.where(first_name: "Alex")                 # equals
User.where.not(first_name: "Alex")             # where not
User.where("age > ?", 30)                      # greaterthan, lessthan
User.where(age: 10..30)                        # within range
User.where(first_name: ["Alex", "Courtney"])   # where in
User.where("first_name LIKE ?", "%#{search}%") # where like

# WHERE can be stacked, it is merged into a WHERE/AND
User.where(first_name: "Alex").where(last_name: "Guthrie")

# WHERE x OR y
User.where(first_name: "Alex")
  .or(User.where(first_name: "Courtney"))

# WHERE x AND y
User.where(first_name: "Alex",
           last_name: "Guthrie")

# Regular SQL
User.where("first_name = ? AND last_name IN (?)", 
  "Courtney", 
  ["Breau", "Guthrie"]))


join


# JOIN (default associations)
User
  .joins(:projects)


# JOIN ON
User
  .where(:memberships => {:user_id => current_user.id})


# JOIN WHERE {joined}.{column}
User
  .joins(:projects)
  .where(projects: { id: 2 })

# RAW SQL
User.
  .joins("INNER JOIN memberships ON memberships.group_id = posts.group_id")

includes (pre-loads associated tables)


When associated tables are accessed as attributes from a .join(...),
an additional query is performed each time the attribute is queried.


include sidesteps this, pre-loading the tables into the activerecord.

Notes

  • cannot include a polymorphic association.
  • includes and where use different names for the same items.
    • .includes(<model_attribute_for_table>)
    • but .where(<table_name>: { <column_name>: "foo" } ).


Examples

Given the following relationships:

class Project < ActiveRecord::Base
  has_many: users
end
class User < ActiveRecord::Base
  belongs_to: projects
  has_many: shoes
end
class Shoe < ActiveRecord::Base
  belongs_to: users
end


Single Association

Project
  .includes(:users)
  .where(active: true,                    # projects.active == true
         users: { first_name: "alex" })   # projects.users.first_name == true


Nested Associations

# You can also nest to deeper levels by using Hashes instead of Arrays
Project
  .includes(users: [:shoes])                # includes projects.users.shoes in original query
  .where(
    active: true,                           # projects.active == true
    users: { first_name: "alex",            # projects.users.first_name == "alex"
             shoes: { colour: "green" } })  # projects.users.shoes.colour == "green"


limit

User.all.limit(3)  # limit to 3x results

selector

pluck (get column-value instead of object)

User.where(first_name: 'alex').pluck(:id)
# > [1, 15, 25]

You can also pluck columns from joined/included tables

User
  .includes(:roles)
  .pluck(:'roles.role_name')
# > ['developer', 'manager', 'developer', ...]

# (same, but less explicit)
User.includes(:roles).pluck(:role_name)

Note that you CANNOT pluck associations.

distinct

person.pets.select(:name).distinct