Query for messages

dotmiko

I'm trying to do a report for the amount of messages for a certain account type.

Basically I was able to create queries already for:
1) Number of unread messages by the user
2) Total number of read messages by the user

All I need is a query for the number of messages that that user has replied to.

This is for educational purposes, can anyone help me out

peregrine

share your code and I might be able to help you.

dotmiko

select a.name as legislator, a.CountUnreadConversations as Unread, c.CountReadMessages as alreadyRead
from gdn_user as a join gdn_userrole as b on a.UserID = b.UserID join gdn_userconversation as c on c.UserID = b.UserID where b.RoleID = 33

basically, i'm trying to do an eparticipation system. i'm trying to make a report on how many messages these legislators are ignoring/replying to the queries of the people :P

hgtonight

The last part will require some type of "advanced" data query calculation because, AFAIK, vanilla doesn't store the number of discussions replied to. You can use c.CountComments as a starting point, but unless you only allow 1 comment per discussion, it will be a largely meaningless number.

You would have to comb through every read discussion, note the id, join in the comments table (based on the discussion id) and see if the legislators id is in the result set.

Admittedly, I am not a DB guru. Take my advice with a grain of salt.

peregrine

you might look at

InsertUserID and UpdateUserID

GDN_Conversation` -

dotmiko

I can't seem to find the connection or thought logic between those three columns =\

peregrine

InsertUseriD is the initiator of conversation.

Every time a reply is made to a conversation  UpdateUserID is set to the last replier

also look in GDN_ConversationMessage`

InsertUserID = it will show you who participated  in a particular conversation

e.g. for a specific conversation.

SELECT *
FROM `GDN_ConversationMessage`
WHERE `ConversationID` =1

so if the result shows a particular userid you know they replied.

if you pseudo code what to read for one conversation , store info in an array and do what you need to process for just one conversation and get it right. Then you can loop.

Or conversely you could read all the results into one huge array and manipulate as needed.

between the 3 tables you should be able to get what you need. Give it a few days of thought .

It's going to require a few steps I think, not one simple join.

peregrine

another idea.

loop thru conversations

SELECT `Contributors`
FROM `GDN_Conversation`
WHERE `ConversationID` = $conversation

parse with json decode

get your contributors
a:2:{i:0;s:1:"2";i:1;s:1:"4";}


id 2 and 4 are participants in conversation

// who replied

SELECT DISTINCT `InsertUserID`
FROM `GDN_ConversationMessage`
WHERE `ConversationID` =2

will show you who actually wrote a message.

so the different id's between a:2:{i:0;s:1:"2";i:1;s:1:"4";} and the result of who replied will show you who got a message an never replied.

dotmiko

I see, lemme play around with it. Thank you for all the help!

peregrine

I don't know exactly what you want - so i can't tell exactly what you want.

If specify an exact scenario in detail. who does what when and where?

Shadowdare

@dotmiko said:
All I need is a query for the number of messages that that user has replied to.

The following query will return the total number of conversations a user has replied to.

SELECT COUNT(*) FROM GDN_ConversationMessage WHERE InsertUserID = $intUserID AND MessageID NOT IN (SELECT FirstMessageID from GDN_Conversation) GROUP BY ConversationID LIMIT 1

Replace $intUserID with a variable that has the user's ID.

Also, the following query will return the total number of replies a user has made in all conversations:

SELECT COUNT(*) FROM GDN_ConversationMessage WHERE InsertUserID = $intUserID AND MessageID NOT IN (SELECT FirstMessageID from GDN_Conversation)

peregrine

@Shadowdare said:
SELECT COUNT(*) FROM GDN_ConversationMessage WHERE InsertUserID = $intUserID AND MessageID NOT IN (SELECT FirstMessageID from GDN_Conversation)

better yet convert these to vanillaese.

x00

You are taking the wrong approach, instead of using inefficient queries becuase the functionality is not there, create the functionality.

You will need de-normalised counts, and to update them appropriately. Then you can reference them easily.

peregrine

@x00

You will need de-normalised counts

elucidate please on the more efficient approach.

and de-normalised counts means what?

x00

http://en.wikipedia.org/wiki/Denormalization

You update your counts when the event happens, whatever that is.

Shadowdare

In many cases, there is more than one way to code something. For example, NOT IN, JOIN, and NOT EXISTS IN can all be used for finding missing values in MySQL, but the procedure for EXISTS is less efficient than the two other functions for this purpose. Sometimes programmers must make trade-offs between safety and speed and this also is considered in database design.

Many believe that normalization is not considered a wrong approach unless there is a huge performance hit that denormalization may be predicted to fix. Denormalization can introduce more problems than it may solve, such as slower update times, but it may help with the performance of specific query procedures of large databases; however, normalization is preferred for general query procedures.

peregrine

It seems like the questioner might only "run a report of this time to time" so your solution seems the easiest since it is done.

Just curious, i haven't messed with it, but do you know the way to code this in vanilla, parlance and is it possible.

e.g.

SQL->
Select .....

Shadowdare

Here is another way to do what @dotmiko needed for returning how many conversations a user has responded to:

SELECT COUNT(DISTINCT ConversationID) FROM GDN_ConversationMessage WHERE InsertUserID = $intUserID AND MessageID NOT IN (SELECT FirstMessageID from GDN_Conversation)

I prefer this line of code over the first one I posted earlier.

@peregrine, it is possible to do this in Vanilla. This will get the total number of replies a user has made in all conversations (all messages from a user excluding the first message of a conversation) in Vanilla:

$intConversationReplyCount = Gdn::SQL()->Select('cm.ConversationID')
   ->From('ConversationMessage cm')
   ->Join('Conversation c', 'cm.MessageID = c.FirstMessageID', 'left')
   ->Where('c.FirstMessageID', null)
   ->Where('cm.InsertUserID', $intUserID)
   ->Get()
   ->NumRows();

echo $intConversationReplyCount;

Again, replace $intUserID with a variable that has the user's ID.

If you need to retrieve how many conversations a user has responded to, change the Select function to:

Select('cm.ConversationID', 'DISTINCT')

x00

Define replied. In a conversation you can receive a new message you haven't read yet, although you have replied to the conversation before, you have not replied to that message. You get a notification if there is a new message after all.

I assumed he was using it in order to in order to give the user info.

Shadowdare

I think he meant he wants to get how many conversations a user has responded to when he said "replied."

peregrine

@x00 and @Shadowdare

I think that is why it is why it was so confusing, the question is a bit ambiguous, so its a bit hard to put a logical construct about it.

It always leads to - ok I sent a message to somebody and they reply "no" and they reply "yes" or "I don't know". Of what consequence is the reply, in my limited vision of this, it doesn't previde much more information that if you tested whethter they read the message, which I think a plugin is already available for.

thanks for the vanilla conversion - Shadowdare

@dotmiko - this has turned out to be an interesting thread, want to explain more - what indeed are you trying to find out and how does it help you.