DEV Community

Beth Haubert
Beth Haubert

Posted on

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
}
Enter fullscreen mode Exit fullscreen mode

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"
}
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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"
}
Enter fullscreen mode Exit fullscreen mode

Top comments (0)