[Building Sakai] Counting messages in MSGCNTR private message folders
Kusnetz, Jeremy
JKusnetz at APUS.EDU
Thu Mar 24 10:18:34 PDT 2011
I'm getting the folder name by CMN_TYPE_T.DISPLAY_NAME where MFR_PVT_MSG_USR_T.TYPE_UUID=CMN_TYPE_T.UUID
MFR_TOPIC_T is for forum topics not for message folders.
Jeremy Kusnetz | Sr. Systems Engineer
American Public University System
American Military University | American Public University
661 S George Street, Charles Town, WV 25414
T 304-885-5333 | M 703-967-5212 | jkusnetz at apus.edu| www.apus.edu
-----Original Message-----
From: sakai-dev-bounces at collab.sakaiproject.org [mailto:sakai-dev-bounces at collab.sakaiproject.org] On Behalf Of DAVID ROLDAN MARTINEZ
Sent: Thursday, March 24, 2011 12:30 PM
To: Earle Nietzel
Cc: sakai-dev at collab.sakaiproject.org
Subject: Re: [Building Sakai] Counting messages in MSGCNTR private message folders
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"
_______________________________________________
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"
This message is private and confidential. If you have received it in error, please notify the sender and remove it from your system.
More information about the sakai-dev
mailing list