[Building Sakai] Counting messages in MSGCNTR private message folders

[DAVID ROLDAN MARTINEZ]

Hi Earle,

That was exactly what I thought at first but when I execute the following query:
select count(*) from mfr_message_t mmt where
        mmt.message_dtype='PM'
        and MMT.SURROGATEKEY in (
            select MTT.ID 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'
                                        and mpft.owner = '25417714'
                                        )
        )

The results should be the same as the number of private message in Received folder for user 24517714 in site CFP_18_3185. However, the former query returns 0. Any clue about where the error is?

Thank you very much in advance.

Cheers,
                David


De: Earle Nietzel [mailto:earle.nietzel at gmail.com]
Enviado el: miércoles, 23 de marzo de 2011 21:30
Para: DAVID ROLDAN MARTINEZ
CC: sakai-dev at collab.sakaiproject.org
Asunto: Re: [Building Sakai] Counting messages in MSGCNTR private message folders

The MFR_MESSAGE_T table is linked to MFR_TOPIC_T via:

MFR_MESSAGE_T.surrogateKey = MFR_TOPIC_T.ID<http://MFR_TOPIC_T.ID>

Earle
On Wed, Mar 23, 2011 at 5:11 AM, DAVID ROLDAN MARTINEZ <darolmar at upvnet.upv.es<mailto: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<http://mpft.id> from mfr_private_forum_t mpft, mfr_area_t mat where
                                       MPFT.SURROGATEKEY = MAT.ID<http://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<mailto: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<mailto: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/20110324/38c62357/attachment.html