Lists and folks on Mastodon

I hadn’t thought to make use of Mastodon lists till I learn the Frustration with lists chapter of Martin Fowler’s Exploring Mastodon, wherein he writes:

I like lists as a result of they permit me to divide up my timeline to matters I wish to examine at totally different occasions. They’re irritating as a result of the instruments to handle them in Twitter are very restricted, so it’s extra problem to arrange the form of surroundings I’d like. Mastodon additionally has lists, sadly its present administration instruments are equally dangerous.

This appeared like an excellent problem for Steampipe. To deal with it, I first wanted so as to add some new tables to the plugin to encapsulate the checklist APIs: mastodon_list and mastodon_list_account. I’ll save that story for an additional time. Right here I’ll simply present that collectively they permit queries like this.

choose
  l.title as checklist,
  array_agg(a.username order by a.username) as folks
from
  mastodon_list l
be a part of
  mastodon_list_account a
on
  l.id = a.list_id
group by
  l.title

+--------------+--------------------------------------+
| checklist         | folks                               |               
+--------------+--------------------------------------+
| Educational     | ____, ______, ____, ___              |
| Schooling    | ___, ______  ___, ______             |
| Power       | ___, ______, ____ __                 |
| Fediverse    | ____ __,                             |
| Humor        | ____, ____ __, ____ __               |
| Journalism   | ___ __, ___ ____,  ___, ______       |
| Library      | __                                   |
| Web          | ___ __, _____, ___ __, __ __, ____   |
| Science      | __, ____ __, ______                  |
| Software program     | ____ __, ______, ____ __             |
+--------------+--------------------------------------+

That’s a helpful view, and I’ve now included it, nevertheless it didn’t handle Martin’s particular want.

To handle these lists, I really want a show that reveals each account that I observe in a desk with its lists. That approach I can simply see which checklist every account is on, and spot any accounts that aren’t on an inventory.

For that I wanted so as to add a checklist column to the Following tab.

This was the unique question.

choose
  url,
  case when display_name="" then username else display_name finish as individual,
  to_char(created_at, 'YYYY-MM-DD') as since,
  followers_count as followers,
  following_count as following,
  statuses_count as toots,
  observe
from
  mastodon_following
order by
  individual

The brand new model captures the above be a part of of mastodon_list and mastodon_list_account, and joins that to the mastodon_following (folks I observe) desk. It’s a left be a part of, which implies I’ll all the time get all of the folks I observe. In case you’re not on an inventory, your checklist column will probably be null.

with knowledge as (
  choose
    l.title as checklist,
    a.*
  from
    mastodon_list l
  be a part of
    mastodon_list_account a
  on
    l.id = a.list_id
),
mixed as (
  choose
    d.checklist,
    f.url,
    case when f.display_name="" then f.username else f.display_name finish as individual,
    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.observe
  from
    mastodon_following f
  left be a part of
    knowledge d
  on
    f.id = d.id
)
choose
  *
from
  mixed
order by
  individual

That question drives the brand new model of the Following tab.

IDG

It’s fairly sparse, I’ve solely simply begun including folks to lists. And truthfully I’m undecided I’ll wish to hold doing this curation, it’s the form of factor that may turn out to be a burden, I must mess around some extra earlier than I commit. In the meantime, the default kind places unlisted folks first in order that they’re straightforward to search out.

To supply a greater method to discover people who find themselves on lists, I expanded the Record tab in a few methods. It had included a dropdown of lists by which to filter the house timeline. Now that dropdown has counts of individuals on every checklist.

enter "checklist"  rely 

I additionally used this question to develop the Record tab.

choose
  l.title as checklist,
  array_to_string( array_agg( decrease(a.username) order by decrease(a.username)), ', ') as folks
from
  mastodon_list l
be a part of
  mastodon_list_account a
on
  l.id = a.list_id
group by
  l.title

The result’s the checklist / folks desk on the appropriate.

IDG

I do know that some received’t cotton to this SQL-forward programming mannequin. However for others who will, I wished to point out a number of detailed examples to present you a way of what’s attainable on the intersection of Mastodon and Steampipe.

In case you’re not tuned into SQL (like I wasn’t for a really very long time), right here’s your takeaway: As SQL goes, these things just isn’t too scary. Sure there are joins, sure there’s an array_agg that transposes a column into an inventory. It’s not newbie SQL. However a number of folks know find out how to use be a part of and array_agg in these methods, tons extra may simply learn the way, and with SQL ascendant these days these are expertise price having.

See additionally:

1. Hope for the fediverse
2. Construct a Mastodon dashboard with Steampipe
3. Looking the fediverse
4. A Bloomberg terminal for Mastodon
5. Create your personal Mastodon UX
6. Lists and folks on Mastodon