Query Fun

Right now we're preparing to move our customers' sites to our new platform, which means scheduling upgrades and repointing DNS. To make it easier on everyone, we're building a platform upgrade dashboard that lists all of a customer's site upgrades by status. For each of the three different statuses, there is a section where the sites within those sections are listed alphabetically. I'm in charge of writing the query.

Here's an imaginary customer's sites:

{
    :id => 129,
    :name => "Shart Attack",
    :owner_id => 1
},
{
    :id => 234,
    :name => "Fart of Gold",
    :owner_id => 1
},
{
    :id => 245,
    :name => "Farty McFly",
    :owner_id => 1
},
{
    :id => 312,
    :name => "Fart Corner",
    :owner_id => 1
}

And here is a list of PlatformUpgrades::SiteUpgrade objects

{
    :id => 1,
    :site_id => 234,
    :status => "scheduled"
},
{
    :id => 2,
    :site_id => 129,
    :status => "unscheduled"
},
{
    :id => 3,
    :site_id => 245,
    :status => "upgraded"
},
{
    :id => 4,
    :site_id => 312,
    :status => "scheduled"
}

So I want to return the PlatformUpgrades::SiteUpgrade objects in the correct order based on their status and site name. I ended up creating a new class to set up and run the query.

I also got to use a bit of Arel directly for the first time. It's a library to help manage SQL for Ruby. In fact, it's the framework that ActiveRecord is built on.

You'll see I initialized the class with a user and scope. Currently this query is only being used in one place, so I set the default scope to all PlatformUpgrades::SiteUpgrades. I also made a custom ordering method.

module PlatformUpgrades
    # Gathers SiteUpgrades for a user and orders them correctly (unscheduled, scheduled, upgraded)
    #
    # Examples:
    #
    #   PlatformUpgrades::SiteUpgradeQuery.new(user: user)
    #
    class OrderedSiteUpgradeQuery < Query
        # Public: Instantiates a new query.
        #
        # user - The user who owns the sites associated with the site upgrades.
        #        Include sites where they are collaborators or members
        # scope - The scope from which we base the query.
        def initialize(user:, scope: PlatformUpgrades::SiteUpgrade.all)
            @user = user
            @scope = scope
        end

                # Public: Runs a query
        #
        # Returns: An ActiveRecord::Relation 
        def find
            scope.joins(:site)
                 .where(sites: { id: user.all_sites.select(:id) })
                 .order(order_by_status)
                 .order(sites[:name].asc)
        end

        private

        attr_reader :user
        attr_reader :scope

        table_alias sites: Site, upgrades: PlatformUpgrades::SiteUpgrade

        def order_by_status
            Arel::Nodes::Case
                .new(upgrades[:status])
                .when("unscheduled").then(0)
                .when("scheduled").then(1)
                .else(2)
        end
    end
end

So now when I run PlatformUpgrades::SiteUpgradeQuery.new(user: User.find(1)).find I get my PlatformUpgrades:SiteUpgrade Objects back in the correct order:

{
    :id => 2,
    :site_id => 129,
    :status => "unscheduled"
},
{
    :id => 4,
    :site_id => 312,
    :status => "scheduled"
},
{
    :id => 1,
    :site_id => 234,
    :status => "scheduled"
},
{
    :id => 3,
    :site_id => 245,
    :status => "upgraded"
}

Blog

Beth Haubert

Join Our Newsletter. No Spam, Only the good stuff.

Related