Is the same app in edit mode hitting the same db? And if not is the public db missing the group by clause in the query that runs successfully when in edit mode? Are there any restrictions on and if there are two dbs?
The app is using the same db in both modes not two different dbs. What do you mean by "public db" should I change any settings in db to make it accessible for public apps?
Without being able to access your app I can't really say what else to look for.... @Tess@Kabirdas@joeBumbaca may be able to assist as they are on the Retool team. Sorry I cannot help anymore.
"... does this API query have some sort of dynamic authentication settings? On public links, we actually collect no user info, which means any kind of authentication would be challenging. All authentication is associated with specific Retool user accounts, and since public app users are fully anonymous there is no user to work with that system."
What type of resource are we using for this query?
@Paulo I understand what you guys are suspecting. The api doesn't use any user information (like getting username or userid from token, auth headers etc.) to run the query.
select
po.id,
po.name,
po."displayName",
po.description,
po."recurringPrice",
po."nonRecurringPrice",
pi."imageObjectId",
json_agg(json_build_object('characteristic', characteristic, 'isPresent', "isPresent") order by "isPresent" desc) as characteristics,
SUM(CASE WHEN psc."isPresent" = 'Yes' THEN 1 ELSE 0 END) AS featureCount
from
catalog_management_product_offering po
LEFT JOIN product_specific_characteristics psc ON po.id = psc."productId"
LEFT JOIN sdwan_product_images pi ON po.name = pi."productName"
GROUP BY po.id, pi."imageObjectId", po.name
order by featureCount
This is the entire query. As you can see, there are no variables in it, no user variables fetched from token, auth headers etc.
From looking at the SQL error you are getting, I wonder if we may have enabled Releases for the app. Perhaps the public app is showing an older version, where the query was different.
On the first screenshot, it looks like we may have not clicked "Save & Run" on the "sdwanProducts" query. Try saving it, running it to make sure everything is still working, create a new release, and check the public app after a minute or two. If this does not solve the issue, come to Office Hours tomorrow at 11am PST. We are happy to take a closer look at what is going on.
You are welcome! I'm glad this solved your issue.
By design, queries are not save automatically to prevent accidental changes. If we create a release before clicking that button, the release does not included the updated query.