Lists and people on Mastodon


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 = 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 = 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 = ) select * from combined order by person

That query drives the new version of the Following tab.

mastodon following with lists 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 = <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 = a.list_id group by l.title

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

expanded list view 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

Copyright © 2023 IDG Communications, Inc.


Leave a Reply

Your email address will not be published. Required fields are marked *