Random SQL Review: FHS Team Map

I’ve always thought that, in the realm of “full-stack” development, SQL has the tendency to be left out. I’m hardly a DBA but I love SQL, so this is disheartening to me.

As an example, a few weeks ago I was on a call with a bunch of my team. We were looking at a bit of code that gets the list of steps in a journey (what those things are don’t really matter) and discussing the best way to filter out steps in the “paused” state. I was surprised when the consensus was to check for the state on the PHP side of things rather than filtering them out via the source SQL query itself. Those in favor of that method suggested that it would be more readable.

In retrospect, I probably should have pushed back about performance, but I got hung up on the idea of that particular query being difficult to read upon adding an extra bit of logic to the where clause.

It inspired me to share one of my more interesting queries from FantasyHockeySim.com with a co-worker, walking him through it to explain what it does and just kind of spread some SQL knowledge. Later, I pulled another such query and did the same and it seems like a good thing to blog about. Maybe it’ll be a new series.

Important caveat: As I said, I’m not a DBA. I’m not saying this is the “right” or “best” way to do things, just how I’m doing it. Even if I’m way off base, I think there are lessons to be learned.

The first query that I shared is used to populate a league map with the location of each team in the league. For that we need the name of the team, its logo, the city it represents, and the coordinates of that city. Additionally, farm teams are shown on the map. For the farm teams we don’t show a logo but we do show the abbreviation of the parent club.

A bit about the database structure involved in this case: As I’ve explained previously, a “team” exists for only one season as a time-blocked instance of a “franchise.” A team belongs to a “division” which belongs to a “conference” – each of which also exist for only one season. A conference belongs to a league (which is a bit of a misnomer as, for example, each league in the FHS ecosystem is actually made up of a “pro” league and a “farm” league). A team may also have a parent team, if it is a farm team. Both franchises and teams have associated team names. Teams and franchises have associated logos, stored as an “asset.”

With all of that in mind, here’s the query:

SELECT *
FROM
    (
        (
            SELECT
                season_division.division_id,
                division_name,
                asset_path,
                team_name name,
                team_label,
                JSON_VALUE(franchise_settings, '$.location.pro.city') city,
                JSON_VALUE(franchise_settings, '$.location.pro.coordinates') coords,
                'P' league
            FROM franchise
                NATURAL JOIN team_name
                NATURAL JOIN season_team
                NATURAL JOIN season_division
                NATURAL JOIN season_conference
                INNER JOIN asset ON franchise.asset_id_franchise = asset.asset_id
            WHERE ((season_id = FHS_CurrentSeason(:league_id)) AND (conference_hidden = 0))
        ) UNION (
            SELECT
                season_division.division_id,
                division_name,
                '' asset_path,
                CONCAT(farm_team_name.team_name, ' (', pro_team_name.team_abbr, ')') name,
                '' team_label,
                JSON_VALUE(franchise_settings, '$.location.farm.city') city,
                JSON_VALUE(franchise_settings, '$.location.farm.coordinates') coords,
                'F' league
            FROM franchise
                NATURAL JOIN season_team pro_team
                INNER JOIN season_division ON pro_team.division_id = season_division.division_id
                NATURAL JOIN season_conference
                INNER JOIN season_team farm_team ON pro_team.team_id = farm_team.parent_team_id
                INNER JOIN team_name farm_team_name ON farm_team.team_name_id = farm_team_name.team_name_id
                INNER JOIN team_name pro_team_name ON pro_team.team_name_id = pro_team_name.team_name_id
            WHERE ((season_id = FHS_CurrentSeason(:league_id)) AND (conference_hidden = 0) AND (1 = :show_farm))
        )
    ) team_location_data
WHERE ((division_id IN (:division1, :divison2, :division3, :division4)) OR (league IN (:league)))
ORDER BY city ASC, league DESC

First off, we immediately see that a big part of this is a subquery that is the union of two queries. The first half gets the “pro” teams while the second half gets the “farm” teams and they’re pretty similar.

For the “pro” query, we get the division ID, the division name, the logo asset, the team name, the team label (a stub used to form team URLs), the name of the city, the location of the city, and we set the league to “P” (for pro, natch). We do this by selecting from the franchise table then joining to the team_name table, then to season_team and season_division and season_conference. These all have unique, matching keys, so we can do natural joins. Finally we get the logo by joining from the franchise table to the asset table. We only want the teams where the season_id for the conference is the current season (as determined by a custom function) and the conference is not hidden (farm teams are in a hidden conference).

On the “farm” side of things we get all the same values (we have to as we’re union-ing these queries together. But there’s no farm logo or team label, so those fields just get empty strings. And the farm team name has the pro team abbreviation appended to it (in parentheses) so there’s some string building there. To get all of this information, we once again start from franchise and join to season_team and season_division and season_conference. This gets us the pro team information and we’re going to be coming back to season_team so we alias it as pro_team. As mentioned, farm teams have a parent_team_id and that’s where re-joining onto season_team comes in, this time giving us the farm team (hence the farm_team alias). From here we have enough information to get the farm team name and the pro team name (and abbreviation) through a couple aliased joins to team_name. We once again want to check by season_id using that custom function and we want to make sure the conference is not hidden (because this is the pro team’s conference) and we only want to do this part if we’re asking for farm teams, so one of our parameters is a flag to check against.

The interface for the map allows for toggling pro teams by division and separately toggling farm teams. As such, after the union is done and we have our full data set, we have a where clause that checks the division IDs and the league (that misnamed pro/farm flag). Pro teams will be filtered in if their division ID appears, farm teams will be filtered in if “F” is passed in as the league parameter.

Writing this all out, I realize there’s probably a better way to handle that last bit of filtering. Farm teams are in a hidden division, so we could add it to the list of safe-listed divisions instead of looking at the misnamed league field. I think this is a relic of a time when farm team data was just a subset of team data and I never fully refactored this query to account for it. Making that change would require another join to season_division, this time from farm_team. I’ll add that to my backlog.