Lists and people on Mastodon

I hadn’t thought of using 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 my timeline into topics that I want to read about at different times. They’re frustrating because the tools to manage them on Twitter are very limited, making it more difficult to set up the kind of environment I’d like. Mastodon has lists too, sadly their current admin tools are just as bad.

This seemed like a good challenge for Steampipe. To address this, 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 allow 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 included it now, but it didn’t address Martin’s specific need.

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

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

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 old combination of mastodon_list and mastodon_list_accountand joins it to mastodon_following (people I follow) table. It’s a left join, which means I’ll always get everyone I follow. If you are not on a list, your list the 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 eyelash.

Also Read: Can AI solve IT’s eternal data problem?

It’s pretty sparse, I just started adding people to lists. And honestly, I’m not sure I want to keep doing this curating, it’s the kind of thing that can become a burden, I need to play around a bit more before committing. Meanwhile, the default order puts unlisted people first so they’re easy to find.

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

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 eyelash.

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.

I know that some will not accept this SQL-forward programming model. But for others who will, I wanted to show some detailed examples to give you an idea of what’s possible at the intersection of Mastodon and Steampipe.

If you’re not tuned into SQL (as I wasn’t for a long time), here’s your takeaway: As SQL goes, this isn’t too scary. Yes, there are unions, yes, there is a array_agg which transposes a column to a list. It’s not SQL for beginners. But many people know how to use join and array_agg In this way, many more could easily learn how to do it, and with SQL on the rise today, these are skills worth having.