Andrew Bishop

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(str,str1,str2,str3,...)

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(N,str1,str2,str3,...)

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:

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_membership
WHERE 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 rank
FROM user_membership
GROUP BY userId;

From there, we can group on priority membership to get the counts.

SELECT priorityMembership, count(userId) as userCount
FROM
  (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;

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.