Need to convert sql-query into go-pg orm

Question

Have a query that looks like this

   q = 'select distinct t.uuid, t.tags
    from tags as t
    inner join tag_item ti on t.uuid = ti.item_id
    inner join tag_words tw on ti.tag_id = tw.id
    where tw.tag in (?) and ti.item_id in (?)'

It works fine if I use err := s.db.QueryContext(ctx, &resp, q, p1, p2)

But I don't always have p1 and p2 so I want to be able to separate it nicely. I tried something like this:

    var resp []*models.TagRecord

    q := s.db.ModelContext(ctx, &resp).ColumnExpr("distinct tags.uuid, tags.tags")
    q = q.Join("inner join tag_item as ti").JoinOn("ti.item_id=tags.uuid")
    if len(filters.UUIDs) != 0 {
        q = q.JoinOn("ti.item_id IN (?)", pg.In(filters.UUIDs))
    }

    q = q.Join("inner join tag_words as tw").JoinOn("tw.id=ti.tag_id")
    if len(filters.Tags) != 0 {
        q = q.JoinOn("tw.tag IN (?)", pg.In(filters.Tags))
    }

    err := q.Select()

But I'm getting this error:

ERROR #42P01 invalid reference to FROM-clause entry for table "tags"" )

What am I doing wrong and how can I make this work?

Answer 1

Had a bright idea to log what query looks like in plain sql, tx to answers to this thread convert go-pg query into plain sql did it and figured out how to make above code work, it turned out to be quite simple:

var resp []*models.TagRecord

q := s.db.ModelContext(ctx, &resp).ColumnExpr("distinct tag_record.uuid, tag_record.tags")
q = q.Join("inner join tag_item as ti").JoinOn("tag_record.uuid=ti.item_id")
if len(filters.UUIDs) != 0 {
    q = q.JoinOn("ti.item_id IN (?)", pg.In(filters.UUIDs))
}

q = q.Join("inner join tag_words as tw").JoinOn("ti.tag_id=tw.id")
if len(filters.Tags) != 0 {
    q = q.JoinOn("tw.tag IN (?)", pg.In(filters.Tags))
}

err := q.Select()

table tags is aliased as tag_record, so changed calls to that alias and all good to go )