MySQL Subtree Search

The need to store hierarchies in SQL is pretty common. Organisational unit databases, staff or position reporting lines, group membership information, … the list goes on.

Dealing with them directly in SQL is far less common. Mostly because these schemas generally define some sort of graph (usually a tree), and SQL returns flat results (tuples, or less formally rows).

This post isn’t meant to cover this complex topic, as there’s already far better places to go for that*. Instead, I want to cover an interesting “solution” to the problem of getting MySQL to return all the nodes in a tree (represented by a [ID, …, ParentID] schema) that sit somewhere under a given parent node, recursively.

Computer Lab Innovations conference talk

Improving Learning Infrastructure and the Student Experience

I gave a talk at #QUESTnet2016 today on a couple of the things I and the rest of the team at work¹ have been creating over the last 5 or 10 years!

The bottomless horror of social choice theory

Vote Greens!

I was recently given the task of creating a voting page for our group at work, to help us make decisions on important questions such as where to go for staff parties or how to interpret “end dates” (first day something has ended? last day it’s still valid? place votes now!)

The usual process in lots of online polls I’ve seen is to just give people a set of radio buttons and let them pick the option they want. This works pretty well for two options (the typical “yes/no” polls you see in so many vacuous articles on the websites of rubbish tabloids) but as the number of options increases and the gap between preferences narrows, I started to notice that the “results” didn’t tell me much about what the group as a whole actually preferred.

As usual, a little research on the problem quickly led me to the realisation that we’re all doing it wrong, and that yet again, something that looks as though it should be simple is in fact a doorway to unending fractal complexity, where just like mathematical logic, the ultimate door prize is a proof of the impossibility of what we’d set out to achieve.

Selecting specific rows from a group

How can I select only the row that has the smallest or largest value in a particular column within a group of rows?

It’s a fairly common question on google or stackoverflow which has inspired articles addressing the issue at least as far back as 2006, and whilst the quality of the answers varies, they all ultimately boil down to the same thing: there are no simple or general ways to do this with SQL, but there are a couple of approaches that work reasonably well in various situations.

Why?

Why am I doing this? Well, I’ve been thinking about doing some content for a while now, and the long-form, detailed, and rather specific kind I had in mind isn’t really suitable for facebook, twitter, or any of the other services or platforms I use.

I’m a Software Engineer, so there will definitely be articles I write on that topic, but I also tinker and tackle personal projects, so no doubt I’ll cover that as well.

Hopefully you get something out of this; I know I will.