It would be great if we could do sub queries (so using the IN() construct in MySQL) like this in one call to MySQL:
I know we can do it in two database queries, but this creates more traffic to the database server, so the ability to do it in one via the menu system would be great! 
Related to:
Isn’t that already possible with custom queries?
Yes, but you and George keep asking me to do as much as possible using the menus, so I am telling you when I cannot! 
I was thinking, a solution to this might be to have a previous database query that is disabled, but allow that to be selected as the subquery of what follows…
Well the custom queries component is exactly created for such exotic cases.
We are referring to expressions created on the page by the UI, not to building custom queries.
But Teodor, a sub query is not exotic if you are building an app, it is a pretty basic thing you find yourself doing a lot of the time.
App building is very different to database driven web site building…
So that is why you have the option to write your custom queries in the specially created custom query builder component!
Indeed… and I am trying to help out in the community by giving ideas that could make this easier by expanding the capabilities of the menu system!
Avoid subqueries as much as possible unless you really know how to optimise them. Eventually you will end up hindering the performance of your app because you used a subquery where should have not
That’s a very bold statement Jon!
In my example I am finding, for example, 3 price amounts where the id values are taken from 3 items ordered on a booking form.
Can you explain what is going to hinder performance doing that?
Or maybe that is one of the scenarios you see as okay…
… so I’d be interested to see some scenarios you think will hit performance!
Not talking specifically about your example.
The IN operator is slow in general but the result will depend on how you write your query and how you design your database.
Is there a faster way to do what I am doing then?
No idea. I don’t know your DB design
As alternatives you have JOIN and EXISTS.
SQl server example:
Mysql EXISTS strategy:
https://dev.mysql.com/doc/refman/5.6/en/subquery-optimization-with-exists.html
All in all, people tend to resort quickly to IN because it seems more intuitive. And while in some contexts that is OK. In others it’s a bad choice performance wise.
The problem is that sometimes JOIN and EXISTS won’t yield the same results as IN. For that to happen, you can sometimes rewrite the query, others you can redesign the database and others you have to stick with IN.
Interesting… I’ll take a read!
Thanks Jon.
Anytime.
I’m generally in favour on any features which make development easier but I think once you go beyond standard queries (and the query builder already has more functionality than most automated builders offer) you are better off just having a custom queries option. This means there is no limit at all. Once you venture down the road of sub-queries, where do you stop?