[Building Sakai] Messages count

[John Leasia]

Haven't done this in a while, here is something that I pulled from my 
bookmarks. Used it on Oracle. Might work for you or give you a start.
You could change it to remove the mfr_area_t.context_id like in the 
where so that it considers all sites
  (change
where mfr_area_t.context_id like '83ef2c1f-e77b-473f-00be-ddebb90517bc'
and mfr_area_t.id=mfr_open_forum_t.surrogatekey
to
where  mfr_area_t.id=mfr_open_forum_t.surrogatekey )

-- count messages per topic per forum for a site
select count(mmt.id) as num_messages, MFR_OPEN_FORUM_T.title as Forum,  
mfr_topic_t.title as Topic
from MFR_OPEN_FORUM_T, mfr_topic_t, mfr_area_t, mfr_message_t mmt
where mfr_area_t.context_id like '83ef2c1f-e77b-473f-00be-ddebb90517bc'
and mfr_area_t.id=mfr_open_forum_t.surrogatekey
and mfr_open_forum_t.id =mfr_topic_t.of_surrogatekey
and mmt.surrogatekey(+)=mfr_topic_t.id
group by MFR_OPEN_FORUM_T.title,  mfr_topic_t.title
order by MFR_OPEN_FORUM_T.title


DAVID ROLDAN MARTINEZ wrote:
>
> Hi all,
>
> Does anybody know how to query Message Center tables to get number of 
> messages on a per topic (received, sent, deleted), user and site 
> basis? I've been looking at hbm.xml files to see if there was a named 
> query to help me but I haven't found any.
>
> Any help will be too much appreciated.
>
> Thank you very much in advance.
>
> Cheers,
>
> David Roldán Martínez
>
> Descripción: Descripción: I love touch
>
>
>
> _______________________________________________
> sakai-dev mailing list
> sakai-dev at collab.sakaiproject.org
> http://collab.sakaiproject.org/mailman/listinfo/sakai-dev
>
> TO UNSUBSCRIBE: send email to sakai-dev-unsubscribe at collab.sakaiproject.org with a subject of "unsubscribe"

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20120216/cb20bd46/attachment.html 
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: image/gif
Size: 8132 bytes
Desc: not available
Url : http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20120216/cb20bd46/attachment.gif