[xwiki-devs] Question about User Query with Solr
Hi devs, Is there a better way with solr query to fast get all the users (last modified after the date "2016-01-14T15:44:02Z") of the group XWikiAdminGroup ? For this group, http://www.xwiki.org/xwiki/rest/wikis/xwiki/spaces/XWiki/pages/XWikiAdminGro... now I just query with the following method, it's too slow. 1. get all user ids from the group http://www.xwiki.org/xwiki/rest/wikis/xwiki/spaces/XWiki/pages/XWikiAdminGro... <objects> <objectSummary> <headline>XWiki.LudovicDubost</headline> -> (id = XWiki.LudovicDubost) </objectSummary> ... ... </objects> 2. one by one check the user's modified time, if after the date, then add the id "XWiki.LudovicDubost" to the array list. For example: http://www.xwiki.org/xwiki/rest/wikis/xwiki/spaces/XWiki/pages/LudovicDubost <modified>2014-07-28T03:04:44+02:00</modified> but it takes up lots of time for checking the modified date one by one. 3. from the list above getting from the step2, we just get all the ids, but no detail information, we also should request server again to get the user's detail properties one by one! it also costs much time. If the group has 1000 users, step1 needs 1 times http request, step3 needs 1000 times http requests, step3 needs <=1000 times, all steps need 1001-2001 times requests and xml parse. Maybe the step2 is not necessary so that we can reduce half of the time. Therefore, is there a better way to query the last modified users? By the way, now I query all the users modified after the date "2016-01-14T15:44:02Z" in the wikis/xwiki. like this: wiki:xwiki and object:XWiki.XWikiUsers and date:[2016-01-14T15:44:02Z TO *] & number=10000 Query Example: http://www.xwiki.org/xwiki/rest/wikis/query?q=wiki:xwiki%20and%20object:XWiki.XWikiUsers%20and%20date:[2016-02-02T15:44:02Z%20%20TO%20*]&number=10000 <http://www.xwiki.org/xwiki/rest/wikis/query?q=wiki:xwiki%20and%20object:XWiki.XWikiUsers%20and%20date:[2016-02-02T15:44:02Z%20%20TO%20*]&number=10000> But I don't know how to query fast the group's users(last modified after the date). Could some one help me? Thanks, Fitz -- View this message in context: http://xwiki.475771.n2.nabble.com/Question-about-User-Query-with-Solr-tp7599... Sent from the XWiki- Dev mailing list archive at Nabble.com.
Thanks for your reply. On Tue, Jun 14, 2016 at 9:38 PM, Paul Libbrecht <[email protected]> wrote:
Are you not after solr's? Something such as [NOW-1MONTH TO *] ?
Yeah, I know. But I want to find the users which should be within a certain group like XWikiAdminGroup and should be last modified after the date like [2016-01-14T15:44:02Z TO *]. If finding all the users in the xwiki (not care about the group), I can use the date range queries, just like "wiki:xwiki and object:XWiki.XWikiUsers and date:[2016-01-14T15:44:02Z TO *]". http://www.xwiki.org/xwiki/rest/wikis/query?q=wiki:xwiki%20and%20object:XWiki.XWikiUsers%20and%20date:[2016-02-02T15:44:02Z%20%20TO%20*]&number=10000 Are there any other ideas ?
paul
fitz wrote:
But I don't know how to query fast the group's users(last modified after the date). Could some one help me?
_______________________________________________ devs mailing list [email protected] http://lists.xwiki.org/mailman/listinfo/devs
I only see joins here, so... HQL or XWQL or something such. They're not really there in SOLR (it's coming in some later versions apparently). paul
Fitz Lee <mailto:[email protected]> 14 June 2016 at 16:23 Thanks for your reply.
On Tue, Jun 14, 2016 at 9:38 PM, Paul Libbrecht <[email protected]> wrote:
Are you not after solr's? Something such as [NOW-1MONTH TO *] ?
Yeah, I know. But I want to find the users which should be within a certain group like XWikiAdminGroup and should be last modified after the date like [2016-01-14T15:44:02Z TO *]. If finding all the users in the xwiki (not care about the group), I can use the date range queries, just like "wiki:xwiki and object:XWiki.XWikiUsers and date:[2016-01-14T15:44:02Z TO *]". http://www.xwiki.org/xwiki/rest/wikis/query?q=wiki:xwiki%20and%20object:XWiki.XWikiUsers%20and%20date:[2016-02-02T15:44:02Z%20%20TO%20*]&number=10000
Are there any other ideas ?
paul
fitz wrote:
But I don't know how to query fast the group's users(last modified after the date). Could some one help me?
devs mailing list [email protected] http://lists.xwiki.org/mailman/listinfo/devs
_______________________________________________ devs mailing list [email protected] http://lists.xwiki.org/mailman/listinfo/devs Paul Libbrecht <mailto:[email protected]> 14 June 2016 at 15:38 Are you not after solr's date range queries? Something such as [NOW-1MONTH TO *] ?
paul
_______________________________________________ devs mailing list [email protected] http://lists.xwiki.org/mailman/listinfo/devs fitz <mailto:[email protected]> 14 June 2016 at 14:56 Hi devs,
Is there a better way with solr query to fast get all the users (last modified after the date "2016-01-14T15:44:02Z") of the group XWikiAdminGroup ? For this group, http://www.xwiki.org/xwiki/rest/wikis/xwiki/spaces/XWiki/pages/XWikiAdminGro...
now I just query with the following method, it's too slow.
1. get all user ids from the group http://www.xwiki.org/xwiki/rest/wikis/xwiki/spaces/XWiki/pages/XWikiAdminGro... <objects> <objectSummary> <headline>XWiki.LudovicDubost</headline> -> (id = XWiki.LudovicDubost) </objectSummary> ... ... </objects>
2. one by one check the user's modified time, if after the date, then add the id "XWiki.LudovicDubost" to the array list. For example: http://www.xwiki.org/xwiki/rest/wikis/xwiki/spaces/XWiki/pages/LudovicDubost <modified>2014-07-28T03:04:44+02:00</modified> but it takes up lots of time for checking the modified date one by one.
3. from the list above getting from the step2, we just get all the ids, but no detail information, we also should request server again to get the user's detail properties one by one! it also costs much time.
If the group has 1000 users, step1 needs 1 times http request, step3 needs 1000 times http requests, step3 needs <=1000 times, all steps need 1001-2001 times requests and xml parse. Maybe the step2 is not necessary so that we can reduce half of the time.
Therefore, is there a better way to query the last modified users?
By the way, now I query all the users modified after the date "2016-01-14T15:44:02Z" in the wikis/xwiki. like this:
wiki:xwiki and object:XWiki.XWikiUsers and date:[2016-01-14T15:44:02Z TO *] & number=10000 Query Example: http://www.xwiki.org/xwiki/rest/wikis/query?q=wiki:xwiki%20and%20object:XWiki.XWikiUsers%20and%20date:[2016-02-02T15:44:02Z%20%20TO%20*]&number=10000 <http://www.xwiki.org/xwiki/rest/wikis/query?q=wiki:xwiki%20and%20object:XWiki.XWikiUsers%20and%20date:[2016-02-02T15:44:02Z%20%20TO%20*]&number=10000>
But I don't know how to query fast the group's users(last modified after the date). Could some one help me?
Thanks, Fitz
-- View this message in context: http://xwiki.475771.n2.nabble.com/Question-about-User-Query-with-Solr-tp7599... Sent from the XWiki- Dev mailing list archive at Nabble.com. _______________________________________________ devs mailing list [email protected] http://lists.xwiki.org/mailman/listinfo/devs
Hi Fitz, The group membership information is stored on the group page while the user profile (last modification date) is on a separate page. This means you need to query information that is stored on two separate (database or index) "rows" (the row that corresponds to the group and the row that corresponds to the user). This requires performing a join, either on the database (using HQL or XWQL) or on the Solr index. The main difference between the two is that a database query targets a single wiki (so if you need the users from all the wikis then you need to iterate over all the wikis and perform the query on each of them) while a Solr query targets all the wikis (so you can get all the users from all the wikis in one single query). (A) Database Query Unfortunately the search REST resource doesn't support XWQL ( http://jira.xwiki.org/browse/XWIKI-13458 ) so you'll have to rely on HQL. For the record, the XWQL query would look like: from doc.object(XWiki.XWikiUsers) as userObj, Document as groupDoc, groupDoc.object(XWiki.XWikiGroups) as groupObj where groupObj.member = doc.fullName and groupDoc.fullName = 'XWiki.XWikiAdminGroup' and doc.date > :date The equivalent HQL query is a lot more complex. I'll let you figure it out. See http://extensions.xwiki.org/xwiki/bin/view/Extension/Query+Module . Once you have it you can use /wikis/{wikiName}/query?q={query}&type=hql (B) Solr Query In order to perform a (useful) join on the Solr index you need to be able to specify both the query (q) and the filter query (fq). Unfortunately the Solr REST resource doesn't support specifying the filter query so you can't use that. Fortunately there is a Solr service used by the search suggest which supports specifying the filter query. This is how you could build the URL from JavaScript (to give you an idea): new XWiki.Document('SuggestSolrService', 'XWiki').getURL('get', jQuery.param({ outputSyntax: 'plain', query: [ 'q={!join from=property.XWiki.XWikiGroups.member_string to=fullname}fullname:XWiki.XWikiAdminGroup', 'fq=date:[NOW/DAY TO *]' ].join('\n'), input: '*', nb: 8, })) This computes the URL to retrieve the users from the AdminGroup that have been modified today. The response is XML by default but you can get JSON with media=json in the query string. Hope this helps, Marius On Tue, Jun 14, 2016 at 3:56 PM, fitz <[email protected]> wrote:
Hi devs,
Is there a better way with solr query to fast get all the users (last modified after the date "2016-01-14T15:44:02Z") of the group XWikiAdminGroup ? For this group,
http://www.xwiki.org/xwiki/rest/wikis/xwiki/spaces/XWiki/pages/XWikiAdminGro...
now I just query with the following method, it's too slow.
1. get all user ids from the group
http://www.xwiki.org/xwiki/rest/wikis/xwiki/spaces/XWiki/pages/XWikiAdminGro... <objects> <objectSummary> <headline>XWiki.LudovicDubost</headline> -> (id = XWiki.LudovicDubost) </objectSummary> ... ... </objects>
2. one by one check the user's modified time, if after the date, then add the id "XWiki.LudovicDubost" to the array list. For example:
http://www.xwiki.org/xwiki/rest/wikis/xwiki/spaces/XWiki/pages/LudovicDubost <modified>2014-07-28T03:04:44+02:00</modified> but it takes up lots of time for checking the modified date one by one.
3. from the list above getting from the step2, we just get all the ids, but no detail information, we also should request server again to get the user's detail properties one by one! it also costs much time.
If the group has 1000 users, step1 needs 1 times http request, step3 needs 1000 times http requests, step3 needs <=1000 times, all steps need 1001-2001 times requests and xml parse. Maybe the step2 is not necessary so that we can reduce half of the time.
Therefore, is there a better way to query the last modified users?
By the way, now I query all the users modified after the date "2016-01-14T15:44:02Z" in the wikis/xwiki. like this:
wiki:xwiki and object:XWiki.XWikiUsers and date:[2016-01-14T15:44:02Z TO *] & number=10000 Query Example:
http://www.xwiki.org/xwiki/rest/wikis/query?q=wiki:xwiki%20and%20object:XWiki.XWikiUsers%20and%20date:[2016-02-02T15:44:02Z%20%20TO%20*]&number=10000 < http://www.xwiki.org/xwiki/rest/wikis/query?q=wiki:xwiki%20and%20object:XWiki.XWikiUsers%20and%20date:[2016-02-02T15:44:02Z%20%20TO%20*]&number=10000
But I don't know how to query fast the group's users(last modified after the date). Could some one help me?
Thanks, Fitz
-- View this message in context: http://xwiki.475771.n2.nabble.com/Question-about-User-Query-with-Solr-tp7599... Sent from the XWiki- Dev mailing list archive at Nabble.com. _______________________________________________ devs mailing list [email protected] http://lists.xwiki.org/mailman/listinfo/devs
A wonderful answer! Thank you very much ! It's clear to me now. I can use the HQL query to fast get the users for my android app :) Thanks, Fitz On Tue, Jun 14, 2016 at 11:13 PM, Marius Dumitru Florea < [email protected]> wrote:
Hi Fitz,
The group membership information is stored on the group page while the user profile (last modification date) is on a separate page. This means you need to query information that is stored on two separate (database or index) "rows" (the row that corresponds to the group and the row that corresponds to the user). This requires performing a join, either on the database (using HQL or XWQL) or on the Solr index. The main difference between the two is that a database query targets a single wiki (so if you need the users from all the wikis then you need to iterate over all the wikis and perform the query on each of them) while a Solr query targets all the wikis (so you can get all the users from all the wikis in one single query).
(A) Database Query
Unfortunately the search REST resource doesn't support XWQL ( http://jira.xwiki.org/browse/XWIKI-13458 ) so you'll have to rely on HQL. For the record, the XWQL query would look like:
from doc.object(XWiki.XWikiUsers) as userObj, Document as groupDoc, groupDoc.object(XWiki.XWikiGroups) as groupObj where groupObj.member = doc.fullName and groupDoc.fullName = 'XWiki.XWikiAdminGroup' and doc.date > :date
The equivalent HQL query is a lot more complex. I'll let you figure it out. See http://extensions.xwiki.org/xwiki/bin/view/Extension/Query+Module . Once you have it you can use
/wikis/{wikiName}/query?q={query}&type=hql
(B) Solr Query
In order to perform a (useful) join on the Solr index you need to be able to specify both the query (q) and the filter query (fq). Unfortunately the Solr REST resource doesn't support specifying the filter query so you can't use that. Fortunately there is a Solr service used by the search suggest which supports specifying the filter query. This is how you could build the URL from JavaScript (to give you an idea):
new XWiki.Document('SuggestSolrService', 'XWiki').getURL('get', jQuery.param({ outputSyntax: 'plain', query: [ 'q={!join from=property.XWiki.XWikiGroups.member_string to=fullname}fullname:XWiki.XWikiAdminGroup', 'fq=date:[NOW/DAY TO *]' ].join('\n'), input: '*', nb: 8, }))
This computes the URL to retrieve the users from the AdminGroup that have been modified today. The response is XML by default but you can get JSON with media=json in the query string.
Hope this helps, Marius
On Tue, Jun 14, 2016 at 3:56 PM, fitz <[email protected]> wrote:
Hi devs,
Is there a better way with solr query to fast get all the users (last modified after the date "2016-01-14T15:44:02Z") of the group XWikiAdminGroup ? For this group,
http://www.xwiki.org/xwiki/rest/wikis/xwiki/spaces/XWiki/pages/XWikiAdminGro...
now I just query with the following method, it's too slow.
1. get all user ids from the group
http://www.xwiki.org/xwiki/rest/wikis/xwiki/spaces/XWiki/pages/XWikiAdminGro...
<objects> <objectSummary> <headline>XWiki.LudovicDubost</headline> -> (id = XWiki.LudovicDubost) </objectSummary> ... ... </objects>
2. one by one check the user's modified time, if after the date, then add the id "XWiki.LudovicDubost" to the array list. For example:
http://www.xwiki.org/xwiki/rest/wikis/xwiki/spaces/XWiki/pages/LudovicDubost
<modified>2014-07-28T03:04:44+02:00</modified> but it takes up lots of time for checking the modified date one by one.
3. from the list above getting from the step2, we just get all the ids, but no detail information, we also should request server again to get the user's detail properties one by one! it also costs much time.
If the group has 1000 users, step1 needs 1 times http request, step3 needs 1000 times http requests, step3 needs <=1000 times, all steps need 1001-2001 times requests and xml parse. Maybe the step2 is not necessary so that we can reduce half of the time.
Therefore, is there a better way to query the last modified users?
By the way, now I query all the users modified after the date "2016-01-14T15:44:02Z" in the wikis/xwiki. like this:
wiki:xwiki and object:XWiki.XWikiUsers and date:[2016-01-14T15:44:02Z TO *] & number=10000 Query Example:
<
But I don't know how to query fast the group's users(last modified after the date). Could some one help me?
Thanks, Fitz
-- View this message in context:
http://xwiki.475771.n2.nabble.com/Question-about-User-Query-with-Solr-tp7599...
Sent from the XWiki- Dev mailing list archive at Nabble.com. _______________________________________________ devs mailing list [email protected] http://lists.xwiki.org/mailman/listinfo/devs
_______________________________________________ devs mailing list [email protected] http://lists.xwiki.org/mailman/listinfo/devs
Fitz Lee wrote:
It's clear to me now. I can use the HQL query to fast get the users for my android app :) Beware of HQL, it translates to SQL pretty easily but then... there are easy situations where you create too big queries which can hog big platforms (as any write will be locked while the query is still being executed).
paul
participants (4)
-
fitz -
Fitz Lee
-
Marius Dumitru Florea -
Paul Libbrecht