Joining Tables In SQL

In a typical relational database, data is often distributed in different tables. This is by design; normalization is utilized for scalability and data integrity. However, this leads to a common challenge. What if the data required is found in multiple different tables?

Joining tables!

That is where Joins come in. There are several types of Joins, and in order to make this as system-agnostic as possible, I will be covering Joins that are common to most database systems. For examples, we will be using the following tables below.

tblPlayers

Id	Name	Club	RoleId
1	Alisson Becker	Liverpool	1
2	Roberto Firmino	Liverpool	6
3	Erling Haaland	Manchester City	6
4	Bruno Fernandes	Manchester United	0
5	Harry Kane	Tottenham	6
6	Richarlisson	Tottenham	6
7	Andy Robertson	Liverpool	3
8	Édouard Mendy	Chelsea	1
9	Adam Smith	Bournemouth	4
10	Nathan Redmond	Southampton	5
11	James Tarkowski	Everton	2
12	David De Gea	Manchester United	1
13	Anthony Martial	Manchester United	0
14	John Stones	Manchester City	3
15	Adam Lallana	Brighton	0
16	Ben White	Arsenal	2
17	Marquinhos	Arsenal	0
18	James Maddison	Leicester City	0
19	Jonny Evans	Leicester City	0
20	Jamie Vardy	Leicester City	0

tblRoles

Id	Name	Type
1	Goalkeeper	DEF
2	Center-back	DEF
3	Full-back	DEF
4	Defensive Midfielder	MID
5	Attacking Midfielder	MID
6	Forward	ATK
7	Winger	MID

Since I'm a soccer nut, there's a table of football players, tblPlayers, and a table of roles, tblRoles. I know that modern football has evolved beyond static roles, but bear with me; this is only an example. In tblPlayers, the RoleId column is a foreign key to tblRoles, corresponding to that table's Id column.

Not all the rows in tblPlayers has a corresponding entry in tblRoles. Some of the entries in the RoleId column is a 0, which corresponds to nothing in tblRoles. While I do know the information, I have deliberately done this to illustrate excluded rows in different Joins.

Inner Joins

When one table is joined to another via an Inner Join, it means that all rows from the first table where the foreign key matches with an entry in the second table, are returned.

A sample INNER JOIN query

SELECT p.Id as Id, p.Name, r.Name as Role
FROM tblPlayers p  as Name
INNER JOIN tblRoles r
ON r.Id = p.RoleId

13 results

Id	Name	Role
1	Alisson Becker	Goalkeeper
2	Roberto Firmino	Forward
3	Erling Haaland	Forward
5	Harry Kane	Forward
6	Richarlisson	Forward
7	Andy Robertson	Full-back
8	Édouard Mendy	Goalkeeper
9	Adam Smith	Defensive Midfielder
10	Nathan Redmond	Attacking Midfielder
11	James Tarkowski	Center-back
12	David De Gea	Goalkeeper
14	John Stones	Full-back
16	Ben White	Center-back

There are only 13 out of 20 players in the result set. You can see that players such as James Maddison and Jamie Vardy do not make the list. This is because their RoleId is 0, which does not have a corresponding value in the Id column of tbLRoles.

Venn Diagram of INNER JOIN

Outer Joins

There are two kinds of Outer Joins - the Left Outer Join and the Right Outer Join. Normally, this is shortened to just "Left Join" or "Right Join" respectively. This means that all the rows in one table are returned, with null values from the other table where no data is available.

A sample LEFT JOIN query

SELECT p.Id as Id, p.Name, r.Name as Role
FROM tblPlayers p  as Name
LEFT JOIN tblRoles r
ON r.Id = p.RoleId

20 results

Id	Name	Role
1	Alisson Becker	Goalkeeper
2	Roberto Firmino	Forward
3	Erling Haaland	Forward
5	Harry Kane	Forward
6	Richarlisson	Forward
7	Andy Robertson	Full-back
8	Édouard Mendy	Goalkeeper
9	Adam Smith	Defensive Midfielder
10	Nathan Redmond	Attacking Midfielder
11	James Tarkowski	Center-back
12	David De Gea	Goalkeeper
13	Anthony Martial	NULL
14	John Stones	Full-back
15	Adam Lallana	NULL
16	Ben White	Center-back
17	Marquinhos	NULL
18	James Maddison	NULL
19	Jonny Evans	NULL
20	Jamie Vardy	NULL

In a Left Join, the first table takes precedence. Therefore, in the resultant table, all records in tblPlayers are returned - a full 20 out of 20. However, those who have a RoleId of 0 will have a null value in the Role column because there is no such value in the tblRoles table.

Venn Diagram of LEFT JOIN

A sample RIGHT JOIN query

SELECT p.Id as Id, p.Name, r.Name as Role
FROM tblPlayers p  as Name
RIGHT JOIN tblRoles r
ON r.Id = p.RoleId

13 results

Id	Name	Role
1	Alisson Becker	Goalkeeper
2	Roberto Firmino	Forward
3	Erling Haaland	Forward
5	Harry Kane	Forward
6	Richarlisson	Forward
7	Andy Robertson	Full-back
8	Édouard Mendy	Goalkeeper
9	Adam Smith	Defensive Midfielder
10	Nathan Redmond	Attacking Midfielder
11	James Tarkowski	Center-back
12	David De Gea	Goalkeeper
14	John Stones	Full-back
16	Ben White	Center-back
NULL	NULL	Winger

In a Right Join, the second table takes precedence. Therefore, in the resultant table, all records in tblRoles are present. There may be repeats where RoleId is referenced more than once. You will see that for the role "Winger", there is an entry; however, since there are no entries in tbPLayers that ave this role, the value for the Name column is null.

Venn Diagram of RIGHT JOIN

Last words on Joins!

Joins are incredibly useful when data needs to be assembled. And often, this is necessary to create a coherent and comprehensive data set.

What Joins to use, however, are the usual challenge. It is up to the individual programmer or data analyst to decide what is best for the current context.

Thanks for JOINing,
T___T

Web Tutorial: D3 Heatmap (Part 3/3)

It's time to fill in the Heatmap's rect tags with colors. The idea here is that all of them have the same color, but different opacity. So the highest value will be at 100% opacity, and a 0 value will be at 0% opacity, with all other values at varying proportional opacities. Oh, and no value will be plain black.

For this, we need the max property in the config object. We'll do it near the beginning of the setData() method.

We set the max property by using the max() method of the d3 object. For the first argument, that will be the cols property of the graphData object. The second argument will be a callback. The callback should have d as a parameter.

"setData": function ()
{
    var stat = d3.select("#ddlStat").node().value;
    
    config.max = d3.max(graphData.cols, function(d)
        {

        }
    );
    
    var container = d3.select(".hmChartContainer");

In the callback, declare maxStat and return it.

"setData": function ()
{
    var stat = d3.select("#ddlStat").node().value;
    
    config.max = d3.max(graphData.cols, function(d)
        {
            var maxStat;

            return maxStat;
        }
    );

    var container = d3.select(".hmChartContainer");

Now set maxStat. It will use, again, the max() method of the d3 object. So we will use d here, passing in the stats property of d. d, in this context, is the current element of the cols property of graphData. The second parameter is a callback with x as the parameter.

"setData": function ()
{
    var stat = d3.select("#ddlStat").node().value;
    
    config.max = d3.max(graphData.cols, function(d)
        {
            var maxStat = d3.max(d.stats, function(x)
                {
                    
                }
            );

            return maxStat;
        }
    );

    var container = d3.select(".hmChartContainer");

In here, return the element of x pointed to by stat. This means that the maximum value of the stat chosen in ddlStat is returned from each particular year, and then in turn, the maximum stat across all the years is returned. So the max property of config is the greatest selected stat (appearances or goals) from the entire dataset.

"setData": function ()
{
    var stat = d3.select("#ddlStat").node().value;
    
    config.max = d3.max(graphData.cols, function(d)
        {
            var maxStat = d3.max(d.stats, function(x)
                {
                    return x[stat];
                }
            );

            return maxStat;
        }
    );

    var container = d3.select(".hmChartContainer");

Now that we've defined the max property, we can proceed to define the fill attribute in the rect tags.

chart.selectAll("rect.rect_" + graphData.rows[r])
.data(graphData.cols)
.enter()
.append("rect")
.attr("x", function(d, i)
{
    return (i * config.dataWidth) + "em";
})
.attr("y", function(d)
{
    return (r * config.scale) + "em";
})
.attr("width", function(d)
{
    return (config.dataWidth) + "em";
})           
.attr("height", function(d)
{
    return (config.scale) + "em";
})
.attr("fill", function(d)
{

});

Define arr as the array returned after running stats through the filter() method, ensuring that the year property of each element of the player's stats matches the current element of the rows property, pointed to by r. There should be only one element.

.attr("fill", function(d)
{
    var arr = d.stats.filter((x) => { return x.year == graphData.rows[r];});
})

We then define opacity, giving it a value of 0. Then we return a color value string using rgba() with a color of red, but with opacity as the last value.

.attr("fill", function(d)
{
    var arr = d.stats.filter((x) => { return x.year == graphData.rows[r];});

    var opacity = 0;

    return "rgba(200, 0, 0, " + opacity + ")";
})

Now to define opacity. If the length of arr is not 1, that means there is no value for that player for that year. The color returned is a solid black.

.attr("fill", function(d)
{
    var arr = d.stats.filter((x) => { return x.year == graphData.rows[r];});

    var opacity = 0;

    if (arr.length == 1)
    {
        
    }
    else
    {
        return "rgba(0, 0, 0, 1)";
    }

    return "rgba(200, 0, 0, " + opacity + ")";
})

If there is data, we take the opacity by grabbing the first (and only) element of arr, get the stat required, and divide it by config.max. It should not be a zero, but feel free to add some defensive programming should you see fit. The value should be less than or equal to max, so the division should result in a value less than 1.

.attr("fill", function(d)
{
    var arr = d.stats.filter((x) => { return x.year == graphData.rows[r];});

    var opacity = 0;

    if (arr.length == 1)
    {
        opacity = (arr[0][stat] / config.max);
    }
    else
    {
        return "rgba(0, 0, 0, 1)";
    }

    return "rgba(200, 0, 0, " + opacity + ")";
})

Now you see this! The specification was red, but this is blue.

That's because the underlying color is blue, therefore messing with the opacity changed that. So just change the background color in the CSS, to a solid white.

.hmChartSvg
{
    width: 20em;
    height: 20em;
    float: left;
    background-color: rgba(255, 255, 255, 1);
}

And now you should see this!

While we're at it, change the opacity of all the other color specifications to zero opacity. And also remove the outline for rect tags. We won't need it.

.hmLegendYSvg
{
    width: 5em;
    height: 20em;
    float: left;
    background-color: rgba(0, 255, 0, 0);
}

.hmLegendYSvg text
{
    fill: rgba(255, 255, 0, 1);
    text-anchor: end;
}

.hmChartSvg
{
    width: 20em;
    height: 20em;
    float: left;
    background-color: rgba(255, 255, 255, 1);
}

.hmChartSvg rect
{
    stroke: rgba(255, 255, 255, 0);
    stroke-width: 1;
}

.hmFillerSvg
{
    width: 5em;
    height: 3em;
    float: left;
    background-color: rgba(0, 0, 0, 0);
}

.hmLegendXSvg
{
    width: 20em;
    height: 3em;
    float: left;
    background-color: rgba(255, 0, 0, 0);
}

There you go.

Adding animation

Here's one final touch! Add these few lines so that the opacity will start out at 0, but graduate to the true opacity 2 seconds later.

.attr("height", function(d)
{
    return (config.scale) + "em";
})
.attr("fill", function(d)
{
    return "rgba(200, 0, 0, 0)";
})
.transition()
.duration(2000)
.attr("fill", function(d)
{
    var arr = d.stats.filter((x) => { return x.year == graphData.rows[r];});

    var opacity = 0;

    if (arr.length == 1)
    {
        opacity = (arr[0][stat] / config.max);
    }
    else
    {
        return "rgba(0, 0, 0, 1)";
    }

    return "rgba(200, 0, 0, " + opacity + ")";
});

Or we could have some fun with this, and do this instead in the duration() method. This is basically sort of repeating the code for determining opacity. The default returned is still 2 seconds, but if there is a new opacity it's multiplied by the default. Hence, the higher the opacity, the slower the animation.

.duration(/*2000*/function(d)
{
    var arr = d.stats.filter((x) => { return x.year == graphData.rows[r];});

    var opacity = 2000;

    if (arr.length == 1)
    {
        opacity *= (arr[0][stat] / config.max);
    }

    return opacity;                   
})

Or try this! Use the index, i. Change opacity to 1 second, and multiply it by the value of i. This way, all the Heatmap's leftmost columns will appear first, all the way to the right!

.duration(function(d, i)
{
    //var arr = d.stats.filter((x) => { return x.year == graphData.rows[r];});

    var opacity = 1000;

    //if (arr.length == 1)
    //{
    //   opacity *= (arr[0][stat] / config.max);
    //}

    return opacity * i;                   
})

I really, truly hope you have some fun with this. There's a great deal of experimentation to be had.

Transparently yours,
T___T

Web Tutorial: D3 Heatmap (Part 2/3)

Let us concentrate on filling up the left and bottom of the heatmap. These are the Y and X axes, respectively. The Y-axis will show seasons, while the X-axis will show players.

Start on legendY, using the rows array of the graphData object as data, and append text tags. This should be a straightforward value.

filler
.style("width", function(d)
{
    return config.legendYWidth + "em";
})
.style("height", function(d)
{
    return config.legendXHeight + "em";
});

legendY.selectAll("text")
.data(graphData.rows)
.enter()
.append("text")
.text(function(d)
{
    return d;
});

Now we will set the x attribute. We'll use half the value of legendYWidth.

legendY.selectAll("text")
.data(graphData.rows)
.enter()
.append("text")
.attr("x", function(d)
{
    return (config.legendYWidth / 2) + "em";
})
.text(function(d)
{
    return d;
});

Now set the y attribute. Logically, the text will appear lower with each iteration of the array. So we will use the index, i. We'll add 1 to i to make sure that the value is at least 1, then multiply that by the scale property. And because we want the text position to be vertically in the middle, we should subtract half the value of scale from that result.

legendY.selectAll("text")
.data(graphData.rows)
.enter()
.append("text")
.attr("x", function(d)
{
    return (config.legendYWidth / 2) + "em";
})
.attr("y", function(d, i)
{
    return (((i + 1) * config.scale) - (config.scale / 2)) + "em";
})
.text(function(d)
{
    return d;
});

Finally, make this change to the CSS. The text tags in the hmLegendYSvg class should use the default font size.

.hmLegendYSvg text
{
    fill: rgba(255, 255, 0, 1);
    text-anchor: end;
    /*font-size: 0.5em;*/
}

The green column is now filled with years in yellow font!

Time to work on the X-axis. As before, we're adding text tags. We are using the cols array of the graphData object. This time, d is an object rather than a string, so we return the title attribute of d.

legendY.selectAll("text")
.data(graphData.rows)
.enter()
.append("text")
.attr("x", function(d)
{
    return (config.legendYWidth / 2) + "em";
})
.attr("y", function(d, i)
{
    return (((i + 1) * config.scale) - (config.scale / 2)) + "em";
})
.text(function(d)
{
    return d;
});

legendX
.selectAll("text")
.data(graphData.cols)
.enter()
.append("text")
.text(function(d)
{
    return d.title;
});

The x attribute is the horizontal positioning of the text. This will increase with each iteration of the data, so we use the index, i. We multiply the value of i by the dataWidth property. This time, we don't add 1 beforehand because a 0 is acceptable. And after that, we add half the value of dataWidth. Because we want the text to begin from the center line. If you check the CSS for the text-anchor property for text tags within hmLegendXSvg CSS class, it is set to middle. The combination of this ensures that the text tags, no matter how long they are, always align to the middle of that allotted space.

legendX
.selectAll("text")
.data(graphData.cols)
.enter()
.append("text")
.attr("x", function(d, i)
{
    return ((i * config.dataWidth) + (config.dataWidth / 2)) + "em";
})
.text(function(d)
{
    return d.title;
});

And for the y attribute, we use half the value of legendXHeight.

legendX
.selectAll("text")
.data(graphData.cols)
.enter()
.append("text")
.attr("x", function(d, i)
{
    return ((i * config.dataWidth) + (config.dataWidth / 2)) + "em";
})
.attr("y", function(d)
{
    return (config.legendXHeight / 2) + "em";
})
.text(function(d)
{
    return d.title;
});

And now you can see the player names appear on the X-axis!

Putting in the data

Let us begin with making some placeholders. We need a For loop to iterate through the elements of the rows array of graphData.

legendX
.selectAll("text")
.data(graphData.cols)
.enter()
.append("text")
.attr("x", function(d, i)
{
    return ((i * config.dataWidth) + (config.dataWidth / 2)) + "em";
})
.attr("y", function(d)
{
    return (config.legendXHeight / 2) + "em";
})
.text(function(d)
{
    return d.title;
});

for (let r = 0; r <= graphData.rows.length; r++)
{

}

And for each element, we append a row of rect tags into chart. In the selectAll() method, we just give it a class which we will never use, just to give each row of elements some uniqueness. For data, we use the cols array of the graphData object.

for (let r = 0; r <= graphData.rows.length; r++)
{
    chart.selectAll("rect.rect_" + graphData.rows[r])
    .data(graphData.cols)
    .enter()
    .append("rect");              
}

Each rect uses dataWidth as width and scale as height.

for (let r = 0; r <= graphData.rows.length; r++)
{
    chart.selectAll("rect.rect_" + graphData.rows[r])
    .data(graphData.cols)
    .enter()
    .append("rect")
    .attr("width", function(d)
    {
        return (config.dataWidth) + "em";
    })           
    .attr("height", function(d)
    {
        return (config.scale) + "em";
    });                 
}

For the x attribute, the rect will appear further to the right progressively per row. So we use i as the index and multiply it by dataWidth (which is the width of each rect).

for (let r = 0; r <= graphData.rows.length; r++)
{
    chart.selectAll("rect.rect_" + graphData.rows[r])
    .data(graphData.cols)
    .enter()
    .append("rect")
    .attr("x", function(d, i)
    {
        return (i * config.dataWidth) + "em";
    })
    .attr("width", function(d)
    {
        return (config.dataWidth) + "em";
    })           
    .attr("height", function(d)
    {
        return (config.scale) + "em";
    });                 
}

For the y attribute, the row will appear lower for each iteration of the rows array, so have the value as r multiplied by scale (which is the height of each rect).

for (let r = 0; r <= graphData.rows.length; r++)
{
    chart.selectAll("rect.rect_" + graphData.rows[r])
    .data(graphData.cols)
    .enter()
    .append("rect")
    .attr("x", function(d, i)
    {
        return (i * config.dataWidth) + "em";
    })
    .attr("y", function(d)
    {
        return (r * config.scale) + "em";
    })
    .attr("width", function(d)
    {
        return (config.dataWidth) + "em";
    })           
    .attr("height", function(d)
    {
        return (config.scale) + "em";
    });                 
}

In the CSS, this class is no longer needed. Remove the properties.

.hmChartSvg text
{
    /*
    fill: rgba(255, 255, 0, 1);
    text-anchor: middle;
    font-weight: bold;
    */
}

Change it to target rect tags instead, and add specifications for stroke and stroke-width. For this one, I am using a white color.

.hmChartSvg rect
{
    /*
    fill: rgba(255, 255, 0, 1);
    text-anchor: middle;
    font-weight: bold;
    */
    stroke: rgba(255, 255, 255, 1);
    stroke-width: 1;
}

So now you can see the individual rect tags!

Next

The basic placeholders are there, along with X and Y axes. We will follow up with filling in the Heatmap with colors!

Web Tutorial: D3 Heatmap (Part 1/3)

Welcome to another episode of D3 goodness!

Since 2018, I've been toying around with D3 and getting it to produce various charts - bar, line and pie - using Liverpool FC's football statistics. Today, I will focus on one more - the Heatmap. Heatmaps are great at visually representing two-dimensional data. With the other kinds of charts earlier mentioned, we were only able to view one dimension at a time whether it was by season or by player. With the Heatmap, we will visualize the data by season and player.

Recycling code

The layout will be largely the same as what we used for the Bar Chart. So let's take the code wholesale and make some changes. We will cut out whole swathes of code, but preserve what can be reused later.

For the HTML, we begin by changing the title.

<title>D3 Heatmap</title>

The styling will be changed because we want to temporarily change some values. Other changes will be less temporary. For instance, for the sake of good naming conventions, let's change every instance of "bar" to "hm". And since we will have two legends instead of a scale and a legend, let's change every instance of "Legend" to "LegendX" and every instance of "Scale" to "LegendY". Also, make sure that the styling for hmChartSvg's rect elements is empty for now. The background colors of the various placeholders are set to full opacity for the time being.

And before you do any of that, remove the classes barChartDataMean, barChartLine and barChartFadedLine. We won't be using any of those.

<style>
    body
    {
        font-size: 12px;
        font-family: arial;
    }

    .hmDashboard
    {
        height: 2em;
        width: 100%;
        text-align: center;
    }

    .hmChart
    {
        outline: 1px solid #000000;
        background-color: rgba(200, 0, 0, 1);
    }

    .hmChartContainer
    {
        margin: 0 auto 0 auto;
    }

    .hmLegendYSvg
    {
        width: 5em;
        height: 20em;
        float: left;
        background-color: rgba(0, 255, 0, 1);
    }

    .hmLegendYSvg text
    {
        fill: rgba(255, 255, 0, 1);
        text-anchor: end;
        font-size: 0.5em;
    }

    .hmChartSvg
    {
        width: 20em;
        height: 20em;
        float: left;
        background-color: rgba(0, 0, 255, 1);
    }

    .hmChartSvg text
    {
        fill: rgba(255, 255, 0, 1);
        text-anchor: middle;
        font-weight: bold;
    }  

    .hmChartSvg rect
    {

    }

    .hmFillerSvg
    {
        width: 5em;
        height: 3em;
        float: left;
        background-color: rgba(0, 0, 0, 1);
    }

    .hmLegendXSvg
    {
        width: 20em;
        height: 3em;
        float: left;
        background-color: rgba(255, 0, 0, 1);
    }

    .hmLegendXSvg text
    {
        fill: rgba(255, 255, 0, 1);
        text-anchor: middle;
        font-weight: bold;
    }  
    /*
    .barChartDataMean
    {
        stroke: rgba(0, 0, 0, 1);
        stroke-width: 1px;
        stroke-dasharray: 1, 5;
    }  

    .barChartLine
    {
        stroke: rgba(255, 255, 0, 1);
        stroke-width: 1px;
    }   

    .barChartFadedLine
    {
        stroke: rgba(255, 255, 0, 0.2);
        stroke-width: 1px;
    }  
    */
</style>

In the HTML, aside from name changes to the divs, you also want to remove the drop-down list for years.

<div class="hmChartContainer">
    <div class="hmDashboard">
        <!--                 
        <select id="ddlYear">

        </select>
        -->

        <select id="ddlStat">

        </select>
    </div>

    <div class="hmChart">
        <svg class="hmLegendYSvg">

        </svg>

        <svg class="hmChartSvg">

        </svg>

        <br style="clear:both"/>

        <svg class="hmFillerSvg">

        </svg>

        <svg class="hmLegendXSvg">

        </svg>
    </div>
</div>

In the script tag, leave graphData alone. For config, make changes to the following properties. scaleWidth has been renamed to legendYWidth and legendHeight has been renamed to legendXHeight. mean has been removed as a property.

"scale": 12,
"dataWidth": 10,
"dataSpacing": 2,
"legendYWidth": 10,
"legendXHeight": 4,
"max": 0,
//"mean": 0,

For the setData() method, I could go through step by step what changes need to be made, but it would be easier to just clear everything out and start afresh.

"setData": function ()
{

}

Outside of the config object, remove this from the rest of the code. We will still need to determine the max property of the config object, just not here. And we no longer need the drop-down list for years.

/*
config.max = d3.max(graphData.cols, function(d)
{
    var maxStat = d3.max(d.stats, function(x)
    {
        return (x.goals > x.appearances ? x.goals : x.appearances);
    }
    );

    return maxStat;
}
);

var ddlYear = d3.select("#ddlYear");

ddlYear.selectAll("option")
.data(graphData.rows)
.enter()
.append("option")
.property("selected", function(d, i)
{
    return i == 0;
})
.attr("value", function(d)
{
    return d;
})
.text(function(d)
{
    return d;
});
*/

var ddlStat = d3.select("#ddlStat");

ddlStat.selectAll("option")
.data(graphData.stats)
.enter()
.append("option")
.property("selected", function(d, i)
{
    return i == 0;
})
.attr("value", function(d)
{
    return d;
})
.text(function(d)
{
    return d;
});

config.setData();

//d3.select("#ddlYear").on("change", function() { config.setData(); });
d3.select("#ddlStat").on("change", function() { config.setData(); });

Let's make a change to getChartHeight(). Add datalength as a parameter, and use it to determine the final result.

"getChartHeight": function(datalength)
{
    //return (this.max * this.scale * 1.5);
    return (datalength * this.scale);
},

And a change to getChartWidth(). This will be highly simplified.

"getChartWidth": function(datalength)
{
    //return (datalength * (this.dataWidth + this.dataSpacing)) + (datalength * 0.5);
    return (datalength * this.dataWidth);
},

Now we will get to work on setData(). Begin by declaring stat and setting it to the selected value of the ddlStat drop-down list.

"setData": function ()
{
    var stat = d3.select("#ddlStat").node().value;
}

Next, we declare height and width. height is calculated using the getChartHeight() method, and we will pass in, as an argument, the number of elements in the rows array of the graphData object. Likewise, width is calculated using the getChartWidth() method, and we will pass in, as an argument, the number of elements in the cols array of the graphData object. Remember that this is a chart with two axes - one for seasons and one for players.

"setData": function ()
{
    var stat = d3.select("#ddlStat").node().value;
    
    var height = config.getChartHeight(graphData.rows.length);
    var width = config.getChartWidth(graphData.cols.length);
}

While we're at it, let's declare some variables, assigning to them the elements found in the HTML. These will ultimately make up the visual structure of our chart.

var stat = d3.select("#ddlStat").node().value;

var container = d3.select(".hmChartContainer");
var wrapper = d3.select(".hmChart");
var legendY = d3.select(".hmLegendYSvg");
var chart = d3.select(".hmChartSvg");
var legendX = d3.select(".hmLegendXSvg");
var filler = d3.select(".hmFillerSvg");

var height = config.getChartHeight(graphData.rows.length);
var width = config.getChartWidth(graphData.cols.length);

Now what we do is set the width of container. It will use width, and the width of the Y-axis, the legendYWidth property. We don't set the height because soon we will be setting the height for its child, wrapper, which will cause container to expand vertically.

var height = config.getChartHeight(graphData.rows.length);
var width = config.getChartWidth(graphData.cols.length);

container
.style("width", function(d)
{
    return (width + config.legendYWidth) + "em";
});

For wrapper, we set height. This will use height and the height of the X-axis, the legendXHeight property.

container
.style("width", function(d)
{
    return (width + config.legendYWidth) + "em";
});

wrapper
.style("height", function(d)
{
    return (height + config.legendXHeight) + "em";
});

Now take a look! The deep red portion is wrapper. The blue, green, scarlet and black portions are the rest of the placeholders that have not yet had their heights and widths specified. Also notice the drop-down list at the top.

Let us now move on to the rest of the placeholders. We want the Y-axis to use the legendYWidth property for width, and use height for its height.

wrapper
.style("height", function(d)
{
    return (height + config.legendXHeight) + "em";
});

legendY
.style("width", function(d)
{
    return config.legendYWidth + "em";
})
.style("height", function(d)
{
    return height + "em";
})
.html("");

You see the green portion has expanded to what we have specified! That is our Y-axis.

chart is straightforward. We just use width and height.

legendY
.style("width", function(d)
{
    return config.legendYWidth + "em";
})
.style("height", function(d)
{
    return height + "em";
})
.html("");

chart
.style("height", function(d)
{
    return height + "em";
})
.style("width", function(d)
{
    return width + "em";
})
.html("");

The blue portion is chart, and it is taking shape.

We now specify the size of legendX.

chart
.style("height", function(d)
{
    return height + "em";
})
.style("width", function(d)
{
    return width + "em";
})
.html("");

legendX
.style("height", function(d)
{
    return config.legendXHeight + "em";
})
.style("width", function(d)
{
    return width + "em";
})
.html("");

The scarlet part, the X-axis, has now expanded! But it will look off until we finish the job with filler.

And here we have it...

legendX
.style("height", function(d)
{
    return config.legendXHeight + "em";
})
.style("width", function(d)
{
    return width + "em";
})
.html("");

filler
.style("width", function(d)
{
    return config.legendYWidth + "em";
})
.style("height", function(d)
{
    return config.legendXHeight + "em";
});

The black portion, filler, has had its size specified, and now we're in business. The deep red portion is no longer visible because it has been covered completely. Good job!

Next

We will work on the X and Y axes.

Five Unpleasant Things About Working With Front-end Technology

In tech, there are several technologies at each layer of the stack. The nuances are usually not covered by tech hiring, and this is usually divided into front-end and back-end. Today, my intention is to examine the two, especially front-end technology. And here are five reasons why you might not want to make front-end technology your career.

1. Rapid evolution

There are several aspects to front-end technology. There's your standard HTML, CSS and JavaScript... and that alone can be a nightmare to keep up with. HTML, if you're up to speed with the most commonly used features of HTML5, not so much. For CSS, the preprocessor packages and CSS3 updates can be slightly more of a pain in the ass.

Multiplying like guppies

But JavaScript? If you elect to go down that rabbit hole, JavaScript is constantly evolving. The core language alone has been through update after update, The jQuery library as well. But all the front-end frameworks - ReactJS, AngularJS, VueJS, just to name a few - are also constantly going through evolution. And new frameworks seem to be coming out of the goddamn woodwork all the time!

And that's only the tech part. The design part is certainly nothing to sniff at. Learning fonts and color schemes is only the tip of the proverbial iceberg. Design patterns and anti-patterns evolve all the time. A typical website in the 90s will look markedly different ten years later, and another ten years after that.

This sounds fun if you're a hobbyist. When you do this shit for a living, not so much.

2. Uncontrollable environment

Front-end code runs on web browsers. Each of which comes with user-controllable functionality a front-end developer may not have catered for.

Yes, you read that right. User-controllable. Not developer-controllable.

Your typical web browser may come with a Dark Mode. There may almost definitely will be controls to increase or decrease font size. This is going to mess with all your layout specifications.

What about Dark Mode?!

Add in the fact that your users may screw with your carefully crafted interface by refreshing the browser insistently, pressing the Back button and using browser functions in all sorts of unruly ways. It's certainly not like a back-end interface where there are limits to input.

3. Testing nightmare

Mobile technology in the past decade has seen a proliferation of different screen sizes - tablets and phones - in addition to the standard screen sizes. In addition, there may be differences in how certain browsers render HTML, CSS and JavaScript. These differences have largely been eliminated with the demise of Internet Explorer, but some remain.

A plethora of different devices.

All of this adds up to an almost untenable amount of user interface testing for the optimal amount of exposure to the market. It is not an enviable position.

Front-end frameworks and libraries such as Twitter Bootstrap, jQuery and such, mitigate much of the insanity. But these issues have remained through the decade and will likely continue to grow the more mobile technology evolves.

4. Vague KPIs

In back-end technology, things are binary. Input is defined, and output is defined. The output is either correct, or it is not. It either is produced quickly enough, or it is not. Things either conform to security standards, or they do not.

Back-end input and
output is simpler.

Front-end technology has all that, true. It also encompasses a whole host of KPIs that are, for the most part, subjective in nature. User experience. Aesthetic beauty. Ease of use. These are guaranteed to vary from user to user and therefore, there is no way to obtain a perfect score.

Imagine being in a situation where you have to live with having less than total success. That is front-end technology.

5. Everyone has an opinion

This is somewhat related to the last point. In back-end technology, laypeople generally have better sense than to ever touch this, because they have little to no understanding of the stack and they have no tech qualifications.

Everyone and their dog.

In front-end technology, however, which is mostly visual, suddenly everybody and their dog has an opinion. Suddenly, everyone feels the need to chime in on how stuff should look and feel like. Nobody is shy about commenting because, well, you don't need tech qualifications to talk about something which is largely visual. It's subjective, so there are no wrong answers.

This makes your work a lot more difficult, when you have to take so much extra feedback into account. And when a sizeable portion of that feedback comes from laypeople, this makes things even more difficult because laypeople don't quite speak the same language techies do, and they may not have a good grasp of what technically makes sense and what doesn't.

In conclusion

Front-end technology is far, far from a bed of roses.

Still, if it still looks intriguing, let nothing deter you from your path. The important thing is not to go in blind, and be aware of all the pitfalls that await you.

Just being up front!
T___T

ONE Pass to rule them all

Singapore introduced the Overseas Networks & Expertise (ONE) Pass was two weeks ago. Under this scheme, any foreigner earning a fixed monthly salary of at least 30,000 SGD will be eligible. Companies wishing to hire such an individual will no longer need to comply with Fair Consideration Framework (FCF), where job advertising for suitable local candidates is concerned. An individual holding this ONE Pass will not be subjected to the requirements of the Complementarity Assessment Framework (COMPASS)  regulations.

As an economic digit, it is probably prudent to try and unpack this.

Perceived benefits

The ultra-large foreign firms looking to begin (or continue) investing in Singapore with a local presence, will now have an easier time of it. For roles requiring that salary range, which can be a challenge to hire for even without having to conform to local existing laws regarding fair hiring practices, it is now a lot easier to expedite the process. This in turn makes it easier for them to operate in Singapore. And that (hopefully) translates to more jobs for qualified Singaporeans, though probably not at that exalted salary range.

Welome to Singapore!

This also serves to attract top talent to Singapore, and retention of that talent. The benefits of that are obvious.

Also, every foreigner that gets hired under this scheme are going to have to stay in Singapore. Unlike locals, they don't have a permanent home on this island and will need a place to stay. That means money is going to be spent in Singapore. There's no downside to that.

Perceived drawbacks

With any kind of new legislation, there are bound to be bad actors trying to be slick and abuse the system. The Singapore Government is going to have to exercise extra vigilance in ensuring that this remains at a minimum. How they are easily going to do that, remains beyond my ken.

A common refrain on Social Media is that this takes away potential jobs from local talents. The belief is that the presence of such highly-paid foreign talent will deprive local talents of these opportunities. If one ignores the fact that such local talent would in all likelihood already be gainfully employed or themselves being a foreign talent in other countries, this might hold water.

This argument
does not hold water.

At the risk of sounding elitist...

I find the protests on behalf of local talent, hollow at best. We are speaking of local talent who can command at least 30,000 SGD a month. I'm not a betting man, but I would wager my last Singapore dollar that the average shmuck ranting on Social Media about the ONE Pass, earns maybe a tenth of that.

People who are capable of earning more than 30,000 SGD a month, are surely equally capable of advocating for themselves. These people certainly do not need others to speak up for them, especially those who have not and will in all probability will never see that kind of money in six months, let alone one.

This is also why I am against people who have never worked tech jobs before, presuming to speak for my industry. Stay in your lane.

One last thing...

When I first saw this news, my first thought was: Pfft. 30,000 SGD isn't even half what I make. Annually, that is.

Jokes aside, there were predictably plenty of complaints from naysayers. And - surprise, surprise, anti-foreigner rhetoric. This isn't a new phenomenon by any means. It is little more than envy from people who think that their comparative lack of earning power would be addressed not by bettering themselves, but by eliminating all competition.

Newsflash: if you're busy being bitter on Social Media, these foreign talents are not your competition. Professionally, you're probably a long way from even being in the same weight class. Deal.

The ONE and Only,
T___T

The Good Old Variable Swap, Redux

Some time back, I wrote a short piece, a how-to on the classic variable swap. Today is a good time to revisit that. As with the last time, code samples are in QBasic.

I was demonstrating this in a class, and one of the students had this bright idea: would this method work with multiplication and division, instead of addition and subtraction?

His idea was as follows.

DIM a = 3
DIM b = 2

a = a * b
b = a / b
a = a / b

And my answer was an unequivocal no. This is why.

In his example, a was 3 and b was 2. That would work. But consider this: what if either a or b was 0? Let's try both times.

Example 1: a = 0, b = 2

DIM a = 0
DIM b = 2

'a = 0 * 2 = 0
a = a * b

'b = 0 / 2 = 0
b = a / b

'a = 0 / 0
a = a / b

Example 2: a = 3, b = 0

DIM a = 3
DIM b = 0

'a = 3 * 0 = 0
a = a * b

'b = 0 / 0
b = a / b

a = a / b

In either case, at some point we are going to end up in a scenario where a division by zero would be attempted. And as we all know, once a division by zero is attempted, an exception occurs.

The dreaded zero.

We could get around this limitation by doing a Try-catch, and assuming the value of 0 once the exception is thrown. But that would add a level of complexity to what is supposed to be a very simple formula.

The takeaway

It was a nice try, and I like to encourage that. An experimental and curious mindset is always valuable to a programmer. What's important is that we always bear certain basics in mind.

Happy pr0gramming,
T___T