[Building Sakai] Counting messages in MSGCNTR private message folders
Earle Nietzel
earle.nietzel at gmail.com
Fri Mar 25 13:22:13 PDT 2011
Hello Dave,
Sorry for not getting back earlier but had some other things to tend to.
Here are the pieces to get what you need:
1) Use this query to get MFR_AREA_T.ID, where mat.CONTEXT_ID is the site
interested in
select mat.ID
from MFR_AREA_T mat
where mat.CONTEXT_ID='59222d9f-cdb5-4a59-9b10-416a48952fcf' and mat.NAME =
'Messages'
2) Then use this query to get the folders from Messages, where
MFR_PRIVATE_FORUM_T.surrogateKey = MFR_AREA_T.ID (from step 1) and
MFR_PRIVATE_FORUM_T.OWNER=<user id>
select mpft.ID, mtt.ID, mpft.UUID, mpft.TITLE, mpft.TYPE_UUID, mpft.OWNER,
mpft.surrogateKey, mtt.UUID, mtt.TITLE, mtt.TYPE_UUID, mtt.pf_surrogateKey,
mtt.USER_ID, mtt.TOPIC_DTYPE
from MFR_PRIVATE_FORUM_T mpft left outer join MFR_TOPIC_T mtt on
mpft.ID=mtt.pf_surrogateKey where mpft.surrogateKey=8655 and mpft.OWNER =
'<userid>'
3) I havn't figured out how to go from the folders to the individual
messages which you only seem to be able to query from MFR_PVT_MSG_USR_T. But
it is close I am sure you will get it!
Earle
On Thu, Mar 24, 2011 at 12:29 PM, DAVID ROLDAN MARTINEZ <
darolmar at upvnet.upv.es> wrote:
> 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"
>
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20110325/8ee8e460/attachment.html
More information about the sakai-dev
mailing list