You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@jackrabbit.apache.org by Omid Milani <om...@gmail.com> on 2010/07/19 16:27:32 UTC

count function in query?

Hi,

I'm implementing a tag cloud in JCR. I wanted to write a query to
return tags and number of articles tagged with each, similar to RDBMS
query
  select tag, count(*) as count from article_tag and tag where
article_tag.tag=tag.id group by tag order by count
I expected something like this to do it in jcr
  /tags/tag( node() |
count(//article/tag[@tag-link=node()/@jcr:uuid])) order by count(...)
It doesn't. I couldn't find a way to do it with sql2 and qom either,
seems there's no count function defined in any of them.

Now, for tag cloud, I did without that query, by keeping count of
links in tag's node for each article tagged. But this seems an
important feature. Is there some way to do this in JCR or Jackrabbit?
Would it be supported in some point in future? Or should one use
relational database cases that require such queries?

Re: count function in query?

Posted by Alexander Klimetschek <ak...@adobe.com>.
On 21.11.2013, at 02:43, ilijas <il...@yahoo.com> wrote:

> I have same problem with matching similar articles by tags. I want to show
> related articles based on tags.
> I tried: 
> SELECT * FROM [nt:unstructured] AS t WHERE (t.tags LIKE '%Tag1%' OR t.tags
> LIKE '%Tag2%') ORDER BY length(tags) DESC
> 
> It would be nice if I can do next: 
> 
> SELECT * FROM [nt:unstructured] AS t WHERE (t.tags LIKE '%Tag1%' OR t.tags
> LIKE '%Tag2%') ORDER BY (length(tags) - length(replace(tags, 'Tag1', '')) -
> length(replace(tags, 'Tag2', '')) )DESC
> 
> but, I can't. If someone has some idea how to solve this, please share with
> us.

No, sorry, such functions are not available in jcr sql1 or sql2. You can only do the sorting on the application layer.

Oak lights up things a bit in that it allows custom search indexes to hook in, if you want to take a look at it.

Cheers,
Alex

Re: count function in query?

Posted by ilijas <il...@yahoo.com>.
I have same problem with matching similar articles by tags. I want to show
related articles based on tags.
I tried: 
SELECT * FROM [nt:unstructured] AS t WHERE (t.tags LIKE '%Tag1%' OR t.tags
LIKE '%Tag2%') ORDER BY length(tags) DESC

It would be nice if I can do next: 

SELECT * FROM [nt:unstructured] AS t WHERE (t.tags LIKE '%Tag1%' OR t.tags
LIKE '%Tag2%') ORDER BY (length(tags) - length(replace(tags, 'Tag1', '')) -
length(replace(tags, 'Tag2', '')) )DESC

but, I can't. If someone has some idea how to solve this, please share with
us.



--
View this message in context: http://jackrabbit.510166.n4.nabble.com/count-function-in-query-tp2294092p4659945.html
Sent from the Jackrabbit - Users mailing list archive at Nabble.com.

Re: count function in query?

Posted by Alexander Klimetschek <ak...@day.com>.
On Mon, Jul 19, 2010 at 17:18, Omid Milani <om...@gmail.com> wrote:
> Thanks for the response, but this isn't what I wanted.
> I'm trying to get count of something (like tag-references to the node)
> for each row of results, or order my result with it.
> With calling getSize on iterator, I would have to do n+1 queries
> instead of 1, to get the count. and event that wouldn't get the order
> right.

Oh, looks like I didn't read your question properly.

There is no count(*) or group by selector in JCR SQL [1], XPath [2] or
JCR-SQL2/AQM [3].

To implement such a tag cloud, you can run one query that fetches all
your content containing the relevant "tag" property:

//element(*, my:Article)[@tag]

and then iterate over the result and count your tags on the
application side by looking at the tag property values and using some
hashmap (tagid -> count).

[1] http://www.day.com/specs/jcr/1.0/ (section 8.5)
[2] http://www.day.com/specs/jcr/1.0/ (section 6.6)
[3] http://www.day.com/specs/jcr/2.0/6_Query.html

Regards,
Alex

-- 
Alexander Klimetschek
alexander.klimetschek@day.com

Re: count function in query?

Posted by Omid Milani <om...@gmail.com>.
Thanks for the response, but this isn't what I wanted.
I'm trying to get count of something (like tag-references to the node)
for each row of results, or order my result with it.
With calling getSize on iterator, I would have to do n+1 queries
instead of 1, to get the count. and event that wouldn't get the order
right.

On Mon, Jul 19, 2010 at 7:28 PM, Alexander Klimetschek <ak...@day.com> wrote:
> On Mon, Jul 19, 2010 at 16:27, Omid Milani <om...@gmail.com> wrote:
>> Hi,
>>
>> I'm implementing a tag cloud in JCR. I wanted to write a query to
>> return tags and number of articles tagged with each, similar to RDBMS
>> query
>>  select tag, count(*) as count from article_tag and tag where
>> article_tag.tag=tag.id group by tag order by count
>> I expected something like this to do it in jcr
>>  /tags/tag( node() |
>> count(//article/tag[@tag-link=node()/@jcr:uuid])) order by count(...)
>> It doesn't. I couldn't find a way to do it with sql2 and qom either,
>> seems there's no count function defined in any of them.
>>
>> Now, for tag cloud, I did without that query, by keeping count of
>> links in tag's node for each article tagged. But this seems an
>> important feature. Is there some way to do this in JCR or Jackrabbit?
>> Would it be supported in some point in future? Or should one use
>> relational database cases that require such queries?
>
> The nodes or row iterator in the JCR query result API give you the
> number of results found. For example:
>
> NodeIterator nodes = QueryResult.getNodes();
> int count = nodes.getSize();
> // ...
>
> Note that RangeIterator.getSize() is allowed to return -1 for
> optimizations, where the query engine itself works lazily and doesn't
> count the whole result set. Such optimizations are present with
> Jackrabbit 2.x now. To work around that, you can force an ordering in
> the query:
>
> //element(*, nt:file) order by @jcr:score descending
>
> (the default sort order is implicitly "order by @jcr:score
> descending", so this doesn't affect the result set)
>
> Regards,
> Alex
>
> --
> Alexander Klimetschek
> alexander.klimetschek@day.com
>

Re: count function in query?

Posted by Alexander Klimetschek <ak...@day.com>.
On Mon, Jul 19, 2010 at 16:27, Omid Milani <om...@gmail.com> wrote:
> Hi,
>
> I'm implementing a tag cloud in JCR. I wanted to write a query to
> return tags and number of articles tagged with each, similar to RDBMS
> query
>  select tag, count(*) as count from article_tag and tag where
> article_tag.tag=tag.id group by tag order by count
> I expected something like this to do it in jcr
>  /tags/tag( node() |
> count(//article/tag[@tag-link=node()/@jcr:uuid])) order by count(...)
> It doesn't. I couldn't find a way to do it with sql2 and qom either,
> seems there's no count function defined in any of them.
>
> Now, for tag cloud, I did without that query, by keeping count of
> links in tag's node for each article tagged. But this seems an
> important feature. Is there some way to do this in JCR or Jackrabbit?
> Would it be supported in some point in future? Or should one use
> relational database cases that require such queries?

The nodes or row iterator in the JCR query result API give you the
number of results found. For example:

NodeIterator nodes = QueryResult.getNodes();
int count = nodes.getSize();
// ...

Note that RangeIterator.getSize() is allowed to return -1 for
optimizations, where the query engine itself works lazily and doesn't
count the whole result set. Such optimizations are present with
Jackrabbit 2.x now. To work around that, you can force an ordering in
the query:

//element(*, nt:file) order by @jcr:score descending

(the default sort order is implicitly "order by @jcr:score
descending", so this doesn't affect the result set)

Regards,
Alex

-- 
Alexander Klimetschek
alexander.klimetschek@day.com