How to specify select query as one of the select fields?

[surajbarkale]

With following two tables:

users (id, name)
likes (id, count, user_id)

Following SQL query can be created:

select *, (select count from likes where user_id = users.id) as like_count from users;

The reason for putting subquery in the field is that it can be selectively added to func(*sq.Row) function passed on to Selectx. In my code I have a very complex subquery like this. I am using the following go code:

type User struct {
  ID uuid.UUID
  Name string
  Likes *int
}

func (u *User) ScanRow(t tables.USERS, fetchLikes bool) func(*sq.Row) {
  return func(row *sq.Row) {
    u.ID = row.UUID(t.ID)
    u.Name = row.String(t.NAME)
    if fetchLikes {
      row.ScanInto(&u.Likes, sq.Fieldf("(select count from likes where user_id = ?)", t.ID).As("like_count"))
    }
  }
}

Can you please help me in removing the hard-coded SQL?

[bokwoon95]

Hmm this resembles #7, does something like that work?

func (u *User) ScanRow(t tables.USERS, l tables.LIKES, fetchLikes bool) func(*sq.Row) {
  return func(row *sq.Row) {
    u.ID = row.UUID(t.ID)
    u.Name = row.String(t.NAME)
    if fetchLikes {
      row.ScanInto(&u.Likes, sq.Fieldf("(?)", sq.
        Select(l.COUNT).
        From(l).
        Where(l.USER_ID.Eq(t.ID)).
        NestThis(), // <-- this is kind of undocumented
      ).As("like_count"))
    }
  }
}

The NestThis() call is required to mark the SelectQuery as nested so that it doesn't prematurely rebind all the ?, ?, ? to $1, $2, $3, and instead lets the outermost SelectQuery do the rebinding. Let me know if it doesn't work.

sidenote: I don't really like the inclusion of NestThis() in the Query interface, I'm planning to remove it in the v2 of this package. SelectQueries inside a SelectQuery should then work normally without needing the extra NestThis() call.

[surajbarkale]

Thanks NestThis() worked as expected to embed the SQL query. While updating my code I realized that table aliasing modifies the source table:

t1 := tables.USERS().As("t1")
t2 := t1.As("t2")
// The following is unexpected result
t1.GetAlias() == "t2"

Not sure if this ia a bug.

[bokwoon95]

Oh, that's functioning sort of as intended (although now I see the function signature is quite misleading). The idea was that .As() would modify the underlying table's alias, but it also returns the same table so that it can be plugged into wherever a table is used (e.g. .From(table.As("alias"))). If another table is needed I guess it would require calling the table constructor again.