I hadn’t believed to use Mastodon lists right up until I study the Irritation with lists chapter of Martin Fowler’s Exploring Mastodon, in which he writes:

I like lists due to the fact they make it possible for me to divide up my timeline to subject areas I want to examine about at different instances. They are discouraging since the tools to take care of them in Twitter are extremely confined, so it is much more stress to established up the sort of atmosphere I’d like. Mastodon also has lists, regrettably its recent administration tools are similarly undesirable.

This seemed like a excellent problem for Steampipe. To tackle it, I first required to increase some new tables to the plugin to encapsulate the record APIs: mastodon_listing and mastodon_listing_account. I’ll help you save that story for an additional time. Here I’ll just present that collectively they allow queries like this.

decide on
  l.title as listing,
  array_agg(a.username buy by a.username) as people
from
  mastodon_checklist l
join
  mastodon_list_account a
on
  l.id = a.list_id
group by
  l.title
+--------------+--------------------------------------+
| listing         | people today                               |               
+--------------+--------------------------------------+
| Academic     | ____, ______, ____, ___              |
| Education    | ___, ______  ___, ______             |
| Strength       | ___, ______, ____ __                 |
| Fediverse    | ____ __,                             |
| Humor        | ____, ____ __, ____ __               |
| Journalism   | ___ __, ___ ____,  ___, ______       |
| Library      | __                                   |
| Net          | ___ __, _____, ___ __, __ __, ____   |
| Science      | __, ____ __, ______                  |
| Software     | ____ __, ______, ____ __             |
+--------------+--------------------------------------+

That is a valuable see, and I have now bundled it, but it did not handle Martin’s precise need.

To control these lists, I seriously need a screen that reveals each individual account that I stick to in a desk with its lists. That way I can simply see which checklist just about every account is on, and location any accounts that aren’t on a list.

For that I needed to include a record column to the Adhering to tab.

This was the authentic question.

pick
  url,
  situation when exhibit_name="" then username else screen_title conclude as man or woman,
  to_char(established_at, 'YYYY-MM-DD') as given that,
  followers_rely as followers,
  adhering to_rely as pursuing,
  statuses_depend as toots,
  note
from
  mastodon_subsequent
buy by
  person

The new model captures the over join of mastodon_record and mastodon_list_account, and joins that to the mastodon_next (people I abide by) desk. It is a remaining join, which indicates I’ll usually get all the folks I comply with. If you’re not on a checklist, your listing column will be null.

with data as (
  pick
    l.title as list,
    a.*
  from
    mastodon_listing l
  join
    mastodon_record_account a
  on
    l.id = a.checklist_id
),
merged as (
  pick out
    d.record,
    f.url,
    circumstance when f.display screen_name="" then f.username else f.exhibit_identify stop as man or woman,
    to_char(f.designed_at, 'YYYY-MM-DD') as considering that,
    f.followers_rely as followers,
    f.pursuing_count as next,
    f.statuses_rely as toots,
    f.observe
  from
    mastodon_adhering to f
  still left join
    knowledge d
  on
    f.id = d.id
)
choose
  *
from
  merged
order by
  person

That question drives the new edition of the Adhering to tab.

mastodon following with lists IDG

It is really sparse, I’ve only just begun adding persons to lists. And honestly I’m not confident I’ll want to maintain executing this curation, it’s the form of point that can turn into a stress, I need to have to perform around some far more right before I dedicate. Meanwhile, the default sort puts unlisted people today very first so they’re easy to discover.

To offer a much better way to find people who are on lists, I expanded the List tab in a few of strategies. It had incorporated a dropdown of lists by which to filter the property timeline. Now that dropdown has counts of persons on each individual record.

enter "checklist" 

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
  l.id = 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