Hi,
is there a way to create a view entity that have result like this sql query?
SELECT a.status_id, COUNT(a) FROM agreement a GROUP BY a.status_id
result should have status name and count of each status.
thanks.
If you mean is there a way to create an entry in the database of a view entity, then no there currently isn’t.
I don’t know of any good reason why it’s not created except that no one has implemented it yet.
The way to get around this is to just change the data in the entities that have changed using the normal <service-call name="create#ExampleEntity... way.
There is a way to run sql like how the SqlScriptRunner screen does it. However there is no sql translator that would allow you to write it in one dialect and translate it into another so if you change your database later, then you may have to fix expected sql problems
1 Like
No, I want to create a view Entity that have same result of query.
first column status name and second count of status!
I created this view entity but result is not correct.
<view-entity entity-name="WikiSpaceAndStatus" package="moqui.resource.wiki">
<member-entity entity-alias="WKSP" entity-name="moqui.resource.wiki.WikiSpace"/>
<member-entity entity-alias="StudyCenter" entity-name="moqui.resource.wiki.WikiSpace" join-from-alias="WKSP" >
<key-map field-name="wikiSpaceId"/>
<entity-condition>
<econdition entity-alias="StudyCenter" field-name="wikiStatusId" value="IsWikiStudyCenter" />
</entity-condition>
</member-entity>
<member-entity entity-alias="HolyDefence" entity-name="moqui.resource.wiki.WikiSpace" join-from-alias="WKSP" >
<key-map field-name="wikiSpaceId"/>
<entity-condition >
<econdition entity-alias="HolyDefence" field-name="wikiStatusId" value="IsWikiHolyDefence" />
</entity-condition>
</member-entity>
<member-entity entity-alias="Cancelled" entity-name="moqui.resource.wiki.WikiSpace" join-from-alias="WKSP" >
<key-map field-name="wikiSpaceId"/>
<entity-condition >
<econdition entity-alias="Cancelled" field-name="wikiStatusId" value="IsWikiCancelled" />
</entity-condition>
</member-entity>
<member-entity entity-alias="Security" entity-name="moqui.resource.wiki.WikiSpace" join-from-alias="WKSP" >
<key-map field-name="wikiSpaceId"/>
<entity-condition >
<econdition entity-alias="Security" field-name="wikiStatusId" value="IsWikiSecurity" />
</entity-condition>
</member-entity>
<member-entity entity-alias="Correction" entity-name="moqui.resource.wiki.WikiSpace" join-from-alias="WKSP" >
<key-map field-name="wikiSpaceId"/>
<entity-condition >
<econdition entity-alias="Correction" field-name="wikiStatusId" value="IsWikiCorrection" />
</entity-condition>
</member-entity>
<member-entity entity-alias="Resistance" entity-name="moqui.resource.wiki.WikiSpace" join-from-alias="WKSP" >
<key-map field-name="wikiSpaceId"/>
<entity-condition >
<econdition entity-alias="Resistance" field-name="wikiStatusId" value="IsWikiResistance" />
</entity-condition>
</member-entity>
<member-entity entity-alias="Center" entity-name="moqui.resource.wiki.WikiSpace" join-from-alias="WKSP" >
<key-map field-name="wikiSpaceId"/>
<entity-condition >
<econdition entity-alias="Center" field-name="wikiStatusId" value="IsWikiCenter" />
</entity-condition>
</member-entity>
<member-entity entity-alias="Notice" entity-name="moqui.resource.wiki.WikiSpace" join-from-alias="WKSP" >
<key-map field-name="wikiSpaceId"/>
<entity-condition >
<econdition entity-alias="Notice" field-name="wikiStatusId" value="IsWikiNotice" />
</entity-condition>
</member-entity>
<member-entity entity-alias="Completed" entity-name="moqui.resource.wiki.WikiSpace" join-from-alias="WKSP" >
<key-map field-name="wikiSpaceId"/>
<entity-condition >
<econdition entity-alias="Completed" field-name="wikiStatusId" value="IsWikiCompleted" />
</entity-condition>
</member-entity>
<member-entity entity-alias="Delete" entity-name="moqui.resource.wiki.WikiSpace" join-from-alias="WKSP" >
<key-map field-name="wikiSpaceId"/>
<entity-condition >
<econdition entity-alias="Delete" field-name="wikiStatusId" value="IsWikiDelete" />
</entity-condition>
</member-entity>
<alias entity-alias="StudyCenter" name="wikiStudyCenter" field="wikiStatusId" function="count" is-aggregate="true"/>
<alias entity-alias="HolyDefence" name="wikiHolyDefenceWorkGroup" field="wikiStatusId" function="count" is-aggregate="true"/>
<alias entity-alias="Cancelled" name="wikiCancelled" field="wikiStatusId" function="count" is-aggregate="true"/>
<alias entity-alias="Security" name="security" field="wikiStatusId" function="count" is-aggregate="true"/>
<alias entity-alias="Correction" name="wikiCorrection" field="wikiStatusId" function="count" is-aggregate="true"/>
<alias entity-alias="Resistance" name="wikiResistance" field="wikiStatusId" function="count" is-aggregate="true"/>
<alias entity-alias="Center" name="wikiCenter" field="wikiStatusId" function="count" is-aggregate="true"/>
<alias entity-alias="Notice" name="wikiNotice" field="wikiStatusId" function="count" is-aggregate="true"/>
<alias entity-alias="Completed" name="wikiCompleted" field="wikiStatusId" function="count" is-aggregate="true"/>
<alias entity-alias="Delete" name="wikiDelete" field="wikiStatusId" function="count" is-aggregate="true"/>
</view-entity>
i want to view entity have result like this query
select wiki_status_id, count(*) from wiki_space group by wiki_status_id
Sorry I misunderstood you.
I think you’re looking for something like this:
<view-entity entity-name="WikiSpaceStatusCount" package="mantle.order">
<member-entity entity-alias="WS" entity-name="moqui.resource.wiki.WikiSpace"/>
<alias name="wikiStatusId" entity-alias="WS"/>
<alias name="wikiStatusCount" entity-alias="WS" field="wikiStatusId" function="count"/>
</view-entity>
Then if you do an entity-find for wiki and specify a wikiStatusId it’ll give you the count of wikiStatusIds.
If you include the wikiSpaceId in the output, then it’ll just count each wikiStatusId as 1 because it counts the number of separate entities with the same result and including wikiSpaceId will always have a separate result.
1 Like
Well Done. Thanks
1 Like