Cargo’s list-type fields exist in the extension to make some things more convenient for the most simple of use cases, but they are almost never the best way to accomplish a task, and you should be aware that they are a lot more complicated than they claim to be. List-type fields are technical debt; while their use can make things easy at first, as your tables grow more complicated, it’s extremely likely that this use will become an impediment to future growth, and you will have to refactor your code.

This warning is not to say that you should never use list-type fields; rather, as soon as you are comfortable reading this article (if you aren’t, it’s okay, come back when you are), you should do so, and make an effort to fully understand what’s going on beneath the interface of these fields.

Furthermore, this article is not intended to pass judgment on when you should and should not use said fields. That will come in a later post; however, that later post will be much easier for me to write if I can refer back to an article that explains how these fields work in the first place. The answer is a lot more complicated than “never” even if you know what you’re doing, because your users might not, and list-type fields are often going to be more friendly to your users. For now, we can go with “rarely.”

Introduction

List-type fields are Cargo’s biggest lie.

• List-type fields are not lists.
• List-type is not a type.
• List-type fields are not fields.

(You may remember some familiarity to my article on bot passwords.)

But the issue goes beyond semantics.

• The names and way of joining the child table to its parent explicitly is extremely opaque (thus this article!) and not exposed by the interface at all; even if you know how to do it, you have to remember a lot of relatively random names of things
• Until very recently (I patched it!) the delimiter of a list-type field was completely hidden from the user interface, and so it was impossible to know which string you should split the returned string on without looking at the wiki’s source code. Now you can see it at Special:CargoTables/$tableName.
• There are some weird case-sensitivity things that are inconsistent between Lua and wikitext (as of publication of this article).

List-type fields are not lists

What I would expect to be returned from the API or from a Lua call when I query a list-type field is, you know, a list. If I write the following code:

1
2
3
4
5
6

local p = {}
function p.main()
	local result = mw.ext.cargo.query('ScoreboardPlayers', 'SummonerSpells,Name',{where="SummonerSpells__FULL LIKE '%Smite%'"})
    mw.logObject(result)
end
return p

And then p.main() in the Lua console, I get the following output:

table#1 {
  table#2 {
    ["Name"] = "alter",
    ["SummonerSpells"] = "Cleanse,Smite",
  },
  table#3 {
    ["Name"] = "Hyami",
    ["SummonerSpells"] = "Cleanse,Smite",
  },
  table#4 {
    ["Name"] = "Hyami",
    ["SummonerSpells"] = "Cleanse,Smite",
  },
}

Note that I’m getting strings as the value of ["SummonerSpells"], and NOT lists. The same happens when you query the API. This behavior is fairly reasonable, of course; what’s stored in SQL is indeed a string with all of the values concatenated together by the delimiter. But list-type fields are strings, not lists.

List-type is not a type

There is no type in SQL called “list-type.” As we saw before, the field that holds the string of concatenated data is a string. Of course, the full type declaration will be something like List (,) of Integer where , is the delimiter and Integer is the type of each item.

• There will be a field called MyList__FULL of type String that will be a comma-separated list of the numbers.
• There will also, separately, be Integer-typed data stored (more about that in the next section).

A brief note on some other “fake” field types

Note that List is not the only Cargo type that’s a lie. The following types are also not real types in SQL and therefore behave differently when queried from Lua and via the API from how they behave when queried in a regular parser-function #cargo_query|format=table query:

• Page
• Wikitext string
• Wikitext
• File
• URL
• Email
• Rating

Unlike list-type fields, these fields confer no real benefit over String or Text that you can’t get yourself with a bit of custom formatting (maybe Rating is convenient if you really want that), so I highly discourage their use altogether.

The fields Start date/End date and Start datetime/End datetime are a bit different; they can provide a real advantage when working with the Calendar format, so I think they’re fine to use if you know what you’re doing and require them.

I’m also not too familiar with Cargo’s maps displays, which require the Coordinates data type.

List-type fields are not fields

Now we get into the heart of the matter. List-type fields are in fact tables. There is also a field, of course, but the important part is an entirely separate, hidden table that Cargo doesn’t want you to know about and tries to hide from you and trick you into thinking doesn’t exist.

Let’s talk about this table.

A toy example

Once again, we have our List (,) of Integer-typed field. This time we’re gonna name it, though. We’ll call it DieRolls. Our parent table is called BossFights. Each row of our table represents a round of a boss fight. We have fields like HealthLost, DamageDealt, TotalEnemiesKilled, PotionsUsed, GoldGained. We also want to track the individual results that the dice gave, so we have one list-type field called DieRolls that’s a list of integers without any context, so that the DM can do a statistical analysis and see if she thinks that the players are cheating. This game only uses d6’s in combat, so all of the numbers will be 1-6.

Behind the scenes, Cargo has created a second table called BossFights__DieRolls that’s effectively a “child” table of BossFights:

• Every time BossFights gets recreated, BossFights_DieRolls also gets recreated.
• If BossFights gets recreated with a replacement, so does the child.
• If the parent replacement gets switched in, so does the child.
• If instead the parent replacement gets deleted, so does the child.
• Etc.

If there are multiple list-type fields in the parent table, there will be multiple list-type child tables, and they’ll all behave the same way.

We can join the parent to the child as follows:

{{#cargo_query:
tables=BossFights=BF, BossFights__DieRolls=BFDR
|join on=BF._ID=BFDR._rowID
}}

The join always is the same. The field _ID is the internal automated primary key of the parent table, and it’s present in the child table as a foreign key with the name _rowID, i.e. the ID of the parent row. So even though these names “_ID” and “_rowID” may seem a bit arbitrary and confusing (they also may seem natural to you, if so, yay!), please try to understand where they come from. This way you don’t need to come up with a confusing mnemonic device to remember them; it’s much easier to understand the logic behind the naming instead.

Caution! If you are aliasing your child table, your alias CANNOT BE THE SAME AS THE LIST-TYPE FIELD NAME, for example in this case you could not do BossFights__DieRolls=DieRolls! This would cause it to fail Cargo’s internal validation. (This is probably a bug in Cargo and should be patched, possibly by me; if it does get fixed, I will update this warning in the future.)

List tables have exactly three columns, and they are always the same:

• _rowID
• _value
• _position (starting with Cargo 2.1) (it’s 1-indexed)

…unless the type is Coordinates, in which case they have those three and additionally:

• _lat; and
• _lon.

In particular, list-children tables do NOT contain the automatic fields of _pageName, _pageNamespace, _pageID, or _pageTitle; if you need one of these, you must get it from its parent (or _pageData or some other table). On the other hand, joining the parent to the child and to nothing else does not create ambiguity in these four field names, the way it does for any other table.

(To be clear, I fully agree with the design decision of not including those 4 columns in list-children templates. It’s a performance improvement and the child tables don’t need them.)

Let’s try and make a query to find out all of the fights that had at least one 6 in their die rolls:

{{#cargo_query:
tables=BossFights=BF, BossFights__DieRolls=BFDR
|join on=BF._ID=BFDR._rowID
|fields=BF.DieRolls
|where=BFDR._value="6"
}}

You can see that the _value field is the one you want to query for.

If you understand everything up to this point, and you are okay to never, ever, ever use HOLDS on your wiki, you’re pretty much set. There’s still a lot to talk about in terms of good schema design and when you need to create additional full entity tables that are true one-to-many relations and not rely on the existence of the __FULL string field that Cargo gives you with the list-type field as a crutch, but you at least fully (haha) understand list-type fields, and you can probably stop reading now.

If, however, you still feel dependent on HOLDS then please keep reading.

How you are supposed to do it

The built-in method of working with list-type fields is to use a Cargo-specific operator, called HOLDS, and to take advantage of the __FULL field. If you are using Lua, case sensitivity is not checked with __FULL, and you may see some code incorrectly using __full if you look at Leaguepedia, but the field must be written in uppercase in wikitext.

Since that toy example with boss fights and die rolls isn’t a real table on my wiki, all it can really do is explain the structure of things, and hopefully it was useful for that. But to make concrete demonstrations, I will need to switch to something that actually exists.

The table is ScoreboardPlayers. Each row represents a Player’s stats (scoreboard entry) in a game of League of Legends. Every game, a player takes two summoner spells, so these spells are entered in a List (,) of String. The field is called SummonerSpells. So one player at the end of a game, and multiple (two) summoner spells.

This is the built-in method that Cargo “wants” you to use:

{{#cargo_query:tables=ScoreboardPlayers
|fields=SummonerSpells,Name
|where=SummonerSpells HOLDS "Smite"
|order by=DateTime_UTC DESC, Name DESC
|limit=5
}}

vs the “correct” method:

{{#cargo_query:tables=ScoreboardPlayers=SP,ScoreboardPlayers__SummonerSpells=SS
|join on=SP._ID=SS._rowID
|fields=SP.SummonerSpells,SP.Name
|where=SS._value="Smite"
|order by=SP.DateTime_UTC DESC, Name DESC
|limit=5
}}

In the following result, the two tables are identical:

This example demonstrates something about the fields value as well. Notice in these examples the SummonerSpells field is displaying the full value of the SummonerSpells list-type field. If all you want is to make it really easy to display a one-to-many relation, this method will suffice. No need to do a lot of complicated logic.

The way Cargo wants you to do it is…fine. There used to be some instances where, with more complicated queries, it would error, but when writing this article I was unable to reproduce any of them, so I think you’re okay to use HOLDS. That said, I wouldn’t recommend it. The explicit join is more versatile, and you should get used to it (see the next section for more information on this).

Joining through a list-type table

One final example/argument in favor of always using the explicit join to the child table is that you can join through the ._value. Consider one more Leaguepedia example, our ScoreboardGames table has a field called Team1Picks which is List (,) of String and contains a list of champions. We also have a table called Champions. Suppose we want to show the title of a champion (e.g. Lee Sin, the Blind Monk).

This query will show the top 5 most-picked on blue side champions in the LEC 2022 Spring Season, along with their champion titles.

{{#cargo_query:tables=ScoreboardGames=SG, ScoreboardGames__Team1Picks=Picks, Champions=C
|join on=SG._ID=Picks._rowID, Picks._value=C.Name
|fields=Picks._value=Champion, C.Title, COUNT(*)
|where=SG.OverviewPage="LEC/2022 Season/Spring Season"
|group by=Picks._value
|order by=COUNT(*) DESC
|limit=5
}}

Final note - using LIKE, RLIKE

You should not do this. The performance is abysmal and you will probably crash your wiki, perhaps taking down your entire server. That said, I will show you the code in case you have some use case for it that I’m not thinking of.

Method with LIKE (valid in both wikitext and Lua):

{{#cargo_query:tables=ScoreboardPlayers
|fields=SummonerSpells,Name
|where=SummonerSpells__FULL LIKE "%Smite%"
|order by=DateTime_UTC DESC, Name DESC
|limit=5
}}

Method with RLIKE (Lua only) (there’s a common pattern here, there’s a lot less validation for the Scribunto methods, and so some things are Lua-only):

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

function p.fakeHolds(field, str, sep)
	if str == nil then return false end
	sep = sep or ','
	str = h.escape(str)
	return ('%s__full RLIKE ".*(^|%s)%s($|%s).*"'):format(field, sep, str, sep)
end

function h.escape(str)
	local tbl = { '%(', '%)' }
	for _, v in ipairs(tbl) do
		str = str:gsub(v, '.')
	end
	return str
end

So in the case of SummonerSpells and , we’d get the following:

SummonerSpells__full RLIKE ".*(^|,)Smite($|,).*"

However, if we try to run this in wikitext we’ll get:

Error: unclosed string literal.

If I knew precisely what part of the validation were breaking this, I would patch it, but validation is a pretty hard problem to solve. So it remains a Lua-only “feature.”

Again, I cannot stress enough how much I anti-recommend using LIKE- or RLIKE-based solutions to querying list-type fields. Please use the explicit join.

Summary

• List-type fields are actually a field called FieldName__FULL (in the where clause you must use the __FULL suffix, unless it’s with HOLDS; in the fields list, you omit the __FULL and the extension adds it for you (unless you used HOLDS in your where clause, then it’s gonna give you the ._value of the child list table) (confused yet?)) as well as a table called TableName__FieldName.
• To join the tables, you join like: TableName._ID=TableName__FieldName._rowID
• To check the value of the list item in a where clause you do like: TableName__FieldName._value="whatever"
• You may also want to use the _value in the child table in a join clause
• It’s okay to use list-type fields when you have very simple use cases, and it’s okay to use HOLDS. But please try to avoid using LIKE or RLIKE.