I hadn’t thought to use Mastodon lists until I read the Frustration with lists chapter of Martin Fowler’s Exploring Mastodon, in which he writes:

I like lists because they allow me to divide up my timeline to topics I want to read about at different times. They are frustrating because the tools to manage them in Twitter are very limited, so it’s more hassle to set up the kind of environment I’d like. Mastodon also has lists, sadly its current management tools are equally bad.

This seemed like a good challenge for Steampipe. To tackle it, I first needed to add some new tables to the plugin to encapsulate the list APIs: mastodon_list and mastodon_list_account. I’ll save that story for another time. Here I’ll just show that together they enable queries like this.

select   l.title as list,   array_agg(a.username order by a.username) as people from   mastodon_list l join   mastodon_list_account a on   l.id = a.list_id group by   l.title 

+--------------+--------------------------------------+ | list         | people                               |                +--------------+--------------------------------------+ | Academic     | ____, ______, ____, ___              | | Education    | ___, ______  ___, ______             | | Energy       | ___, ______, ____ __                 | | Fediverse    | ____ __,                             | | Humor        | ____, ____ __, ____ __               | | Journalism   | ___ __, ___ ____,  ___, ______       | | Library      | __                                   | | Net          | ___ __, _____, ___ __, __ __, ____   | | Science      | __, ____ __, ______                  | | Software     | ____ __, ______, ____ __             | +--------------+--------------------------------------+ 

That’s a useful view, and I’ve now included it, but it didn’t address Martin’s specific need.

To manage these lists, I really need a display that shows every account that I follow in a table with its lists. That way I can easily see which list each account is on, and spot any accounts that aren’t on a list.

For that I needed to add a list column to the Following tab.

This was the original query.

select   url,   case when display_name = '' then username else display_name end as person,   to_char(created_at, 'YYYY-MM-DD') as since,   followers_count as followers,   following_count as following,   statuses_count as toots,   note from   mastodon_following order by   person 

The new version captures the above join of mastodon_list and mastodon_list_account, and joins that to the mastodon_following (people I follow) table. It’s a left join, which means I’ll always get all the people I follow. If you’re not on a list, your list column will be null.

with data as (   select     l.title as list,     a.*   from     mastodon_list l   join     mastodon_list_account a   on     l.id = a.list_id ), combined as (   select     d.list,     f.url,     case when f.display_name = '' then f.username else f.display_name end as person,     to_char(f.created_at, 'YYYY-MM-DD') as since,     f.followers_count as followers,     f.following_count as following,     f.statuses_count as toots,     f.note   from     mastodon_following f   left join     data d   on     f.id = d.id ) select   * from   combined order by   person 

That query drives the new version of the Following tab.

IDG

It’s pretty sparse, I’ve only just begun adding people to lists. And honestly I’m not sure I’ll want to keep doing this curation, it’s the kind of thing that can become a burden, I need to play around some more before I commit. Meanwhile, the default sort puts unlisted people first so they’re easy to find.

To provide a better way to find people who are on lists, I expanded the List tab in a couple of ways. It had included a dropdown of lists by which to filter the home timeline. Now that dropdown has counts of people on each list.

input "list" {   type = "select"   width = 2   sql = <<EOQ     with list_account as (       select         l.title       from         mastodon_list l       join           mastodon_list_account a       on         l.id = a.list_id     ),     counted as (       select         title,         count(*)       from         list_account       group by         title       order by         title     )     select       title || ' (' || count || ')' as label,       title as value     from       counted     order by       title   EOQ } 

I also used this query to expand the List tab.

select   l.title as list,   array_to_string( array_agg( lower(a.username) order by lower(a.username)), ', ') as people from   mastodon_list l join   mastodon_list_account a on   l.id = a.list_id group by   l.title 

The result is the list / people table on the right.

IDG

I know that some won’t cotton to this SQL-forward programming model. But for others who will, I wanted to show a few detailed examples to give you a sense of what’s possible at the intersection of Mastodon and Steampipe.

If you’re not tuned into SQL (like I wasn’t for a very long time), here’s your takeaway: As SQL goes, this stuff is not too scary. Yes there are joins, yes there’s an array_agg that transposes a column into a list. It’s not beginner SQL. But lots of people know how to use join and array_agg in these ways, lots more could easily learn how, and with SQL ascendant nowadays these are skills worth having.

See also:

1. Hope for the fediverse
2. Build a Mastodon dashboard with Steampipe
3. Browsing the fediverse
4. A Bloomberg terminal for Mastodon
5. Create your own Mastodon UX
6. Lists and people on Mastodon