[xwiki-users] using XWQL with additional search criterias by dates
Hi! Maybe somebody can help me. I need to select some docs with own class. Together I need 3 filters: 1) by class: only "myclass" 2) by property value: where "myproperty" = "myvalue" 3) by date: where doc date = current date - 5 days I created xwql script with 2 creterias and it's working well: select obj.name from Document doc, doc.object(mySpace.myClass) as obj where obj.myProperty like 'myValue' order by obj.name How need to add one more criteria by date? Thanks beforehand! Eugen Colesnicov -- View this message in context: http://xwiki.475771.n2.nabble.com/using-XWQL-with-additional-search-criteria... Sent from the XWiki- Users mailing list archive at Nabble.com.
Hi! Eugen Colesnicov wrote:
Hi! Maybe somebody can help me.
I need to select some docs with own class. Together I need 3 filters: 1) by class: only "myclass" 2) by property value: where "myproperty" = "myvalue" 3) by date: where doc date = current date - 5 days I created xwql script with 2 creterias and it's working well:
select obj.name from Document doc, doc.object(mySpace.myClass) as obj where obj.myProperty like 'myValue' order by obj.name
How need to add one more criteria by date?
This query is working fine in a XE 1.3.8295 installation, but I'm using HQL though. #set($query = ", BaseObject as obj, StringProperty as type, StringProperty as withdrawal where doc.fullName = obj.name and obj.className='Users.PdrUserClass' and obj.id=type.id.id and type.id.name='Type' and obj.id=withdrawal.id.id and withdrawal.id.name='Withdrawal' and (type.value like '%lonxa%' or type.value like '%xest%') and withdrawal.value ='non' and year(doc.date) = '2010' order by doc.fullName asc") HTH, Ricardo -- Ricardo Rodríguez CTO eBioTIC. Life Sciences, Data Modeling and Information Management Systems
Thanks, Ricardo! But for me your example is not enough. I need to filter not by fixed date, but for dynamic criteria - current date - 5 days ... How is it possible? Maybe I should do it by 2 steps: 1) set target date (current date - 5 days) as value, 2) use this target date (fixed value) as a filter criteria. Ok, but for this - how is it possible to define my target date = current date - 5 days? Thanks beforehand Eugen Colesnicov -- View this message in context: http://xwiki.475771.n2.nabble.com/using-XWQL-with-additional-search-criteria... Sent from the XWiki- Users mailing list archive at Nabble.com.
Hi! Eugen Colesnicov wrote:
Thanks, Ricardo! But for me your example is not enough.
I need to filter not by fixed date, but for dynamic criteria - current date - 5 days ... How is it possible?
Well, this is not hard to do... after the examples shown here... http://platform.xwiki.org/xwiki/bin/view/DevGuide/velocityHqlExamples
Maybe I should do it by 2 steps: 1) set target date (current date - 5 days) as value, 2) use this target date (fixed value) as a filter criteria. Ok, but for this - how is it possible to define my target date = current date - 5 days?
This simple script works for me here... #set($query = ", BaseObject as obj, StringProperty as firstName, StringProperty as lastName where doc.fullName = obj.name and obj.className='XWiki.XWikiUsers' and obj.id=firstName.id.id and firstName.id.name='first_name' and obj.id=lastName.id.id and lastName.id.name='last_name' and (firstName.value like '%Ricardo%' or firstName.value like '%Mar%') and lastName.value like '%Rodr%' and year(doc.date) = year(current_date()) and month(doc.date) = month(current_date()) and day(doc.date) > (day(current_date()) - 100) order by doc.fullName asc") #set($results=$xwiki.searchDocuments($query)) #foreach($item in $results) #set($itemdoc = $xwiki.getDocument($item)) [$itemdoc.display('first_name')>$item]<br/> #end HTH! -- Ricardo Rodríguez CTO eBioTIC. Life Sciences, Data Modeling and Information Management Systems
I checked this example and your script - it is not correct, because you compare separately year, month and day of document with current date - year, month and day-100. But if, for example now is 02 january, your script give uncorrect result, becase I also should change year and month (and year), then I want to get date BEFORE 100 days of 02 january. Need to make arithmetical operations with dates and these operations should consider also possible transitions of month and year when day changed ... In any case - thanks! I found the answer - need to use special velocity object $datetool Example: #set ($d = 5) - days interval which I need #set ($ms = $d * 24 * 60 * 60 * 1000) - Calculating internal in Milliseconds #set ($calendar = $datetool.getCalendar()) #set ($currentTime = $calendar.getTimeInMillis()) #set ($targetTime = $currentTime - $ms) #set ($void = $calendar.setTimeInMillis($targetTime)) #set ($targetDate = $calendar.getTime()) #set ($targetYear = $datetool.getYear($targetDate)) :get year of a date = currentdate - 5 days #set ($targetMonth = $datetool.getMonth($targetDate) + 1) :get month of a date = currentdate - 5 days #set ($targetDay = $datetool.getDay($targetDate)) :get day of a date = currentdate - 5 days and after this, in a hql searching, I can compare this year, month, and day with documents year, month and day ... -- View this message in context: http://xwiki.475771.n2.nabble.com/using-XWQL-with-additional-search-criteria... Sent from the XWiki- Users mailing list archive at Nabble.com.
On 10/23/2010 10:45 PM, Eugen Colesnicov wrote:
I checked this example and your script - it is not correct, because you compare separately year, month and day of document with current date - year, month and day-100. But if, for example now is 02 january, your script give uncorrect result, becase I also should change year and month (and year), then I want to get date BEFORE 100 days of 02 january.
Need to make arithmetical operations with dates and these operations should consider also possible transitions of month and year when day changed ... In any case - thanks!
I found the answer - need to use special velocity object $datetool
You can use the jodatime plugin, which makes it easier to work with dates. See https://svn.xwiki.org/svnroot/xwiki/platform/xwiki-plugins/trunk/jodatime/ Accessed with $xwiki.jodatime Also, you shouldn't use computed date segments as constants inside a query, but do a parameterized query and compare dates. Schematic example: $xwiki.searchDocuments(", DateProperty someDateProp where someDateProp.value > ?", [$computedStartDate])
Example: #set ($d = 5) - days interval which I need #set ($ms = $d * 24 * 60 * 60 * 1000) - Calculating internal in Milliseconds
#set ($calendar = $datetool.getCalendar()) #set ($currentTime = $calendar.getTimeInMillis()) #set ($targetTime = $currentTime - $ms) #set ($void = $calendar.setTimeInMillis($targetTime)) #set ($targetDate = $calendar.getTime()) #set ($targetYear = $datetool.getYear($targetDate)) :get year of a date = currentdate - 5 days #set ($targetMonth = $datetool.getMonth($targetDate) + 1) :get month of a date = currentdate - 5 days #set ($targetDay = $datetool.getDay($targetDate)) :get day of a date = currentdate - 5 days
and after this, in a hql searching, I can compare this year, month, and day with documents year, month and day ...
-- Sergiu Dumitriu http://purl.org/net/sergiu/
Hi Sergiu! I played before with jodatime. It have some problems ... For example, I tried to use example script from plugin home page (it can be usefull in my situation) and this script doesn't working: #set($formatter = $xwiki.jodatime.getDateTimeFormatterForPattern('yyyy.MM.dd')) #set($myBirthday = $formatter.parseDateTime('2007.11.25')) #set($daysLeft = $myBirthday.minus($now.millis)) $daysLeft.getDayOfYear() days left till my birthday! Also joda time is badly described - for all these reasons I used datetool. About query ... Better for me - XWQL, but all my tryings to use any dates in XWQL gives some errors (most of all - java errors in a translation from XWQL to hql ...). I am not a professional in a XWQL and HQL (I am not sure - this is my error or bug), and Ricardo gave working example of using HQL - for this reason I used ready query example. If you can give working example of using dates in a XWQL - will be great! Also I tried examples from here: http://dev.xwiki.org/xwiki/bin/view/DesignArchive/XWikiQueryLanguageSpecific... (second script) - but give me error. -- View this message in context: http://xwiki.475771.n2.nabble.com/using-XWQL-with-additional-search-criteria... Sent from the XWiki- Users mailing list archive at Nabble.com.
Hi Eugen, Eugen Colesnicov wrote:
Hi Sergiu!
I played before with jodatime. It have some problems ... For example, I tried to use example script from plugin home page (it can be usefull in my situation) and this script doesn't working: #set($formatter = $xwiki.jodatime.getDateTimeFormatterForPattern('yyyy.MM.dd')) #set($myBirthday = $formatter.parseDateTime('2007.11.25')) #set($daysLeft = $myBirthday.minus($now.millis)) $daysLeft.getDayOfYear() days left till my birthday!
Also joda time is badly described - for all these reasons I used datetool.
Have you tried to use Velocity ComparisonDateTool? I'm able to use DateTool like you, but I don't know how to call methods from ComparisonDateTool. Are they available within Xwiki? Thanks! http://ebiotic.net/bin/ICT/Date
About query ... Better for me - XWQL, but all my tryings to use any dates in XWQL gives some errors (most of all - java errors in a translation from XWQL to hql ...). I am not a professional in a XWQL and HQL (I am not sure - this is my error or bug), and Ricardo gave working example of using HQL - for this reason I used ready query example.
If you can give working example of using dates in a XWQL - will be great! Also I tried examples from here: http://dev.xwiki.org/xwiki/bin/view/DesignArchive/XWikiQueryLanguageSpecific... (second script) - but give me error.
-- Ricardo Rodríguez CTO eBioTIC. Life Sciences, Data Modeling and Information Management Systems
Hi! Eugen Colesnicov wrote:
I checked this example and your script - it is not correct, because you compare separately year, month and day of document with current date - year, month and day-100. But if, for example now is 02 january, your script give uncorrect result, becase I also should change year and month (and year), then I want to get date BEFORE 100 days of 02 january.
You are completely right: it is clearly stated in the example. They talked only about "current" day, week and month. Sorry about being "too fast" and don't think enough before posting!
Need to make arithmetical operations with dates and these operations should consider also possible transitions of month and year when day changed ... In any case - thanks!
I found the answer - need to use special velocity object $datetool Example: #set ($d = 5) - days interval which I need #set ($ms = $d * 24 * 60 * 60 * 1000) - Calculating internal in Milliseconds
#set ($calendar = $datetool.getCalendar()) #set ($currentTime = $calendar.getTimeInMillis()) #set ($targetTime = $currentTime - $ms) #set ($void = $calendar.setTimeInMillis($targetTime)) #set ($targetDate = $calendar.getTime()) #set ($targetYear = $datetool.getYear($targetDate)) :get year of a date = currentdate - 5 days #set ($targetMonth = $datetool.getMonth($targetDate) + 1) :get month of a date = currentdate - 5 days #set ($targetDay = $datetool.getDay($targetDate)) :get day of a date = currentdate - 5 days
and after this, in a hql searching, I can compare this year, month, and day with documents year, month and day ...
-- Ricardo Rodríguez CTO eBioTIC. Life Sciences, Data Modeling and Information Management Systems
Hi [Ricardo Rodriguez] eBioTIC. wrote:
Hi Eugen,
Eugen Colesnicov wrote:
Hi Sergiu!
I played before with jodatime. It have some problems ... For example, I tried to use example script from plugin home page (it can be usefull in my situation) and this script doesn't working: #set($formatter = $xwiki.jodatime.getDateTimeFormatterForPattern('yyyy.MM.dd')) #set($myBirthday = $formatter.parseDateTime('2007.11.25')) #set($daysLeft = $myBirthday.minus($now.millis)) $daysLeft.getDayOfYear() days left till my birthday!
Also joda time is badly described - for all these reasons I used datetool.
Have you tried to use Velocity ComparisonDateTool? I'm able to use DateTool like you, but I don't know how to call methods from ComparisonDateTool. Are they available within Xwiki? Thanks!
As you said: reading attentively the documentation is required to get this working! Following http://platform.xwiki.org/xwiki/bin/view/DevGuide/Scripting#HVelocitySpecifi... I modified xwiki.properties to include... http://velocity.apache.org/tools/releases/1.4/javadoc/org/apache/velocity/to... by adding... velocity.tools = comparisondatetool = org.apache.velocity.tools.generic.ComparisonDateTool It seems to me that the subclass is not available as the current date is printed if I use $comparisondatetool, but I'm not able yet to use its methods. (I've also checked that the required Velocity Tools library release is available ./WEB-INF/lib/velocity-tools-1.4.jar ) http://ebiotic.net/bin/ICT/Date Please, could you/anybody give an example? Thanks!
About query ... Better for me - XWQL, but all my tryings to use any dates in XWQL gives some errors (most of all - java errors in a translation from XWQL to hql ...). I am not a professional in a XWQL and HQL (I am not sure - this is my error or bug), and Ricardo gave working example of using HQL - for this reason I used ready query example.
If you can give working example of using dates in a XWQL - will be great! Also I tried examples from here: http://dev.xwiki.org/xwiki/bin/view/DesignArchive/XWikiQueryLanguageSpecific... (second script) - but give me error.
Cheers, -- Ricardo Rodríguez CTO eBioTIC. Life Sciences, Data Modeling and Information Management Systems
participants (3)
-
[Ricardo Rodriguez] eBioTIC. -
Eugen Colesnicov -
Sergiu Dumitriu