Filters for sub models don't generate the right subqueries

[RichardLindhout]

When I filter on

query{
  flowBlocks(filter:{
    where: {
      block:{
        title:{
          equalTo: "Wat wil je reinigen?"
        }
      }
    }
  }){
    id
    flow{
      title
    }
    block{
      id
      title
      organization{
        id
      }
    }
  }
}

It results in the following query:

SELECT * 
FROM   `flow_block` 
WHERE  `flow_block`.`organization_id` = 1 
       AND ( EXISTS(SELECT 1 
                    FROM   `block` 
                    WHERE  ( title = 'Wat wil je reinigen?' )) ); 

This results in all almost all flowblocks returning while it should only return flowblocks which contain a block with this title so the query should be:

SELECT * 
FROM   `flow_block` 
WHERE  `flow_block`.`organization_id` = 1 
       AND ( EXISTS(SELECT 1 
                    FROM   `block` 
                    WHERE  ( title = 'Wat wil je reinigen?' ) 
                           AND `flow_block`.`block_id` = `block`.`id`) ); 

[troian]

@RichardLindhout any luck to fix this?

here is an example of the issue in my case

SELECT * FROM "appointment" WHERE (((starts_at >= $1 AND starts_at < $2) OR (ends_at >= $3 AND ends_at < $4)) AND EXISTS(SELECT 1 FROM "appointment_participant" WHERE (account_id = $1)));
[2020-07-08 23:00:00 +0000 UTC 2020-07-08 23:20:00 +0000 UTC 2020-07-08 23:00:00 +0000 UTC 2020-07-08 23:20:00 +0000 UTC df95dc13-8673-40c0-ada4-203cf72e20ff]
{"level":"error","error":"models: failed to assign all query results to Appointment slice: bind failed to execute query: pq: operator does not exist: uuid = timestamp without time zone","time":"2020-07-10T03:50:27Z","message":"could not list appointments"}

account_id should pick argument 5, not 1

[troian]

I think issue is described here. cannot think about options to fix then

[RichardLindhout]

Hey I'll try to invest some time in this soon, maybe today!

[RichardLindhout]

What is the code what cause this it looks like this is another issue

[RichardLindhout]

I ment what is the graphql filter you sent?

[troian]

{
    appointments(filter: {
        where : {
            startsAt: "2020-07-08T23:00:00Z",
            endsAt: "2020-07-08T23:20:00Z",
            appointmentParticipants : {
                account : {
                    id : {
                        equalTo: "df95dc13-8673-40c0-ada4-203cf72e20ff"
                    }
                }
            }
        }
    }) {
        id
        startsAt
        endsAt
        status
    }
}

    subQueryMods := AppointmentParticipantWhereToMods(m, !hasForeignKeyInRoot)
    if len(subQueryMods) > 0 {
        subQuery := models.AppointmentParticipants(append(subQueryMods, qm.Select("1"))...)
        queryMods = appendSubQuery(queryMods, subQuery.Query) // <-- issue happens here
    }

[RichardLindhout]

I can't reproduce the error you are having, but I think I know a way to fix this issue but I would like to make this library more polished and testable first. I don't have time in the next few weeks to work around this. Will surely fix in the future + new features but I have a few private projects I need to finish and work on before I can work on open source again.

[troian]

If you could drop me a few lines about fix idea you have I could try to make it work

[troian]

that quite dirty hack, but works

var (
    replaceQuestion = regexp.MustCompile(`(\$\d{1,2})`)
)

func appendSubQuery(queryMods []qm.QueryMod, q *queries.Query) []qm.QueryMod {
    qs, args := queries.BuildQuery(q)
    qsClean := strings.TrimSuffix(qs, ";")

    res := replaceQuestion.ReplaceAllStringFunc(qsClean, func(m string) string {
        parts := replaceQuestion.FindStringSubmatch(m)

        if len(parts) > 1 {
            return "?"
        }
        return ""
    })

    return append(queryMods, qm.Where(fmt.Sprintf("EXISTS(%v)", res), args...))
}

[troian]

i just think it probably worth to ask sqlboler authors to provide BuildQuery with option to not substitute params, not sure though it is a good idea. or use UseIndexPlaceholders set to false

[troian]

like this 🤣

func appendSubQuery(queryMods []qm.QueryMod, q *queries.Query) []qm.QueryMod {
    // all nasty job below is to tell query builder not to replace ? placeholders
    member := reflect.ValueOf(q).Elem().FieldByName("dialect")
    dialectPtr := (**drivers.Dialect)(unsafe.Pointer(member.UnsafeAddr()))
    dialect := **dialectPtr
    dialect.UseIndexPlaceholders = false
    *dialectPtr = &dialect

    qs, args := queries.BuildQuery(q)
    qsClean := strings.TrimSuffix(qs, ";")

    return append(queryMods, qm.Where(fmt.Sprintf("EXISTS(%v)", qsClean), args...))
}

[RichardLindhout]

I think this issue is another problem too, even if the arguments worked there would still be issues where there are too much results. This looks more like a sqlboiler issue/enhancement.

Anyway I'm fixing the issue title and will try to look if I have the same issue with more filters

[RichardLindhout]

Fixed the issue described (not @troian 's case yet).

However it only works 2/3 level nested at the moment since we need to pass all other queryMods to the sub queries too so deeper filters still need a better bugfix for this so we can go endless deeply with filters

SELECT
   * 
FROM
   `block` 
WHERE
   `block`.`organization_id` = 1
   AND 
   (
      EXISTS
      (
         SELECT
            1 
         FROM
            `flow_block` 
         WHERE
            (
               EXISTS
               (
                  SELECT
                     1 
                  FROM
                     `block` 
                  WHERE
                     (
                        EXISTS
                        (
                           SELECT
                              1 
                           FROM
                              `block_choice` 
                           WHERE
                              (
                                 EXISTS
                                 (
                                    SELECT
                                       1 
                                    FROM
                                       `block` 
                                    WHERE
                                       (
                                          EXISTS
                                          (
                                             SELECT
                                                1 
                                             FROM
                                                `block_choice` 
                                             WHERE
                                                (
                                                   id = 11
                                                )
                                                AND 
                                                (
                                                   block_choice.block_id = block.id
                                                )
                                          )
                                       )
                                 )
                              )
                              AND 
                              (
                                 block_choice.block_id = block.id
                              )

                        )
                     )
               )
            )
            AND 
            (
               flow_block.block_id = block.id
            )
      )
   )
;

Need to become

SELECT
   * 
FROM
   `block` 
WHERE
   `block`.`organization_id` = 1 
   AND 
   (
      EXISTS 
      (
         SELECT
            1 
         FROM
            `flow_block` 
         WHERE
            (
               EXISTS 
               (
                  SELECT
                     1 
                  FROM
                     `block` 
                  WHERE
                     (
                        EXISTS 
                        (
                           SELECT
                              1 
                           FROM
                              `block_choice` 
                           WHERE
                              (
                                 EXISTS 
                                 (
                                    SELECT
                                       1 
                                    FROM
                                       `block` 
                                    WHERE
                                       (
                                          EXISTS 
                                          (
                                             SELECT
                                                1 
                                             FROM
                                                `block_choice` 
                                             WHERE
                                                (
                                                   id = 11 
                                                )
                                                AND 
                                                (
                                                   block_choice.block_id = block.id 
                                                )
                                                AND 
                                                (
                                                   flow_block.block_id = block.id 
                                                )
                                          )
                                       )
                                 )
                              )
                              AND 
                              (
                                 block_choice.block_id = block.id 
                              )
                              AND 
                              (
                                 flow_block.block_id = block.id 
                              )
                        )
                     )
               )
            )
            AND 
            (
               flow_block.block_id = block.id 
            )
      )
   )
;

[RichardLindhout]

@troian Maybe something is wrong with the time filter.

I have the following query which I think is the same as yours relationship wise

query{
  blocks(filter:{
    where: {
      blockType:{
        equalTo: "CHOICE"
      }
      flowBlocks:{
        block:{
          id:{equalTo:"block-4"},
        }
      }
    }
  }){
    id
   title
    flowBlocks{
      id
      block{
        id
        blockType
        blockChoices{
          id
          title
          block{
            id
          }
        }
      }
    }
  }
}

SELECT * FROM `block` WHERE `block`.`organization_id` = 1 AND (block_type = 'CHOICE' AND EXISTS(SELECT 1 FROM `flow_block` WHERE (block_id = 4) AND (flow_block.block_id = block.id)));

[RichardLindhout]

Or maybe it's postgres related.. Hmm. Maybe we should ask Aaron.

[RichardLindhout]

@troian try v2.1.4 and re-generate the filters

[troian]

The issue is definitely in there Here is generated SQL query and the last placeholder is set tp $1 but should be $5

SELECT * FROM "appointment" WHERE (((starts_at >= $1 AND starts_at < $2) OR (ends_at >= $3 AND ends_at < $4)) AND EXISTS(SELECT 1 FROM "appointment_participant" WHERE (account_id = $1)));

[RichardLindhout]

How do get this sql, I only got ? inside of it. Or is this postgres related?

SELECT * FROM `block` WHERE `block`.`organization_id` = ? AND (block_type = ? AND EXISTS(SELECT 1 FROM `flow_block` WHERE (block_id = ?) AND (flow_block.block_id = block.id)));

[RichardLindhout]

I think this is something sqlboiler should handle to be honest. It should see that subqueries are provided and ++ the indexes

[RichardLindhout]

Ok forgot about this xD

func appendSubQuery(queryMods []qm.QueryMod, q *queries.Query) []qm.QueryMod {
    qs, args := queries.BuildQuery(q)
    qsClean := strings.TrimSuffix(qs, ";")
    return append(queryMods, qm.Where(fmt.Sprintf("EXISTS(%v)", qsClean), args...))
}

[troian]

Yah, issue happens when appendSubQuery in filter.go invokes BuildQuery from sqlboiler Thats why rubbish below gets it to work

    member := reflect.ValueOf(q).Elem().FieldByName("dialect")
    dialectPtr := (**drivers.Dialect)(unsafe.Pointer(member.UnsafeAddr()))
    dialect := **dialectPtr
    dialect.UseIndexPlaceholders = false
    *dialectPtr = &dialect

[RichardLindhout]

Ok, let's discuss this issue with Aaron :)

[RichardLindhout]

Aaron said in Slack he has no time a.t.m. so I think we could maybe workaround this ourselves or use the reflect hack for the time being. Or do a pull request for sqlboiler.

I think my usage of the BuildQuery is not done before but the alternative is all raw queries which I want to prevent.

[troian]

Probably it worth to do it with reflect hack (or whatever option would fit). as this issue is quite a big hold

[RichardLindhout]

I've proposed something to Aaron: https://github.com/volatiletech/sqlboiler/issues/482#issuecomment-660898770

We could probably export a more advanced query builder with some options

[RichardLindhout]

Then we can disable the questionMarks without using reflect. But it would need a PR to sqlboiler

[RichardLindhout]

@troian can you verify if the filter works when replacing

filter.go (use BuildSubQuery instead of BuildQuery!)

qs, args := queries.BuildSubQuery(q)
return append(queryMods, qm.Where(fmt.Sprintf("EXISTS(%v)", qs), args...))

sqlboiler code queries/query_builder.go with the following code+

package queries

import (
    "bytes"
    "fmt"
    "regexp"
    "sort"
    "strings"

    "github.com/volatiletech/strmangle"
)

var (
    rgxIdentifier = regexp.MustCompile(`^(?i)"?[a-z_][_a-z0-9]*"?(?:\."?[_a-z][_a-z0-9]*"?)*$`)
    rgxInClause   = regexp.MustCompile(`^(?i)(.*[\s|\)|\?])IN([\s|\(|\?].*)$`)
)

type QueryOptions struct {
    SemiSuffix bool
    UseIndexPlaceholders bool
}

var queryOptions = QueryOptions{
    SemiSuffix: true,
    UseIndexPlaceholders: true,
}

// BuildQuery builds a query object into the query string
// and it's accompanying arguments. Using this method
// allows query building without immediate execution.
func BuildQuery(q *Query) (string, []interface{}) {
    return BuildQueryWithOptions(q, queryOptions)
}

var subQueryOptions = QueryOptions{
    SemiSuffix: false,
    UseIndexPlaceholders: false,
}

// BuildSubQuery builds a query object into the query string
// and it's accompanying arguments. Using this method
// allows query building without immediate execution.
func BuildSubQuery(q *Query) (string, []interface{}) {
    return BuildQueryWithOptions(q, subQueryOptions)
}

// BuildQueryWithOptions builds a query object into the query string
// and it's accompanying arguments. Using this method
// allows query building without immediate execution.
func BuildQueryWithOptions(q *Query, o QueryOptions) (string, []interface{}) {
    var buf *bytes.Buffer
    var args []interface{}

    switch {
    case len(q.rawSQL.sql) != 0:
        return q.rawSQL.sql, q.rawSQL.args
    case q.delete:
        buf, args = buildDeleteQuery(q, o)
    case len(q.update) > 0:
        buf, args = buildUpdateQuery(q, o)
    default:
        buf, args = buildSelectQuery(q, o)
    }

    defer strmangle.PutBuffer(buf)

    // Cache the generated query for query object re-use
    bufStr := buf.String()
    q.rawSQL.sql = bufStr
    q.rawSQL.args = args

    return bufStr, args
}

func buildSelectQuery(q *Query, o QueryOptions) (*bytes.Buffer, []interface{}) {
    buf := strmangle.GetBuffer()
    var args []interface{}

    writeCTEs(q, buf, &args)

    buf.WriteString("SELECT ")

    if q.dialect.UseTopClause {
        if q.limit != 0 && q.offset == 0 {
            fmt.Fprintf(buf, " TOP (%d) ", q.limit)
        }
    }

    if q.count {
        buf.WriteString("COUNT(")
    }

    hasSelectCols := len(q.selectCols) != 0
    hasJoins := len(q.joins) != 0
    hasDistinct := q.distinct != ""
    if hasDistinct {
        buf.WriteString("DISTINCT ")
        if q.count {
            buf.WriteString("(")
        }
        buf.WriteString(q.distinct)
        if q.count {
            buf.WriteString(")")
        }
    } else if hasJoins && hasSelectCols && !q.count {
        selectColsWithAs := writeAsStatements(q)
        // Don't identQuoteSlice - writeAsStatements does this
        buf.WriteString(strings.Join(selectColsWithAs, ", "))
    } else if hasSelectCols {
        buf.WriteString(strings.Join(strmangle.IdentQuoteSlice(q.dialect.LQ, q.dialect.RQ, q.selectCols), ", "))
    } else if hasJoins && !q.count {
        selectColsWithStars := writeStars(q)
        buf.WriteString(strings.Join(selectColsWithStars, ", "))
    } else {
        buf.WriteByte('*')
    }

    // close SQL COUNT functionxw
    if q.count {
        buf.WriteByte(')')
    }

    fmt.Fprintf(buf, " FROM %s", strings.Join(strmangle.IdentQuoteSlice(q.dialect.LQ, q.dialect.RQ, q.from), ", "))

    if len(q.joins) > 0 {
        argsLen := len(args)
        joinBuf := strmangle.GetBuffer()
        for _, j := range q.joins {
            switch j.kind {
            case JoinInner:
                fmt.Fprintf(joinBuf, " INNER JOIN %s", j.clause)
            case JoinOuterLeft:
                fmt.Fprintf(joinBuf, " LEFT JOIN %s", j.clause)
            case JoinOuterRight:
                fmt.Fprintf(joinBuf, " RIGHT JOIN %s", j.clause)
            case JoinOuterFull:
                fmt.Fprintf(joinBuf, " FULL JOIN %s", j.clause)
            default:
                panic(fmt.Sprintf("Unsupported join of kind %v", j.kind))
            }
            args = append(args, j.args...)
        }
        var resp string
        if q.dialect.UseIndexPlaceholders && o.UseIndexPlaceholders {
            resp, _ = convertQuestionMarks(joinBuf.String(), argsLen+1)
        } else {
            resp = joinBuf.String()
        }
        fmt.Fprintf(buf, resp)
        strmangle.PutBuffer(joinBuf)
    }

    where, whereArgs := whereClause(q, o, len(args)+1)
    buf.WriteString(where)
    if len(whereArgs) != 0 {
        args = append(args, whereArgs...)
    }

    writeModifiers(q, buf, &args)

    if o.SemiSuffix {
        buf.WriteByte(';')
    }

    return buf, args
}

func buildDeleteQuery(q *Query, o QueryOptions) (*bytes.Buffer, []interface{}) {
    var args []interface{}
    buf := strmangle.GetBuffer()

    writeCTEs(q, buf, &args)

    buf.WriteString("DELETE FROM ")
    buf.WriteString(strings.Join(strmangle.IdentQuoteSlice(q.dialect.LQ, q.dialect.RQ, q.from), ", "))

    where, whereArgs := whereClause(q, o, 1)
    if len(whereArgs) != 0 {
        args = append(args, whereArgs...)
    }
    buf.WriteString(where)

    writeModifiers(q, buf, &args)

    if o.SemiSuffix {
        buf.WriteByte(';')
    }

    return buf, args
}

func buildUpdateQuery(q *Query, o QueryOptions) (*bytes.Buffer, []interface{}) {
    buf := strmangle.GetBuffer()
    var args []interface{}

    writeCTEs(q, buf, &args)

    buf.WriteString("UPDATE ")
    buf.WriteString(strings.Join(strmangle.IdentQuoteSlice(q.dialect.LQ, q.dialect.RQ, q.from), ", "))

    cols := make(sort.StringSlice, len(q.update))

    count := 0
    for name := range q.update {
        cols[count] = name
        count++
    }

    cols.Sort()

    for i := 0; i < len(cols); i++ {
        args = append(args, q.update[cols[i]])
        cols[i] = strmangle.IdentQuote(q.dialect.LQ, q.dialect.RQ, cols[i])
    }

    setSlice := make([]string, len(cols))
    for index, col := range cols {
        setSlice[index] = fmt.Sprintf("%s = %s", col, strmangle.Placeholders(q.dialect.UseIndexPlaceholders && o.UseIndexPlaceholders, 1, index+1, 1))
    }
    fmt.Fprintf(buf, " SET %s", strings.Join(setSlice, ", "))

    where, whereArgs := whereClause(q, o, len(args)+1)
    if len(whereArgs) != 0 {
        args = append(args, whereArgs...)
    }
    buf.WriteString(where)

    writeModifiers(q, buf, &args)

    if o.SemiSuffix {
        buf.WriteByte(';')
    }

    return buf, args
}

func writeParameterizedModifiers(q *Query, buf *bytes.Buffer, args *[]interface{}, keyword, delim string, clauses []argClause) {
    argsLen := len(*args)
    modBuf := strmangle.GetBuffer()
    fmt.Fprintf(modBuf, keyword)

    for i, j := range clauses {
        if i > 0 {
            modBuf.WriteString(delim)
        }
        modBuf.WriteString(j.clause)
        *args = append(*args, j.args...)
    }

    var resp string
    if q.dialect.UseIndexPlaceholders {
        resp, _ = convertQuestionMarks(modBuf.String(), argsLen+1)
    } else {
        resp = modBuf.String()
    }

    buf.WriteString(resp)
    strmangle.PutBuffer(modBuf)
}

func writeModifiers(q *Query, buf *bytes.Buffer, args *[]interface{}) {
    if len(q.groupBy) != 0 {
        fmt.Fprintf(buf, " GROUP BY %s", strings.Join(q.groupBy, ", "))
    }

    if len(q.having) != 0 {
        writeParameterizedModifiers(q, buf, args, " HAVING ", " AND ", q.having)
    }

    if len(q.orderBy) != 0 {
        writeParameterizedModifiers(q, buf, args, " ORDER BY ", ", ", q.orderBy)
    }

    if !q.dialect.UseTopClause {
        if q.limit != 0 {
            fmt.Fprintf(buf, " LIMIT %d", q.limit)
        }

        if q.offset != 0 {
            fmt.Fprintf(buf, " OFFSET %d", q.offset)
        }
    } else {
        // From MS SQL 2012 and above: https://technet.microsoft.com/en-us/library/ms188385(v=sql.110).aspx
        // ORDER BY ...
        // OFFSET N ROWS
        // FETCH NEXT M ROWS ONLY
        if q.offset != 0 {

            // Hack from https://www.microsoftpressstore.com/articles/article.aspx?p=2314819
            // ...
            // As mentioned, the OFFSET-FETCH filter requires an ORDER BY clause. If you want to use arbitrary order,
            // like TOP without an ORDER BY clause, you can use the trick with ORDER BY (SELECT NULL)
            // ...
            if len(q.orderBy) == 0 {
                buf.WriteString(" ORDER BY (SELECT NULL)")
            }

            // This seems to be the latest version of mssql's syntax for offset
            // (the suffix ROWS)
            // This is true for latest sql server as well as their cloud offerings & the upcoming sql server 2019
            // https://docs.microsoft.com/en-us/sql/t-sql/queries/select-order-by-clause-transact-sql?view=sql-server-2017
            // https://docs.microsoft.com/en-us/sql/t-sql/queries/select-order-by-clause-transact-sql?view=sql-server-ver15
            fmt.Fprintf(buf, " OFFSET %d ROWS", q.offset)

            if q.limit != 0 {
                fmt.Fprintf(buf, " FETCH NEXT %d ROWS ONLY", q.limit)
            }
        }
    }

    if len(q.forlock) != 0 {
        fmt.Fprintf(buf, " FOR %s", q.forlock)
    }
}

func writeStars(q *Query) []string {
    cols := make([]string, len(q.from))
    for i, f := range q.from {
        toks := strings.Split(f, " ")
        if len(toks) == 1 {
            cols[i] = fmt.Sprintf(`%s.*`, strmangle.IdentQuote(q.dialect.LQ, q.dialect.RQ, toks[0]))
            continue
        }

        alias, name, ok := parseFromClause(toks)
        if !ok {
            return nil
        }

        if len(alias) != 0 {
            name = alias
        }
        cols[i] = fmt.Sprintf(`%s.*`, strmangle.IdentQuote(q.dialect.LQ, q.dialect.RQ, name))
    }

    return cols
}

func writeAsStatements(q *Query) []string {
    cols := make([]string, len(q.selectCols))
    for i, col := range q.selectCols {
        if !rgxIdentifier.MatchString(col) {
            cols[i] = col
            continue
        }

        toks := strings.Split(col, ".")
        if len(toks) == 1 {
            cols[i] = strmangle.IdentQuote(q.dialect.LQ, q.dialect.RQ, col)
            continue
        }

        asParts := make([]string, len(toks))
        for j, tok := range toks {
            asParts[j] = strings.Trim(tok, `"`)
        }

        cols[i] = fmt.Sprintf(`%s as "%s"`, strmangle.IdentQuote(q.dialect.LQ, q.dialect.RQ, col), strings.Join(asParts, "."))
    }

    return cols
}

// whereClause parses a where slice and converts it into a
// single WHERE clause like:
// WHERE (a=$1) AND (b=$2) AND (a,b) in (($3, $4), ($5, $6))
//
// startAt specifies what number placeholders start at
func whereClause(q *Query, o QueryOptions, startAt int) (string, []interface{}) {
    if len(q.where) == 0 {
        return "", nil
    }

    manualParens := false
ManualParen:
    for _, w := range q.where {
        switch w.kind {
        case whereKindLeftParen, whereKindRightParen:
            manualParens = true
            break ManualParen
        }
    }

    buf := strmangle.GetBuffer()
    defer strmangle.PutBuffer(buf)
    var args []interface{}

    notFirstExpression := false
    buf.WriteString(" WHERE ")
    for _, where := range q.where {
        if notFirstExpression && where.kind != whereKindRightParen {
            if where.orSeparator {
                buf.WriteString(" OR ")
            } else {
                buf.WriteString(" AND ")
            }
        } else {
            notFirstExpression = true
        }

        switch where.kind {
        case whereKindNormal:
            if !manualParens {
                buf.WriteByte('(')
            }
            if q.dialect.UseIndexPlaceholders && o.UseIndexPlaceholders {
                replaced, n := convertQuestionMarks(where.clause, startAt)
                buf.WriteString(replaced)
                startAt += n
            } else {
                buf.WriteString(where.clause)
            }
            if !manualParens {
                buf.WriteByte(')')
            }
            args = append(args, where.args...)
        case whereKindLeftParen:
            buf.WriteByte('(')
            notFirstExpression = false
        case whereKindRightParen:
            buf.WriteByte(')')
        case whereKindIn:
            ln := len(where.args)
            // WHERE IN () is invalid sql, so it is difficult to simply run code like:
            // for _, u := range model.Users(qm.WhereIn("id IN ?",uids...)).AllP(db) {
            //    ...
            // }
            // instead when we see empty IN we produce 1=0 so it can still be chained
            // with other queries
            if ln == 0 {
                buf.WriteString("(1=0)")
                break
            }
            matches := rgxInClause.FindStringSubmatch(where.clause)
            // If we can't find any matches attempt a simple replace with 1 group.
            // Clauses that fit this criteria will not be able to contain ? in their
            // column name side, however if this case is being hit then the regexp
            // probably needs adjustment, or the user is passing in invalid clauses.
            if matches == nil {
                clause, count := convertInQuestionMarks(q.dialect.UseIndexPlaceholders && o.UseIndexPlaceholders, where.clause, startAt, 1, ln)
                if !manualParens {
                    buf.WriteByte('(')
                }
                buf.WriteString(clause)
                if !manualParens {
                    buf.WriteByte(')')
                }
                args = append(args, where.args...)
                startAt += count
                break
            }

            leftSide := strings.TrimSpace(matches[1])
            rightSide := strings.TrimSpace(matches[2])
            // If matches are found, we have to parse the left side (column side)
            // of the clause to determine how many columns they are using.
            // This number determines the groupAt for the convert function.
            cols := strings.Split(leftSide, ",")
            cols = strmangle.IdentQuoteSlice(q.dialect.LQ, q.dialect.RQ, cols)
            groupAt := len(cols)

            var leftClause string
            var leftCount int
            if q.dialect.UseIndexPlaceholders && o.UseIndexPlaceholders {
                leftClause, leftCount = convertQuestionMarks(strings.Join(cols, ","), startAt)
            } else {
                // Count the number of cols that are question marks, so we know
                // how much to offset convertInQuestionMarks by
                for _, v := range cols {
                    if v == "?" {
                        leftCount++
                    }
                }
                leftClause = strings.Join(cols, ",")
            }
            rightClause, rightCount := convertInQuestionMarks(q.dialect.UseIndexPlaceholders && o.UseIndexPlaceholders, rightSide, startAt+leftCount, groupAt, ln-leftCount)
            if !manualParens {
                buf.WriteByte('(')
            }
            buf.WriteString(leftClause)
            buf.WriteString(" IN ")
            buf.WriteString(rightClause)
            if !manualParens {
                buf.WriteByte(')')
            }
            startAt += leftCount + rightCount
            args = append(args, where.args...)
        default:
            panic("unknown where type")
        }
    }

    return buf.String(), args
}

// convertInQuestionMarks finds the first unescaped occurrence of ? and swaps it
// with a list of numbered placeholders, starting at startAt.
// It uses groupAt to determine how many placeholders should be in each group,
// for example, groupAt 2 would result in: (($1,$2),($3,$4))
// and groupAt 1 would result in ($1,$2,$3,$4)
func convertInQuestionMarks(UseIndexPlaceholders bool, clause string, startAt, groupAt, total int) (string, int) {
    if startAt == 0 || len(clause) == 0 {
        panic("Not a valid start number.")
    }

    paramBuf := strmangle.GetBuffer()
    defer strmangle.PutBuffer(paramBuf)

    foundAt := -1
    for i := 0; i < len(clause); i++ {
        if (i == 0 && clause[i] == '?') || (clause[i] == '?' && clause[i-1] != '\\') {
            foundAt = i
            break
        }
    }

    if foundAt == -1 {
        return strings.ReplaceAll(clause, `\?`, "?"), 0
    }

    paramBuf.WriteString(clause[:foundAt])
    paramBuf.WriteByte('(')
    paramBuf.WriteString(strmangle.Placeholders(UseIndexPlaceholders, total, startAt, groupAt))
    paramBuf.WriteByte(')')
    paramBuf.WriteString(clause[foundAt+1:])

    // Remove all backslashes from escaped question-marks
    ret := strings.ReplaceAll(paramBuf.String(), `\?`, "?")
    return ret, total
}

// convertQuestionMarks converts each occurrence of ? with $<number>
// where <number> is an incrementing digit starting at startAt.
// If question-mark (?) is escaped using back-slash (\), it will be ignored.
func convertQuestionMarks(clause string, startAt int) (string, int) {
    if startAt == 0 {
        panic("Not a valid start number.")
    }

    paramBuf := strmangle.GetBuffer()
    defer strmangle.PutBuffer(paramBuf)
    paramIndex := 0
    total := 0

    for {
        if paramIndex >= len(clause) {
            break
        }

        clause = clause[paramIndex:]
        paramIndex = strings.IndexByte(clause, '?')

        if paramIndex == -1 {
            paramBuf.WriteString(clause)
            break
        }

        escapeIndex := strings.Index(clause, `\?`)
        if escapeIndex != -1 && paramIndex > escapeIndex {
            paramBuf.WriteString(clause[:escapeIndex] + "?")
            paramIndex++
            continue
        }

        paramBuf.WriteString(clause[:paramIndex] + fmt.Sprintf("$%d", startAt))
        total++
        startAt++
        paramIndex++
    }

    return paramBuf.String(), total
}

// parseFromClause will parse something that looks like
// a
// a b
// a as b
func parseFromClause(toks []string) (alias, name string, ok bool) {
    if len(toks) > 3 {
        toks = toks[:3]
    }

    sawIdent, sawAs := false, false
    for _, tok := range toks {
        if t := strings.ToLower(tok); sawIdent && t == "as" {
            sawAs = true
            continue
        } else if sawIdent && t == "on" {
            break
        }

        if !rgxIdentifier.MatchString(tok) {
            break
        }

        if sawIdent || sawAs {
            alias = strings.Trim(tok, `"`)
            break
        }

        name = strings.Trim(tok, `"`)
        sawIdent = true
        ok = true
    }

    return alias, name, ok
}

func writeCTEs(q *Query, buf *bytes.Buffer, args *[]interface{}) {
    if len(q.withs) == 0 {
        return
    }

    buf.WriteString("WITH")
    argsLen := len(*args)
    withBuf := strmangle.GetBuffer()
    lastPos := len(q.withs) - 1
    for i, w := range q.withs {
        fmt.Fprintf(withBuf, " %s", w.clause)
        if i >= 0 && i < lastPos {
            withBuf.WriteByte(',')
        }
        *args = append(*args, w.args...)
    }
    withBuf.WriteByte(' ')
    var resp string
    if q.dialect.UseIndexPlaceholders {
        resp, _ = convertQuestionMarks(withBuf.String(), argsLen+1)
    } else {
        resp = withBuf.String()
    }
    fmt.Fprintf(buf, resp)
    strmangle.PutBuffer(withBuf)
}

[RichardLindhout]

I'm interested though how this understands the arguments in the parent query understand that the arguments of the subquery. Can we have both query arguments in the parent query and the subquery etc . Before and after a sub query, we need some tests for this on sqlboiler too.

[troian]

@RichardLindhout i'll give it a check but atm it looks about right. About substituting subqueries parameters I have exact same question. That was actually made me a surprise when trick with reflect did work.

[RichardLindhout]

Hey @troian thanks for your workaround! I have released this under https://github.com/web-ridge/gqlgen-sqlboiler/releases/tag/v2.1.5

To enable it:

api.AddPlugin(gbgen.NewConvertPlugin(
            a,  
            b,  
            b, 
            gbgen.ConvertPluginConfig{
                UseReflectWorkaroundForSubModelFilteringInPostgresIssue25: true,
            },
        )),