HI,
I want to setup clause FROM dynamically, because collection name could change,
And I need to have FROM parameter aliased for unnest purposes.
so I came with this solution, with I don`t like.
SELECT tt.translation
FROM "+ collection +"data
UNNEST data.values ff
UNNEST ff.translation.translations tt
WHERE ff.code = $code
AND data.fieldName = $fieldName
AND tt.locale = $locale "
/ collection is param is set up in application parameters/
Is there a way do do this better?
No, you can’t pass the collection as a parameter - the statement can’t be prepared (against a collection) without knowing the collection up front. (If you pass the FROM argument as a parameter then it is a FROM <value> clause and the name passed will be interpreted as a string value, not a collection name.)
What you have is a typical design pattern - generate the SQL text according to application needs then prepare that.
HTH.
In addition to @dh comment main reason is during prepare statement it doesn’t know collection and prepare one need to pick index selection and generate optimize plan. That is reason this restriction.
Very very few cases in 7.0+ non-join case if query has USE KEYS it can have parameter must be aliased.
Example: SELECT t.* FROM $coll AS t USE KEYS $keys WHERE …
Is there a way do do this better?
Yes. Slice your data by Scope instead of by Collection. Each scope will have the same collection(s).
The same query can be executed against different scopes.
Thank you all for quick and precise response.