Querying a many:many relationship on PK of the related table (ie. filtering by related table column)

Question

I have a many:many relationship between 2 tables: note and tag, and want to be able to search all notes by their tagId. Because of the many:many I have a junction table note_tag.

My goal is to expose a computed field on my Postgraphile-generated Graphql schema that I can query against, along with the other properties of the note table.

I'm playing around with postgraphile-plugin-connection-filter. This plugin makes it possible to filter by things like authorId (which would be 1:many), but I'm unable to figure out how to filter by a many:many. I have a computed column on my note table called tags, which is JSON. Is there a way to "look into" this json and pick out where id = 1?

Here is my computed column tags:

    create or replace function note_tags(note note, tagid text)
        returns jsonb as $$
            select
                json_strip_nulls(
                    json_agg(
                        json_build_object(
                            'title', tag.title, 
                            'id', tag.id,
                        ) 
                    )
                )::jsonb 
            from note
            inner join note_tag on note_tag.tag_id = tagid and note_tag.note_id = note.id
            left join note_tag nt on note.id = nt.note_id 
            left join tag on nt.tag_id = tag.id
            where note.account_id = '1'
            group by note.id, note.title;
        $$ language sql stable;

as I understand the function above, I am returning jsonb, based on the tagid that was given (to the function): inner join note_tag on note_tag.tag_id = tagid. So why is the json not being filtered by id when the column gets computed?

I am trying to make a query like this:

query notesByTagId {
  notes {
    edges {
      node {
        title
        id
        tags(tagid: "1")
      }
    }
  }
}

but right now when I execute this query, I get back stringified JSON in the tags field. However, all tags are included in the json, whether or not the note actually belongs to that tag or not.

For instance, this note with id = 1 should only have tags with id = 1 and id = 2. Right now it returns every tag in the database

{
  "data": {
    "notes": {
      "edges": [
        {
          "node": {
            "id": "1",
            "tags": "[{\"id\":\"1\",\"title\":\"Psychology\"},{\"id\":\"2\",\"title\":\"Logic\"},{\"id\":\"3\",\"title\":\"Charisma\"}]",
            ...

The key factor with this computed column is that the JSON must include all tags that the note belongs to, even though we are searching for notes on a single tagid

here are my simplified tables...

note:

create table notes(
  id text, 
  title text
)

tag:

create table tag(
  id text,
  title text
)

note_tag:

create table note_tag(
  note_id text FK references note.id
  tag_id text FK references tag.id
)

Update

I am changing up the approach a bit, and am toying with the following function:

    create or replace function note_tags(n note)
        returns setof tag as $$
            select tag.*
            from tag
            inner join note_tag on (note_tag.tag_id = tag.id)
            where note_tag.note_id = n.id;
        $$ language sql stable;

I am able to retrieve all notes with the tags field populated, but now I need to be able to filter out the notes that don't belong to a particular tag, while still retaining all of the tags that belong to a given note.

So the question remains the same as above: how do we filter a table based on a related table's PK?

Answer 1

After a while of digging, I think I've come across a good approach. Based on this response, I have made a function that returns all notes by a given tagid.

Here it is:

    create or replace function all_notes_with_tag_id(tagid text)
        returns setof note as $$
            select distinct note.*
            from tag
            inner join note_tag on (note_tag.tag_id = tag.id)
            inner join note on (note_tag.note_id = note.id)
            where tag.id = tagid;
        $$ language sql stable;

The error in approach was to expect the computed column to do all of the work, whereas its only job should be to get all of the data. This function all_nuggets_with_bucket_id can now be called directly in graphql like so:

query MyQuery($tagid: String!) {
  allNotesWithTagId(tagid: $tagid) {
    edges {
      node {
        id
        title
        tags {
          edges {
            node {
              id
              title
            }
          }
        }
      }
    }
  }
}