SQLBoiler select from another table in `AndIn`

Question

I am trying to follow the examples in sqlboiler (https://github.com/volatiletech/sqlboiler). But, I can't find a way to add another table in AndIn clause, since any Where leads to return two values.

users, err := models.Users(
  Select("id", "name"),
  Where("age > ?", 30),
  AndIn("c.kind in ?", "visa", "mastercard"),
).All(ctx, db)

In this example, if, we could get a filter a way to fetch values from another table, it'd be equivalent to SQL.

One way to get this done is to obtain the values separately and then feed it in as a variable.

Thanks!

These sorts of problems are one of the main reasons not to use an ORM. ORMs are ostensibly designed to make life easier. When you start banging your head against the wall because it's difficult to make the ORM do the right thing, it's obviously notmaking life easier any more. — Jonathan Hall, Jun 09 '19 at 22:16
Yes, but then if the code changes require db migration, then ORMs are convenient. Standard SQL with break a lot of things in production code. Besides, don't really have an option to not use ORM. — Coder, Jun 09 '19 at 22:21
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/194673/discussion-between-vikas-goel-and-flimzy). — Coder, Jun 09 '19 at 22:21
If that's your view, then use an ORM. In my view, an ORM is never convieneint. — Jonathan Hall, Jun 09 '19 at 22:21

score 0 · Answer 1 · answered Jun 11 '19 at 23:15

You can use arbitrary SQL in Where.

users, err := models.Users(
  Select("id", "name"),
  Where("age > ?", 30),
  Where("c.kind IN (select kind from your_other_table)"),
).All(ctx, db)

WhereIn, AndIn are just handy functions to autogenerate a list of a variable amount of placeholders. WhereIn("x in ?", "a", "b") is the same as Where("x in (?, ?)", "a", "b"). If you don't need that, just use regular Where.

SQLBoiler select from another table in `AndIn`

1 Answers1