[Building Sakai] Counting messages in MSGCNTR private message folders

[Earle Nietzel]

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> 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]
> *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
>
> 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/20110324/c874e860/attachment.html