failed to assign all query results to <type> slice

[troian]

For the following schema and generated query mods, sqlboiler returns an error

SELECT * FROM "deal" WHERE (creator_id = $1 AND EXISTS(SELECT 1 FROM "user" WHERE (id = $1)));
[8875fd38-b75d-45a7-a5a1-cbee58c8716d 8875fd38-b75d-45a7-a5a1-cbee58c8716d]
{"level":"error","error":"models: failed to assign all query results to Deal slice: bind failed to execute query: pq: got 2 parameters but the statement requires 1","time":"2020-05-11T07:27:58-04:00","message":"Could not list deals"}

If comment subQueryMods := UserWhereToMods(m, !hasForeignKeyInRoot) part from function below it seem to start working

func UserWhereSubqueryToMods(m *graphql_models.UserWhere, foreignColumn string) []qm.QueryMod {
    if m == nil {
        return nil
    }
    var queryMods []qm.QueryMod
    queryMods = append(queryMods)

    // if foreign key exist so we can filter on ID in the root table instead of subquery
    hasForeignKeyInRoot := foreignColumn != ""
    if hasForeignKeyInRoot {
        queryMods = append(queryMods, IDFilterToMods(m.ID, foreignColumn)...)
    }

    subQueryMods := UserWhereToMods(m, !hasForeignKeyInRoot)
    if len(subQueryMods) > 0 {
        subQuery := models.Users(append(subQueryMods, qm.Select("1"))...)
        queryMods = appendSubQuery(queryMods, subQuery.Query)
    }
    return queryMods
}

type Deal {
    id: ID!
    creator: User!
    negotiation: Negotiation
    agreement: Agreement
    updatedAt: Int!
    createdAt: Int!
    agreements: [Agreement]
    appointments: [Appointment]
    negotiations: [Negotiation]
    userDeals: [UserDeal]
}

input DealFilter {
    search: String
    where: DealWhere
}

input DealWhere {
    id: IDFilter
    creator: UserWhere
    negotiation: NegotiationWhere
    agreement: AgreementWhere
    updatedAt: IntFilter
    createdAt: IntFilter
    agreements: AgreementWhere
    appointments: AppointmentWhere
    negotiations: NegotiationWhere
    userDeals: UserDealWhere
    or: DealWhere
    and: DealWhere
}

type Query {
    deals(filter: DealFilter): [Deal!]!
}

[RichardLindhout]

What is your graphql query?

[troian]

Sorry, missed it

query {
    deals(filter: {
        where: {
            creator: {
                id: {
                    equalTo: "8875fd38-b75d-45a7-a5a1-cbee58c8716d"
                }
            }
        }
    }) {
        id
    }
}

[RichardLindhout]

I don't seem to have this problem here:

SELECT * FROM `flow_block` WHERE `flow_block`.`organization_id` = ? AND (EXISTS(SELECT 1 FROM `flow_block` WHERE (flow_id = ?)));
[1 1]

    where:{
      flowBlock:{
        flow: {
          id: {
            equalTo: "flow-1"
          }
        }
      }
    }

Maybe it is the StringToInterfaces function (Update: it is not the case)

[RichardLindhout]

It should not be doing the AND EXISTS(SELECT 1 FROM "user" WHERE (id = $1) anyway since it's in your root table.

[RichardLindhout]

Also this line seems to be off: queryMods = append(queryMods) we need to remove that :-D

[RichardLindhout]

Is hasForeignKeyInRoot true or false? if you log it?

[RichardLindhout]

Could it be that you removed the

    if m == nil {
        return nil
    }

Inside these functions?

func StringFilterToMods(m *graphql_models.StringFilter, column string) []qm.QueryMod {
    if m == nil {
        return nil
    }

[RichardLindhout]

Maybe when debugging https://github.com/web-ridge/gqlgen-sqlboiler/issues/17

[troian]

Thats current StringFilterToMods

func StringFilterToMods(m *graphql_models.StringFilter, column string) []qm.QueryMod {
    if m == nil {
        return nil
    }

    var queryMods []qm.QueryMod
    if m.EqualTo != nil {
        queryMods = append(queryMods, qmhelper.Where(column, qmhelper.EQ, *m.EqualTo))
    }
    if m.NotEqualTo != nil {
        queryMods = append(queryMods, qmhelper.Where(column, qmhelper.NEQ, *m.NotEqualTo))
    }

    lowerColumn := "LOWER(" + column + ")"
    if m.StartWith != nil {
        queryMods = append(queryMods, qm.Where(lowerColumn+isLike, startsWithValue(strings.ToLower(*m.StartWith))))
    }
    if m.EndWith != nil {
        queryMods = append(queryMods, qm.Where(lowerColumn+isLike, endsWithValue(strings.ToLower(*m.EndWith))))
    }
    if m.Contain != nil {
        queryMods = append(queryMods, qm.Where(lowerColumn+isLike, containsValue(strings.ToLower(*m.Contain))))
    }

    if m.StartWithStrict != nil {
        queryMods = append(queryMods, qm.Where(column+isLike, startsWithValue(*m.StartWithStrict)))
    }
    if m.EndWithStrict != nil {
        queryMods = append(queryMods, qm.Where(column+isLike, endsWithValue(*m.EndWithStrict)))
    }
    if m.ContainStrict != nil {
        queryMods = append(queryMods, qm.Where(column+isLike, containsValue(*m.ContainStrict)))
    }

    if len(m.In) > 0 {
        queryMods = append(queryMods, qm.WhereIn(column+in, boilergql.IDsToBoilerInterfaces(m.In)...))
    }
    if len(m.NotIn) > 0 {
        queryMods = append(queryMods, qm.WhereIn(column+notIn, boilergql.IDsToBoilerInterfaces(m.NotIn)...))
    }

    return queryMods
}

[troian]

hasForeignKeyInRoot is true

[RichardLindhout]

subQueryMods := UserWhereToMods(m, !hasForeignKeyInRoot)

Should return nothing based on your query. I think it does return something in your case. The

    if m == nil {
        return nil
    }

is very important in all of the FilterToMods. Maybe somehow this is not the case somewhere

[troian]

What I get for this query

hasForeignKeyInRoot
subQueryMods [{id = ? [8875fd38-b75d-45a7-a5a1-cbee58c8716d]}]

[RichardLindhout]

That should not be the case

Is the

if m == nil {
        return nil
    }

In the UserWhereSubqueryToMods?

func UserWhereSubqueryToMods(m *graphql_models.User, foreignColumn string) []qm.QueryMod {
    if m == nil {
        return nil
    }

[RichardLindhout]

    if withPrimaryID {
        queryMods = append(queryMods, IDFilterToMods(m.ID, models.BlockColumns.ID)...)
    }

Should not add this subquery since it's not needed since it's already filtered via your foreign key

[troian]

Yah,

That's what is in, with some rubbish logging from me

func UserWhereSubqueryToMods(m *graphql_models.UserWhere, foreignColumn string) []qm.QueryMod {
    if m == nil {
        return nil
    }
    var queryMods []qm.QueryMod
    queryMods = append(queryMods)

    // if foreign key exist so we can filter on ID in the root table instead of subquery
    hasForeignKeyInRoot := foreignColumn != ""
    if hasForeignKeyInRoot {
        fmt.Println("hasForeignKeyInRoot")
        queryMods = append(queryMods, IDFilterToMods(m.ID, foreignColumn)...)
    }

    subQueryMods := UserWhereToMods(m, !hasForeignKeyInRoot)
    if len(subQueryMods) > 0 {
        subQuery := models.Users(append(subQueryMods, qm.Select("1"))...)
        queryMods = appendSubQuery(queryMods, subQuery.Query)
    }

    fmt.Println("subQueryMods", subQueryMods)

    return queryMods
}

[RichardLindhout]

In your userWhereToMods the if withPrimaryID { should exist

func UsersWhereToMods(m *graphql_models.UserWhere, withPrimaryID bool) []qm.QueryMod {
    if m == nil {
        return nil
    }
    var queryMods []qm.QueryMod

    if withPrimaryID {
fmt.Println("THIS SHOULD NOT LOG IN YOUR CASE ")
        queryMods = append(queryMods, IDFilterToMods(m.ID, models.BlockColumns.ID)...)
    }
..................

[RichardLindhout]

Ok in my case it runs too I see now xD. Looking into it further

[RichardLindhout]

Ok, it runs only when for the root. I do not get any subqueries back

[RichardLindhout]

I dit put the log in te wrong place. So still can not reproduce it :)

[RichardLindhout]

In my example this log never runs it's the same as yours relationship wise. e.g. FlowBlock has a parent Flow Deal has a parent User

func FlowWhereToMods(m *graphql_models.FlowWhere, withPrimaryID bool) []qm.QueryMod {
    if m == nil {
        return nil
    }
    var queryMods []qm.QueryMod

    if withPrimaryID {
        fmt.Println("FlowWhereToMods withPrimaryID == true")

        queryMods = append(queryMods, IDFilterToMods(m.ID, models.FlowColumns.ID)...)
    }

[troian]

In my case if withPrimaryID { does not exist in UserWhereToMods

func UserWhereToMods(m *graphql_models.UserWhere, withPrimaryID bool) []qm.QueryMod {
    if m == nil {
        return nil
    }
    var queryMods []qm.QueryMod

    queryMods = append(queryMods, IDFilterToMods(m.ID, models.UserColumns.ID)...)
    queryMods = append(queryMods, StringFilterToMods(m.FirstName, models.UserColumns.FirstName)...)
    queryMods = append(queryMods, StringFilterToMods(m.MiddleName, models.UserColumns.MiddleName)...)
    queryMods = append(queryMods, StringFilterToMods(m.LastName, models.UserColumns.LastName)...)
    queryMods = append(queryMods, StringFilterToMods(m.About, models.UserColumns.About)...)
    queryMods = append(queryMods, StringFilterToMods(m.Marketing, models.UserColumns.Marketing)...)
    queryMods = append(queryMods, StringFilterToMods(m.Strategy, models.UserColumns.Strategy)...)
    queryMods = append(queryMods, StringFilterToMods(m.Negotiation, models.UserColumns.Negotiation)...)
    queryMods = append(queryMods, StringFilterToMods(m.Avatar, models.UserColumns.Avatar)...)
    queryMods = append(queryMods, IntFilterToMods(m.CreatedAt, models.UserColumns.CreatedAt)...)
    queryMods = append(queryMods, IntFilterToMods(m.UpdatedAt, models.UserColumns.UpdatedAt)...)
    queryMods = append(queryMods, AgreementWhereSubqueryToMods(m.FromAgreements, "")...)
    queryMods = append(queryMods, AgreementWhereSubqueryToMods(m.ToAgreements, "")...)
    queryMods = append(queryMods, DealWhereSubqueryToMods(m.CreatorDeals, "")...)
    queryMods = append(queryMods, UserAppointmentWhereSubqueryToMods(m.UserAppointments, "")...)
    queryMods = append(queryMods, UserCompanyOfficeWhereSubqueryToMods(m.UserCompanyOffices, "")...)
    queryMods = append(queryMods, UserDealWhereSubqueryToMods(m.UserDeals, "")...)
    queryMods = append(queryMods, UserRoleWhereSubqueryToMods(m.UserRoles, "")...)
    queryMods = append(queryMods, UserSocialWhereSubqueryToMods(m.UserSocials, "")...)
    queryMods = append(queryMods, UserTagWhereSubqueryToMods(m.UserTags, "")...)
    if m.Or != nil {
        queryMods = append(queryMods, qm.Or2(qm.Expr(UserWhereToMods(m.Or, true)...)))
    }
    if m.And != nil {
        queryMods = append(queryMods, qm.Expr(UserWhereToMods(m.And, true)...))
    }
    return queryMods
}```

[RichardLindhout]

Somehow the UserWhereToMods adds a ID to the subquery but it should not since withPrimaryID should be false since it's !hasForeignKeyInRoot

        queryMods = append(queryMods, IDFilterToMods(m.ID, models.FlowColumns.ID)...)

[RichardLindhout]

Ok thats the problem!

[RichardLindhout]

Looking into it ;)

[RichardLindhout]

Thanks for all your help that's a bug in gqlgen-sqlboiler!

[RichardLindhout]

This is the cause of the bug. IsPrimaryNumberID in your case it's a string. So I'm changing it to IsPrimaryID

[troian]

Won't it break existing usage of the library for other users? Maybe worth to introduce some config file?

[RichardLindhout]

No, it keeps working for number id's. But it's a bug with support stringId's.

Number id's have different convert functions.

[RichardLindhout]

We surely need some config in the future. Also for this https://github.com/web-ridge/gqlgen-sqlboiler/issues/18. But I'll probably tag it as a v3 release since it's a breaking change more or less.

[RichardLindhout]

This is really not, since it only fix this bug. Users with int id's did not have this problem, that's why I did not have the issue.

[troian]

I see, makes sense

[RichardLindhout]

v2.0.9 should work!

[RichardLindhout]

I also really want to spend more time automatically testing the results so that everyone's use case continues to work properly. Also more in development branch before real releases, it's on the roadmap, but I have some tight deadlines at the moment so mostly trying it out in production right away so the basic thing should keep working just fine. The string id's are another use-case for which there is a reason to make automatic tests because they are not used internally and can therefore be broken more easily.

I'm thinking in automatic test as in integration test which test the whole flow gqlgen -> gqlgen-sqlboiler -> boiler.

which will result in resolver_test.go files if you choose to opt-in for that so you have a lot of testing coverage out of the box.

[troian]

It does work indeed! Thanks

[RichardLindhout]

Thanks. Also updated the examples! https://github.com/web-ridge/gqlgen-sqlboiler-examples/commit/04f41c4e34333dbd8b01a5e807dbcfe566387f2a