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: volatiletech/sqlboiler#482 (comment)

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]

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,
			},
		)),