I'm trying to write an OQL query that will return a count of all of the views for an application per day. I've tried multiple queries, but all i seem to get back is the count of all of the views. Not those for my given app. I've tried it with and without the Inner JOIN but everything i try seems to just do a count of all of the views. SELECT COUNT(*) as total, DATEPART(DAY,Gallery.View.createdDate) as totalDay, DATEPART(MONTH,Gallery.View.createdDate) as totalMonth, DATEPART(YEAR,Gallery.View.createdDate) as totalYear FROM Gallery.View INNER JOIN Gallery.View/Gallery.Favourite_Showcase/Gallery.Showcase WHERE Gallery.Showcase/AppID = $Showcase GROUP BY DATEPART(DAY,Gallery.View.createdDate), DATEPART(MONTH,Gallery.View.createdDate), DATEPART(YEAR,Gallery.View.createdDate)
Check out the console for the SQL that our beloved MBS comes up with. To do so, in the Modeler's view 'Console', click on Advanced->Loglevels and set the log level of ConnectionBus_Retrieve and ConnectionBus_Queries to 'Trace'.
Let us know what you find.
𝕿𝖎𝖒 𝖛𝖆𝖓 𝕾𝖙𝖊𝖊𝖓𝖇𝖊𝖗𝖌𝖊𝖓
If all you need is a count, just do an XPath retrieve followed by an aggregate list (Count) action. Mendix translates this into an SQL Count query.
You have a really weird WHERE clause. Looking at the syntax, it seems like you are comparing an attribute to an identifier. When I type something similar in Mendix, I get an error: