admin 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 subjects I wish to examine at completely different instances. They’re irritating as a result of the instruments to handle them in Twitter are very restricted, so it’s extra trouble to arrange the type of setting I’d like. Mastodon additionally has lists, sadly its present administration instruments are equally unhealthy.
This appeared like a great problem for Steampipe. To deal with it, I first wanted so as to add some new tables to the plugin to encapsulate the record 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 record,
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
+--------------+--------------------------------------+
| record | folks |
+--------------+--------------------------------------+
| Tutorial | ____, ______, ____, ___ |
| Training | ___, ______ ___, ______ |
| Power | ___, ______, ____ __ |
| Fediverse | ____ __, |
| Humor | ____, ____ __, ____ __ |
| Journalism | ___ __, ___ ____, ___, ______ |
| Library | __ |
| Web | ___ __, _____, ___ __, __ __, ____ |
| Science | __, ____ __, ______ |
| Software program | ____ __, ______, ____ __ |
+--------------+--------------------------------------+
That’s a helpful view, and I’ve now included it, however it didn’t tackle 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 means I can simply see which record every account is on, and spot any accounts that aren’t on a listing.
For that I wanted so as to add a record 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,
notice
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 at all times get all of the folks I observe. For those who’re not on a listing, your record column will likely be null.
with knowledge as (
choose
l.title as record,
a.*
from
mastodon_list l
be a part of
mastodon_list_account a
on
l.id = a.list_id
),
mixed as (
choose
d.record,
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.notice
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.
IDGIt’s fairly sparse, I’ve solely simply begun including folks to lists. And truthfully I’m unsure I’ll wish to maintain doing this curation, it’s the type 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 so that they’re simple to search out.
To supply a greater technique to discover people who find themselves on lists, I expanded the Listing 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 record.
enter "record" depend
I additionally used this question to broaden the Listing tab.
choose
l.title as record,
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 record / folks desk on the correct.
IDGI do know that some gained’t cotton to this SQL-forward programming mannequin. However for others who will, I wished to indicate a number of detailed examples to present you a way of what’s attainable on the intersection of Mastodon and Steampipe.
For those who’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 is just not too scary. Sure there are joins, sure there’s an array_agg that transposes a column into a listing. It’s not newbie SQL. However plenty of folks know how one can use be a part of and array_agg in these methods, tons extra might simply find out how, and with SQL ascendant these days these are abilities price having.
