Database: Scopes, migrations, and indices

Wether you modify an existing named scope or add a new one, or when you write a new query, make sure you have the proper indices.

This particularly applies if you're going to run non-trivial queries of course (admin backends, analytics, etc).

Compound indices

A chain of scopes results in (usually) one query. You should take into account all attributes (columns) that are used in :conditions, :join, :group, :having, and :order, as all those result in filtering and sorting–slow operations without indices.

Take the list of all ...


When you set date attributes, you should not pass times

As you know, time zones make stuff a bit more difficult but are necessary.

A time-zoned record is converted to UTC using to_s(:db) to be stored, and put back into the correct time zone when loaded. So when you are not on UTC, the following will happen:

>> Time.current
=> Fri, 15 Mar 2013 11:56:03 CET +01:00
>> Time.current.to_s(:db)
=> "2013-03-15 10:56:03" # This is now UTC

That will blow up in your face when you sen...


Writing raw SQL queries

If you really, really have a good reason to write raw SQL (generally a bad practice), make sure you use the right call.

Avoid ActiveRecord::Base.connection.execute in general, in particular because Octopus relies on this to determine which queries go to replicas. execute would always go to the master.

A number of other methods Archive are available: select_value, select_rows, of update being the most usual.

If you GROUP BY, make sure you ORDER BY NULL

TL;DR: If using :group => :some_field you might want to :order => 'NULL'.

According to the man Archive

By default, MySQL sorts all GROUP BY col1, col2, ... queries as if you specified ORDER BY col1, col2, ... in the query as well. If you include an ORDER BY clause explicitly that contains the same column list, MySQL optimizes it away without any speed penalty, although the sorting still occurs. If a query includes GROUP BY but you want to avoid the overhead of sorting ...

DateTimes are Dates, beware


datetime =
date = datetime.to_date #or

will assert:

datetime.is_a? Date == true
datetime.is_a? DateTime == true
datetime.instance_of? Date == false
datetime.instance_of? DateTime == true

In case you have a table and a model like:

create_table :event do |t|    :day
  t.string  :description
class Event < ActiveRecord::Base; end

And you say:

event = => '2013-03-22')

Rails will convert the supplied value for day to the type of the database field,...

ActiveRecord::NamedScopes (2.3.x) obtaining the SQL conditions

It's a good pratice to chain several named scopes like:


Now, to make the lesson more valuable let's assume the following code:

Property.scoped(:conditions => "foo = 2").scoped(:conditions => "foo2 IS NOT NULL")

Next, if you want to be able to fetch the underlying conditions scope generated by ActiveRecord. You must do this:

Property.scoped(:conditions => "foo = 2").scoped(:conditions => "foo2 IS NOT NULL").scope(:find)
# => {:conditions => "(foo = 2) AND (foo2...
This website uses short-lived cookies to improve usability.
Accept or learn more