Exploring queries for private feeds

One of the discussions this weekend in Berlin was on the topic of private feeds. Martijn and Sven made great progress by implemeting a flow to fetch private pages using various endpoints for tokens and authentication.

Apart from the question how to fetch private feeds, there is also the question how to present private feeds. The easiest way is probably to give every user their own feed, containing only the private posts for them. They can separately follow your public feed, and your queries are easier.

But in line with Silo’s like Twitter and Facebook, I think I would prefer presenting one feed, with both public and private posts, scoped for the authenticated user. When I described this to Aaron he said that he liked it, but that he didn’t know where to begin with writing code that does that. I didn’t either, but it made me want to explore the possibilities.

On a sidenote: this feed design also raises another problem, of how to signal to the user that they can see this post but no-one else. I leave that one for another time.

Drawing rough lines around boxes

Borrowing from Facebook, there are roughly four categories you can share content in:

  1. public – These posts can be seen by anyone. This is the default on nearly all IndieWeb sites today.
  2. authenticated – These posts can only been seen if you sign in, but, anyone can sign in. Facebook has this category and we can mimic that with IndieAuth, but it might not add that much value.
  3. friends only – This is a big category on Facebook, and made possible by the friendslist, which is also a big feature on Facebook.
  4. selected audience – Facebook also allows you to pick your audience on a per-post basis. This can be done by either selecting individual users, or selecting lists, which can contain users.

There is also the possibility of excluding specific people or lists from posts, but that one is even more advanced, so I put it out of scope for this exploration.

The first category is easy, for we already have it. The second category is harder, but once you got past the authentication it’s easy again. One could query a database for visibility = 'authenticated' OR visibility = 'public', that would work.

The third category would require us to keep a list of friends. The fourth category could also require us to keep lists of people, so it might be better to merge them.

Throw in some tables

This brings us to a simple database schema. I see three main tables: entries, people and groups, with a pivot table between all of them: entry_group, entry_person and group_person. I have chosen ‘people’ over ‘users’, because I might not want to give these people write access to anything, but they could be users as well.

It should work like this:

  • Entries have a field for visibility, which can me marked public, authenticated or private.
  • People can belong to groups, which have names. Think ‘Friends’, ‘Family’ and ‘Coworkers’.
  • Entries can be opened up to individual people, or for a whole group.

There might be better ways of naming these, but I like the simplicity of this model. With private posts and audiences, I will always have to manage some form of lists, and this is the most simple way of doing it.

Enter the monster query

So, with some trial and error, PHPUnit tests, and a lot of Laravel magic I came to the following monster query for these tables:

  select `entries`.* 
  from `groups` 
  inner join `group_person` 
    on `groups`.`id` = `group_person`.`group_id` 
  inner join `entry_group` 
    on `groups`.`id` = `entry_group`.`group_id` 
  inner join `entries` 
    on `entries`.`id` = `entry_group`.`entry_id` 
  where `group_person`.`person_id` = ?
  select `entries`.* 
  from `entries` 
  inner join `entry_person` 
    on `entries`.`id` = `entry_person`.`entry_id` 
  where `entry_person`.`person_id` = ?
  select * 
  from `entries` 
  where `visibility` = 'public'
order by `published_at` desc

... which is way shorter when expressed in with Laravel’s Eloquent:

class Person extends Model
    public function timeline()
        return $this->groups()
            ->join('entry_group', 'groups.id', '=', 'entry_group.group_id')
            ->join('entries', 'entries.id', '=', 'entry_group.entry_id')
            ->orderBy('published_at', 'desc');

    public function groups()
        return $this->belongsToMany(Group::class);

    public function entries()
        return $this->belongsToMany(Entry::class);

Since the method timeline() returns the Query object, other where-clauses can be appended when needed.

I am in a bit of a fight with Laravel still, for it adds
'`group_person`.`person_id` as `pivot_person_id`, `group_person`.`group_id` as `pivot_group_id`' to the first query, which makes it blow up, but the raw query works!

There is possibly a better way of doing it, but this is a start! Feel free to steal or improve, but if you improve, let me know.

3 personen vinden dit leuk



This is a great write up, thanks! This is definitely the road I’ve been thinking down. So far I have been using visibility public, unlisted, and private. The goal has been eventually to set an a…