I have a table of posts within a group. Every person in that group can interact with the post, star it, rate it, etc.

The structure is something like

Table User {
  id;
  groupId;
}

Table Post {
  postId,
  authorId FOREIGN KEY REFERENCES User(id),
  content,
}

Table UserPostInfo {
  postId FOREIGN KEY REFERENCES Post(postId),
  userId FOREIGN KEY REFERENCES User(id),
  is_starred,
  rating,...
}

Note that by default the UserPostInfo can be empty – i.e. if a user has not interacted with a post, the row does not exist.

What I would like to achieve is do something like:

1. For a specific user, show which posts it has starred
2. Collect the UserPostInfo relative to that specific user
3. Make ‘global’ query filtering, e.g. collect all posts which no one has starred.

So my postgREST query looks something like this (using supabase syntax, but that’s basically the same as a normal REST query):

  const currentUser = ...;
  let dbQuery = supabase
    .from('post')
    .select(
      `*, 
      author:user!inner(*),
      "userPostInfo"(*), 
      fupi:"userPostInfo"()`,
      { count: 'exact' },
    )
    .eq('author.groupId', currentUser.groupId)
    .eq('"userPostInfo"."userId"', currentUser.id);

So I will look at every post whose author is in the same group as the current user. Then I will get all the userPostInfo about the current post, but filtering out data relative to other users (only keeping the info relative to the current user).

Then my intention was to modify this base query to:

1. Only show starred posts by the current user:

    dbQuery = dbQuery
      .eq('"userPostInfo".starred', true)
      .not('userPostInfo', 'is', null);

(Note the addition of userPostInfo.not.is.null, since for the starred value to be set to true, we need the field to be present. If we don’t look for starred values specifically, the field might not be present but we should still show the post list).

2. Show posts which no user has starred:

dbQuery = dbQuery.eq('fupi.starred', true).is('fupi', null);

So my idea was: To collect posts which no user has starred, I cannot use userPostInfo, as that one is already being filtered out to only include the current user information. So instead I create an empty resource embed (fupi) which contains the values relative to all users. I look for any starred ones, and enforce fupi to be null – so that if any user has starred a post, the condition fupi is null will fail and the post won’t be returned.

The problem

I guess I might be misunderstanding how resource embeds are working, because this isn’t working at all.
Trying to return posts starred by the current user seems to work, but trying to return posts which no user has starred breaks in many ways:

1. The userPostInfo now includes values relative to other users as well, not only the current user – even if I have the ‘userPostInfo.userId.eq’ condition.
2. The values returned still contain posts which have been starred by other recruiters.

Can someone shed some light on these issues? What am I doing wrong, and is there some way of accomplishing what I want using postgREST?