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