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.
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.
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:
Copyright © 2023 IDG Communications, Inc.