[Building Sakai] Counting messages in MSGCNTR private message folders

[DAVID ROLDAN MARTINEZ]

This works fine but I need to retrieve the info by topic (equals to folder?) and I don't know to link MFR_TOPIC_T table. I've tried with
select count(*) from MFR_MESSAGE_T mmt
    join MFR_PVT_MSG_USR_T mpmut on (mpmut.messageSurrogateKey = mmt.ID)
    where mpmut.CONTEXT_ID = 'CFP_18_3185'
    and mpmut.USER_ID = '25417714'
        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'
                                        )
        ) 
but I get 0. :(
________________________________________
De: Earle Nietzel [earle.nietzel at gmail.com]
Enviado el: jueves, 24 de marzo de 2011 15:23
Para: DAVID ROLDAN MARTINEZ
CC: sakai-dev at collab.sakaiproject.org
Asunto: Re: [Building Sakai] Counting messages in MSGCNTR private message folders

Ahh I see your after the Private Messages you can try the following this will retrieve PM's for a user and site:

select * from MFR_MESSAGE_T mmt
    join MFR_PVT_MSG_USR_T mpmut on (mpmut.messageSurrogateKey = mmt.ID)
    where mpmut.CONTEXT_ID = 'CFP_18_3185'
    and mpmut.USER_ID = '25417714';

Earle

On Thu, Mar 24, 2011 at 8:33 AM, DAVID ROLDAN MARTINEZ <darolmar at upvnet.upv.es<mailto:darolmar at upvnet.upv.es>> wrote:
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<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<mailto: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"