To Documents

ActiveRecord Queries

• Reference: ActiveRecord Query Interface
  guides.rubyonrails.org/active_record_querying.html
• Rails uses ActiveRecord, which is an ORM (Object Relational Mapping), built on top of SQL.
• This means that you do not need to know SQL (Structured Query Language) to use Rails.
• If you wish, however, you can call SQL from ActiveRecord.
• Here are some ActiveRecord methods:
  

  # General purpose
  all  first  last  where  find_by  select
  
  # Computation
  count  sum  average  minimum  maximum
  
  # Increase efficiency
  find_each  find_in_batches  pluck
  
  # Issue SQL command directly
  find_by_sql
  

• Create a Rails project with a model named Patient, and populated with with this seeds.rb file (delete the code pertaining to Doctor before running rake db:seed). Then run these Rails commands from the Rails console, which is invoked with
  

  > rails c
  

  Here are the queries
  

  # 1. Get an array of all Patient records:
  a = Patient.all
  
  # 2. Get the first Patient record:
  a = Patient.first
  
  # 3. Get an array containing the first Patient record:
  a = Patient.first(1)
  
  # 4. Get an array of the first 3 Patient records:
  a = Patient.first(3)
  
  # 5. Get the last Patient record:
  a = Patient.last
  
  # 6. Get an array containing the last Patient record:
  a = Patient.last(1)
  
  # 7. Get an array of the last 2 Patient records:
  a = Patient.last(2)
  
  # 8. Get ActiveRecord relation that returns all records
  #    with first name Judith.  Note that where always
  #    returns a relation.  To get an array of single element
  #    see queries 9 and 10.
  a = Patient.where(first_name: 'Judith')
  
  # 9. To obtain an actual array, use to_a or all:
  a = Patient.where(first_name: 'Judith').to_a
  a = Patient.where(first_name: 'Judith').all
  
  # 10. To pick out the first element of array, use first or [0]:
  a = Patient.where(first_name: 'Judith').first
  a = Patient.where(first_name: 'Judith')[0]
  
  # 11. This form of the query is dangerous because
        SQL injection is possible:
  a = Patient.where("first_name = 'Judith'")
  
  # 12. To prevent SQL injection, pass 'Judith' as a parameter:
  a = Patient.where("first_name = ?", 'Judith')
  
  # 13. Use a dynamic finder:
  a = Patient.find_by_first_name('Judith')
  
  # 14. If there is more than one match, find_by only returns
        the first match:
  a = Patient.find_by_gender('F')
  
  # 15. The query in 11 could also have been written like this:
  a = Patient.find_by(first_name: 'Judith')
  
  # 16. Use order for sorting a relation by a field:
  a = Patient.order(:first_name)
  a = Patient.order(first_name: :desc)
  a = Patient.order(:first_name).reverse_order
  
  # 17. Sort by two fields:
  a = Patient.order(:gender, :first_name)
  
  # 18. Use select to pick out fields from the table.
        A relation is returned.
  a = Patient.select(:first_name)
  a = Patient.select(:id, :first_name)
  
  # 19. Use count to count records:
  n = Patient.where(gender: 'F').count
  
  # 20. sum, average, minimum, maximum perform the 
        calculation for a field
  sum = Patient.sum(:age)
  min = Patient.minimum(:age)
  max = Patient.maximum(:age)
  ave = Patient.average(:age)