Ruby rails: queries: Difference between revisions
From wikinotes
(→filter) |
(→filter) |
||
Line 110: | Line 110: | ||
# WHERE x OR y | # WHERE x OR y | ||
User.where(first_name: "Alex") | User.where(first_name: "Alex") | ||
.or(first_name: "Courtney") | .or(User.where(first_name: "Courtney")) | ||
# WHERE x AND y | # WHERE x AND y |
Latest revision as of 16:17, 3 November 2021
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 endcombine 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 databasefind_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 associationtake (any column)
User.take # get any user, or nil if non exist User.take(2) # get 2 usersselect (object, if column matches)
User .where(last_name: "Guthrie") .select(first_name: "Alex") # filter out "Guthries" with firstnames other than alexaggregates
# 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 AssociationProject .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 resultsselector
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