[Building Sakai] Counting messages in MSGCNTR private message folders

[Earle Nietzel]

The MFR_MESSAGE_T table is linked to MFR_TOPIC_T via:

MFR_MESSAGE_T.surrogateKey = MFR_TOPIC_T.ID

Earle

On Wed, Mar 23, 2011 at 5:11 AM, DAVID ROLDAN MARTINEZ <
darolmar at upvnet.upv.es> wrote:

>
>      Hi all,
>
>      I need to make a report for our staff. We need to count message inside
> received, sent and deleted folders in Private Messages tool for a set of
> sites on a per instructor basis (the objective is to measure instructor
> productivity). I have been trying to figure out how to retrieve this
> information directly from the database but I haven't been able to design the
> right query.
>
>      The folder of one particular user in a site inside Private Message
> tools are stored a MFR_TOPIC_T and can be found as follows (for received
> messages):
>
>            select mtt.* from mfr_topic_t mtt where
>                    mtt.topic_dtype='PT'
>                    and mtt.title='pvt_received'
>                    and mtt.pf_surrogatekey in (
>                        select mpft.id from mfr_private_forum_t mpft,
> mfr_area_t mat where
>                                        MPFT.SURROGATEKEY = MAT.ID
>                                        and mat.context_id = 'CFP_18_3185'
>  <--- This is the site id
>                                        and mpft.owner = '25417714'
>      <--- This is the user id
>                                        )
>
>         However, I don't how to relate this information with messages
> information. Private Messages from a user are stored at MRF_MESSAGE_T and
> can be retrieved as follows:
>
> select * from mfr_message_t mmt where
>        mmt.message_dtype='PM'
>        and mmt.deleted = 0
>        and mmt.draft = 0
>        and mmt.created_by = '25417714'    <--- This is the user id
>
>        But I don't now how to establish the relationship between messages
> and topics. Does any body of you can help us, please?
>
>        Thank you very much in advance.
>
>         Cheers,
>                David
> _______________________________________________
> 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/20110323/1ba3f462/attachment.html