How to generate nested select statements

[parnic]

Apologies if this is answered somewhere, I tried searching and couldn't find anything.

If I wanted to generate a nested select statement/select subquery similar to the following:

SELECT * FROM players WHERE team_id IN (SELECT id FROM teams WHERE season_id IN (SELECT id FROM seasons WHERE status='active'))

Is that possible with goqu? I've tried something like this for the nested bits:

seasonQuery := dialect.From(models.TableNames.Seasons).Select(models.SeasonColumns.ID).Where(goqu.C(models.SeasonColumns.Status).Eq(models.SeasonsStatusActive))
teamQuery := dialect.From(models.TableNames.Teams).Select(models.TeamColumns.ID).Where(goqu.C(models.TeamColumns.SeasonID).In(seasonQuery))

but using an SQLite dialect it ends up generating a query like:

SELECT `id` FROM `teams` WHERE (`season_id` IN ((SELECT `id` FROM `seasons` WHERE (`status` = 'active'))))

where the extra () in the IN breaks the select from seeing multiple IDs.

Is there a way to use one SelectDataset, or a derivative of it, in an In() like this?

[parnic]

It looks like replacing In() with Eq() in the code above causes goqu to turn the Eq into an In internally and generate an appropriate query, so that works for me. I wouldn't have expected it to do that translation (especially when In() doesn't work the same way), so I guess consider this a feature request instead of a question.

Behaves as desired:

seasonQuery := dialect.From(models.TableNames.Seasons).Select(models.SeasonColumns.ID).Where(goqu.C(models.SeasonColumns.Status).Eq(models.SeasonsStatusActive))
teamQuery := dialect.From(models.TableNames.Teams).Select(models.TeamColumns.ID).Where(goqu.C(models.TeamColumns.SeasonID).Eq(seasonQuery))

=>

SELECT `id` FROM `teams` WHERE (`season_id` IN (SELECT `id` FROM `seasons` WHERE (`status` = 'active')))

[nic11]

Hey @parnic, I've also recently stumbled upon this bug. I'll make the PR with a fix soon :) In short, In considers a subquery to be a list and that causes extra braces to be appended

[nic11]

@parnic I made a PR. But looks like replacing In with Eq gives the desired behavour. Not sure why is this like so and if it is a really the desired behaviour to treat In(Select(...)) like a 'in the array of one select', but looks very quirky

It even has this in tests:

expressionTestCase{val: ident.Eq(ae), sql: `("a" IN (SELECT "id" FROM "test2"))`},

PS. Noticed that you told the same thing about Eq/In just when I finished writing this comment :D