[Building Sakai] Counting messages in MSGCNTR private message folders

[DAVID ROLDAN MARTINEZ]

      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