fahlen

Does anyone know how to query a list for the number of items that fulfill a certain requirement without actually returning all those items Can one do this in CAML using SPQuery Or any other class using the SharePoint API

As a last resort, I imagine one could query the table in the database directly using SQL. How do I find the names of the database, table and columns

Thanks,
Markus


Re: SharePoint - Development and Programming Aggregate functions when querying lists

fahlen

No one There has to be some way to get the number of results of a query without actually returning the list data itself. I'd really appreciate any input. Thanks in advance.




Re: SharePoint - Development and Programming Aggregate functions when querying lists

__Bill__F__

As far as I know, there is no way to do a COUNT(*) in an SPQuery. You could perform your query in code, and only return the number of items, like:

Code Block

Dim myList as SPList = SPContext.Current.Web.Lists("My List Name")

Dim myQuery as new SPQuery

myQuery.Query = "<Where>(Your filter here)</Where>"

Dim myItems as SPListItemCollection = myList.GetItems(myQuery)

Return myItems.Count

Altough I suspect that's not really how you want to do it.

As for querying the database directly, this is possible but I'm not sure if it's a good or bad thing to do so. The place to look is the "UserData" view in your "WSS_Content" database. To find out wich fields (they are named int1, int2, int3 etc.) contains the data you're interested in, look in the "tp_fields" field in the view "Lists". This field contains xml with the mappings between list fields and database fields.

One more thing to consider is that if you have many fields in your list, there is a change that one item will be split over several rows in the database.

/Bill.





Re: SharePoint - Development and Programming Aggregate functions when querying lists

fahlen

That's what I imagined.I suppose for now I will settle with returning all the query results and then count the items, although I'm not interested in the items, only the number of items. Later, if I notice that the CAML queries take a long time to execute, I will explore the alternative of working directly against the database with SQL.

Thank you.




Re: SharePoint - Development and Programming Aggregate functions when querying lists

Gaurav Rehan

This is strange, There should be some way by which we should be able to fire aggregate queries via view fields passed to GetListItems. I was going through the sdk, it mentions elements like <Aggregation> but it didnt worked for me. Query field has an options of specifying <GroupBy> so Aggregations should be possible. (the problem is that the SDK does not have a working example for performing aggregations via Lists.GetListItems)

It would be really bad from MS side if they do not provide aggregation queries via web services