— MySQL
What are the field()
and elt()
functions used for in MySQL? I recently came across them when implementing a voting system and found them pretty useful for doing calculations in queries with group by
clauses.
Let's start with the definitions from the docs:
FIELD() returns the index (position) of str in the str1, str2, str3, ... list. Returns 0 if str is not found.
SELECT field('orange', 'banana', 'apple', 'orange');-- Returns 3
ELT() returns the Nth element of the list of strings: str1 if N = 1, str2 if N = 2, and so on. ELT() is the complement of FIELD().
SELECT elt(3, 'banana', 'apple', 'orange');-- Returns 'orange'
OK, now that we understand the basics let's look at a situation where we might want to reach for these functions.
Assume we have a database table user_membership
that records users and the memberships they belong to within an organization. A user can have multiple memberships (ie. there is a one-to-many relationship between users and memberships). Here are a few example rows from our contrived database table:
id | userId | membership |
---|---|---|
1 | 101 | board |
2 | 101 | staff |
3 | 102 | staff |
4 | 102 | general |
5 | 103 | general |
6 | 104 | general |
We can see that user 101 has 'board' and 'staff' memberships, user 102 has 'staff' and 'general' memberships, and user 103 and 104 have just 'general' memberships.
Now imagine we are given a business rule that requires us to retrieve the number of users by membership, but only counting users once by their priority membership ('board' > 'staff' > 'general'). We can achieve this by using the field()
function and a subquery.
SELECT userId, membership AS priorityMembership FROM user_membershipWHERE field(membership, "board", "staff", "general") = ( SELECT min(field(membership, "board", "staff", "general")) FROM user_membership t WHERE t.userId = user_membership.userId)GROUP BY userId;
However, there is a better and more perfomant way to make this same calculation using, you guessed it, elt()
. It allows us to convert the index value back to the corresponding string at the index number specified in the list of arguments.
SELECT userId, elt(min(field(membership, "board", "staff", "general")), "board", "staff", "general") AS priorityMembership, min(field(membership, "board", "staff", "general")) AS rankFROM user_membershipGROUP BY userId;
userId | priorityMembership | rank |
---|---|---|
101 | board | 1 |
102 | staff | 2 |
103 | general | 3 |
104 | general | 3 |
From there, we can group on priority membership to get the counts.
SELECT priorityMembership, count(userId) as userCountFROM (SELECT userId, elt(min(field(userGroup, "board", "staff", "general")), "board", "staff", "general") AS priorityMembership, min(field(userGroup, "board", "staff", "general")) AS rank FROM user_membership GROUP BY userId)GROUP BY priorityMembership;
priorityMembership | userCount |
---|---|
board | 1 |
staff | 1 |
general | 2 |
A couple of gotchas to note about the field()
and elt()
functions:
field()
returns 0 if the first argument is NULL because NULL fails equality comparison with any value.elt()
returns NULL if the first argument is less than 1 or greater than the number of string arguments.