Lists and other people on Mastodon | Area Tech

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

I like lists as a result of they permit me to divide my timeline into subjects that I wish to examine at completely 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 sort of surroundings I would like. Mastodon has lists too, sadly their present admin instruments are simply as unhealthy.

This appeared like a very good problem for Steampipe. To handle this, 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 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 checklist,
  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

+--------------+--------------------------------------+
| checklist         | individuals                               |               
+--------------+--------------------------------------+
| Tutorial     | ____, ______, ____, ___              |
| Training    | ___, ______  ___, ______             |
| Power       | ___, ______, ____ __                 |
| Fediverse    | ____ __,                             |
| Humor        | ____, ____ __, ____ __               |
| Journalism   | ___ __, ___ ____,  ___, ______       |
| Library      | __                                   |
| Internet          | ___ __, _____, ___ __, __ __, ____   |
| Science      | __, ____ __, ______                  |
| Software program     | ____ __, ______, ____ __             |
+--------------+--------------------------------------+

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

To handle these lists, I actually need a display that reveals every account I comply with in a desk with their lists. That approach I can simply see which checklist 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 particular person,
  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
  particular person

The brand new model captures the outdated mixture of mastodon_list Y mastodon_list_accountand joins it to mastodon_following (individuals I comply with) desk. It is a left be part of, which suggests I am going to all the time get everybody I comply with. In case you are not on a listing, your prepared the column shall be null.

with information as (
  choose
    l.title as checklist,
    a.*
  from
    mastodon_list l
  be 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 particular person,
    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
  particular person

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 preserve doing this curating, it is the sort of factor that may grow to be a burden, I must 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 seek out.

To offer a greater solution 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 "checklist" 

I additionally used this question to broaden the Prepared eyelash.

choose
  l.title as checklist,
  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 fitting.

IDG

I do know that some is not going to settle for this SQL-forward programming mannequin. However for others who will, I needed to indicate some detailed examples to present you an thought of ​​what’s attainable on the intersection of Mastodon and Steampipe.

If you happen to’re not tuned into SQL (as I wasn’t for a very long time), this 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 is not SQL for rookies. However many individuals know tips on how to use be part of Y array_agg On this approach, many extra may simply learn to do it, and with SQL on the rise in the present day, these are expertise value having.

See additionally:

1. Hope for the trustworthy
2. Create a Mastodon panel with Steampipe
3. Navigating the fediverse
4. A Bloomberg terminal for Mastodon
5. Create your individual Mastodon UX
6. Lists and other people on Mastodon