velocityHqlExamples (XWiki.org)

#includeMacros("DevGuide.velocityHqlExamplesMacro")

1 Velocity HQL query examples

XWiki allows user to access documents and objects with [hql>http://www.hibernate.org/hib_docs/reference/en/html/queryhql.html] queries in [Velocity>http://velocity.apache.org/engine/releases/velocity-1.5/user-guide.html] scripts.

#toc("" "" "")

1.1 Public API (searchDocuments)

10

11

#info("With this API the query consist in the WHERE condition.\\

12

Any user with edit rights can use this API (as in write a script using it).\\

13

Any user with view rights can view the result of such a query.")

14

15

You can execute queries as follows:

16

{code}

17

#set($query="where doc.creator='XWiki.VincentMassol'")

18

#set($results = $xwiki.searchDocuments($query, 5, 0))

19

#foreach ($item in $results)

* $item

#end

{code}

1.1.1 Simple Query

#set($hql="where doc.creator='XWiki.VincentMassol'")

26

#displayQuery($hql false)

27

28

1.1.1 Ordered Query

29

#set($hql="where doc.creator='XWiki.VincentMassol' order by doc.date asc")

30

#displayQuery($hql false)

31

32

1.1.1 Advanced Query (date & time)

33

34

Since there is no [standard way to calculate dates interval in HQL>http://opensource.atlassian.com/projects/hibernate/browse/HHH-2434] those queries are a bit unnatural.

35

36

#set($hql="where year(doc.date) = year(current_date()) and month(doc.date) = month(current_date()) and day(doc.date) = day(current_date()) and hour(doc.date) > (hour(current_time()) - 1) order by doc.date desc")

37

#displayQuery($hql false)

38

39

{code}

40

Other examples, documents modified :

41

42

during current day : "where year(doc.date) = year(current_date()) and month(doc.date) = month(current_date()) and day(doc.date) > (day(current_date()) - 1) order by doc.date desc"

43

during current week : "where year(doc.date) = year(current_date()) and month(doc.date) = month(current_date()) and day(doc.date) > (day(current_date()) - 7) order by doc.date desc"

44

during current month : "where year(doc.date) = year(current_date()) and month(doc.date) > (month(current_date()) - 1) order by doc.date desc"

{code}

1.1 Privileged API (search : Documents, Objects, Properties, etc)

49

50

#warning("Calls to te privileged API are only executed when the calling page has been saved by an Admin. \\

51

The reason is that search can be used to send any HQL command like update, delete, etc.")

52

53

You can execute queries as follows:

54

{code}

55

#set($query="select doc.name from XWikiDocument doc")

56

#set($results = $xwiki.search($query, 5, 0))

57

#foreach ($item in $results)

* $item

#end

{code}

1.1.1 Simply Query

#set($hql="select doc.name from XWikiDocument doc")

64

#displayQuery($hql true)

1.1.1 Count Query

{code}

#set($query="select count(doc) from XWikiDocument doc")

70

#set($results = $xwiki.search($query))

71

## $xwiki.search returning a list, we get its first element

72

$query result : $results.get(0)

73

{code}

74

75

#set($query="select count(doc) from XWikiDocument doc")

76

#set($results = $xwiki.search($query))

77

$query results : $results.get(0)

78

79

80

1.1.1 Simply Query with multiple fields

81

82

{code}

83

#set($results=$xwiki.search("select doc.name, doc.date from XWikiDocument doc", 5, 0))

84

#foreach ($row in $results)

85

#foreach ($col in $row)

86

#if ($velocityCount==1)

87

#set($docName=$col)

88

#elseif ($velocityCount==2)

#set($docDate=$col)

#end

#end

$docName : $docDate

#end

{code}

#set($hql="select doc.name, doc.date from XWikiDocument doc")

97

#displayQuery($hql true)

98

99

100

1.1.1 Getting objects of a specific class

101

#set($hql="select obj.name from BaseObject obj where obj.className='XWiki.XWikiUsers'")

102

#displayQuery($hql true)

103

104

105

1.1.1 Getting objects' properties

106

#set($hql="select obj.name, prop.value from BaseObject obj, StringProperty prop where obj.className='XWiki.XWikiUsers' and prop.id.id=obj.id and prop.name='first_name'")

107

#displayQuery($hql true)

108

109

110

1.1.1 Getting documents where objects' properties equals some value

111

#set($hql="select doc.fullName from XWikiDocument doc, BaseObject obj, StringProperty prop where doc.fullName=obj.name and obj.className='XWiki.XWikiUsers' and prop.id.id=obj.id and prop.name='first_name' and prop.value='Jean-Vincent'")

112

#displayQuery($hql true)

113

114

1.1 Non-exhaustive list of queryable object fields

115

116

#macro(exval $value)Example of value : ~~$value~~#end

117

118

1.1.1 XWikiDocument

119