Lists and folks on Mastodon | Tech Adil

I hadn’t considered utilizing Mastodon lists till I learn the frustration with lists chapter of Martin Fowler’s Exploring Mastodon, during which he writes:

I like lists as a result of they permit me to divide my timeline into matters that I wish to examine at totally different occasions. They’re irritating as a result of the instruments to handle them on Twitter are very restricted, making it harder to arrange the type of surroundings I might like. Mastodon has lists too, sadly their present admin instruments are simply as unhealthy.

This appeared like problem for Steampipe. To deal with this, 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 am going to save that story for an additional time. Right here I am going to simply present that collectively they permit queries like this.

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

+--------------+--------------------------------------+
| record         | individuals                               |               
+--------------+--------------------------------------+
| Educational     | ____, ______, ____, ___              |
| Training    | ___, ______  ___, ______             |
| Vitality       | ___, ______, ____ __                 |
| Fediverse    | ____ __,                             |
| Humor        | ____, ____ __, ____ __               |
| Journalism   | ___ __, ___ ____,  ___, ______       |
| Library      | __                                   |
| Web          | ___ __, _____, ___ __, __ __, ____   |
| Science      | __, ____ __, ______                  |
| Software program     | ____ __, ______, ____ __             |
+--------------+--------------------------------------+

That is a helpful view, and I’ve included it now, but it surely did not deal with Martin’s particular want.

To handle these lists, I actually need a display screen that reveals every account I observe in a desk with their lists. That approach I can simply see which record every account is on and spot accounts that are not on a listing.

For that I wanted so as to add a prepared column to the Following eyelash.

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 previous mixture of mastodon_list Y mastodon_list_accountand joins it to mastodon_following (individuals I observe) desk. It is a left be part of, which implies I am going to all the time get everybody I observe. If you’re not on a listing, your prepared the column might be null.

with information as (
  choose
    l.title as record,
    a.*
  from
    mastodon_list l
  be 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 part of
    information d
  on
    f.id = d.id
)
choose
  *
from
  mixed
order by
  individual

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

IDG

It is fairly sparse, I simply began including individuals to lists. And actually, I am undecided I wish to hold doing this curating, it is the type of factor that may grow to be a burden, I have to mess around a bit extra earlier than committing. In the meantime, the default order places unlisted individuals first in order that they’re straightforward to search out.

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

enter "record" 

I additionally used this question to develop the Prepared eyelash.

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

The result’s the prepared / individuals desk on the proper.

IDG

I do know some won’t settle for this SQL-forward programming mannequin. However for others who will, I wished to indicate some detailed examples to offer you an concept of ​​what’s attainable on the intersection of Mastodon and Steampipe.

For those who’re not tuned into SQL (as I wasn’t for a very long time), here is your takeaway: As SQL goes, this is not too scary. Sure, there are unions, sure, there’s a array_agg which transposes a column to a listing. It isn’t SQL for novices. However many individuals know tips on how to use be part of Y array_agg On this approach, many extra may simply discover ways to do it, and with SQL on the rise immediately, these are expertise value having.

See additionally:

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