You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@jackrabbit.apache.org by gregoryjoseph <gr...@magnolia-cms.com> on 2009/02/05 15:46:56 UTC
Counting occurences of a given property's different values
Hi list,
Here's what I'd like to do, in semi-sql form:
SELECT count(*), prop_value FROM nodes WHERE prop_name='foo' GROUP BY
prop_name
(or something in that vein, i'd have to refresh my sql 'skills')
The use case is simply a tag cloud. I have content nodes with one property
'tag', which is a multi-value. I want to know how many time each distinct
value if used over all (or a set of) nodes.
JCR-260 tells me that count() isn't implemented in xpath, and I assume it
isn't in jcr-sql either. Is there any alternative, or something obvious that
I don't see ? It'd be acceptable for me to use jackrabbit extensions (i.e a
custom function maybe)
Cheers and thanks for any tip,
-g
--
View this message in context: http://www.nabble.com/Counting-occurences-of-a-given-property%27s-different-values-tp21852761p21852761.html
Sent from the Jackrabbit - Users mailing list archive at Nabble.com.
Re: Counting occurences of a given property's different values
Posted by Sébastien Launay <se...@anyware-tech.com>.
gregoryjoseph a écrit :
> Thanks for the hint; however, I still don't know what any of the values are.
> My initial fake query might have been misleading because of the
> prop_name='foo' clause, might have been clearer with prop_name='tag'. In
> short, I know the property *name* I'm looking for, and I want to collect all
> its existing *values* over a set of nodes in my tree.
>
Hi Gregory,
Because you want to browse tags, you might want to change your model by:
- creating a branch with the tags (/jcr:root/tags/foo)
- references these tags in your node by using a path property or
a reference property.
Reference property allow you to count references and then simply use:
node.getReferences().getSize()
on the tag node to get its sizes.
But, references comes with somes constraints [1].
The "query" will just be :
List<Pair<String, Integer>> tagCloud = new ArrayList()
for (Node node : tagsNode.getNodes())
tagCloud.add(new Pair(node.getName(), node.getReferences().getSize())
[1]
http://wiki.apache.org/jackrabbit/DavidsModel#head-ed794ec9f4f716b3e53548be6dd91b23e5dd3f3a
--
Sébastien Launay
Re: Counting occurences of a given property's different values
Posted by gregoryjoseph <gr...@magnolia-cms.com>.
Alexander Klimetschek wrote:
>
> On Thu, Feb 5, 2009 at 4:28 PM, gregoryjoseph
> <gr...@magnolia-cms.com> wrote:
>>> SELECT * FROM my:nodetype WHERE tag='foo'
>>> Not quite - i do not know all possible values for the tag property and i
>>> went to know how many time each distinct value is used.
>
> For multi-value properties, the meaning of "=" is slightly different
> in jcr xpath/sql: it means that the exact value occurs at least once
> in the array of values.
>
> See section 6.6.4.10 (xpath) and 8.5.2.7 (sql) of the jcr spec:
>
> http://www.day.com/specs/jcr/1.0/6.6.4.10_Searching_Multi-value_Properties.html
>
> http://www.day.com/specs/jcr/1.0/8.5.2.7_Multi-value_Properties.html
>
Thanks for the hint; however, I still don't know what any of the values are.
My initial fake query might have been misleading because of the
prop_name='foo' clause, might have been clearer with prop_name='tag'. In
short, I know the property *name* I'm looking for, and I want to collect all
its existing *values* over a set of nodes in my tree.
--
View this message in context: http://www.nabble.com/Counting-occurences-of-a-given-property%27s-different-values-tp21852761p21854986.html
Sent from the Jackrabbit - Users mailing list archive at Nabble.com.
Re: Counting occurences of a given property's different values
Posted by Alexander Klimetschek <ak...@day.com>.
On Thu, Feb 5, 2009 at 4:28 PM, gregoryjoseph
<gr...@magnolia-cms.com> wrote:
>> SELECT * FROM my:nodetype WHERE tag='foo'
>> Not quite - i do not know all possible values for the tag property and i
>> went to know how many time each distinct value is used.
For multi-value properties, the meaning of "=" is slightly different
in jcr xpath/sql: it means that the exact value occurs at least once
in the array of values.
See section 6.6.4.10 (xpath) and 8.5.2.7 (sql) of the jcr spec:
http://www.day.com/specs/jcr/1.0/6.6.4.10_Searching_Multi-value_Properties.html
http://www.day.com/specs/jcr/1.0/8.5.2.7_Multi-value_Properties.html
Regards,
Alex
--
Alexander Klimetschek
alexander.klimetschek@day.com
Re: Counting occurences of a given property's different values
Posted by gregoryjoseph <gr...@magnolia-cms.com>.
Alexander Klimetschek wrote:
>
> Run your jcr xpath/sql query that selects your desired nodes and then
> call QueryResult.getNodes().getSize() to get the number of results.
> The query for your case would probably be something like this:
>
> SELECT * FROM my:nodetype WHERE tag='foo'
> Not quite - i do not know all possible values for the tag property and i
> went to know how many time each distinct value is used.
Worst case I guess i should be able to do SELECT tag FROM my:ntype WHERE tag
IS NOT NULL and do the counting myself; just wanted to see if there was
anything else I could do.
--
View this message in context: http://www.nabble.com/Counting-occurences-of-a-given-property%27s-different-values-tp21852761p21853614.html
Sent from the Jackrabbit - Users mailing list archive at Nabble.com.
Re: Counting occurences of a given property's different values
Posted by Alexander Klimetschek <ak...@day.com>.
On Thu, Feb 5, 2009 at 3:46 PM, gregoryjoseph
<gr...@magnolia-cms.com> wrote:
> Here's what I'd like to do, in semi-sql form:
> SELECT count(*), prop_value FROM nodes WHERE prop_name='foo' GROUP BY
> prop_name
> (or something in that vein, i'd have to refresh my sql 'skills')
>
> The use case is simply a tag cloud. I have content nodes with one property
> 'tag', which is a multi-value. I want to know how many time each distinct
> value if used over all (or a set of) nodes.
Run your jcr xpath/sql query that selects your desired nodes and then
call QueryResult.getNodes().getSize() to get the number of results.
The query for your case would probably be something like this:
SELECT * FROM my:nodetype WHERE tag='foo'
Regards,
Alex
--
Alexander Klimetschek
alexander.klimetschek@day.com