CODEWITHSUNDEEP
mysqlcoldfusion
Yannick
Yannick

Reputation: 235

Order all queries

I'm creating a new chat system with ColdFusion, but I'm having a lot of trouble with sorting out a little problem.

First of all I'll explain how the system works. When creating a new chat with someone, it creates a conversation row in the MySQL table conversations. After that, users can send each other messages. When sending a message, a row is inserted into the table conversations_messages with the timestamp, the user that sent the message, the message, and of course the id of the message. Sounds logical, but now comes the big problem: organizing all those messages.

Users can view their messages with others on a page, this is the code of that page:

// Check conversation ID based on page url
<cfif IsDefined('URL.chat') and URL.chat neq "">

     // Load conversation based on page url
     <cfquery name = "getconv" datasource = "#DSN#">
         SELECT * 
         FROM  `conversations` 
     </cfquery>

    // Get all messages from this conversation
    <cfoutput query="getconv">
         <cfquery name = "getmessages" datasource = "#DSN#">    
             SELECT * 
             FROM  `conversations_messages` 
             WHERE  `conversation` = '#chat#'
             ORDER BY  `conversations_messages`.`id` DESC 
         </cfquery>

         // Get messages sent by 'user_one'
         <cfquery name = "my" datasource = "#DSN#"> 
              SELECT * 
              FROM  `conversations_messages` 
              WHERE  `conversation` LIKE  '#chat#'
              AND    `user` LIKE  '#user.id#'
              ORDER BY  `conversations_messages`.`id` DESC 
         </cfquery>

         // Get messages sent by 'user_two'
         <cfquery name = "friend" datasource = "#DSN#"> 
             SELECT * 
             FROM  `conversations_messages` 
             WHERE  `conversation` LIKE  '#chat#'
             AND    `user` LIKE  '#getconv.user_two#'
         </cfquery>


         <div class="content">

             // Messages sent by 'user_one' aka. user that created conversation
             You: <cfoutput><cfloop query="my">#my.message#<br></cfloop></cfoutput>

             // Messages sent by 'user_two'
             Friend: <cfoutput><cfloop query="friend">#friend.message#</cfloop></cfoutput>

            </cfoutput>
         </div>

 </cfif>

This is how the results show up right now: (as you can see it's sorted by user)

enter image description here

I want it to be sorted by message ID, so it shows up like this:

enter image description here

I hope it's a bit clearer now! Help would be appreciated!

Upvotes: 1

Views: 120

Answers (2)

Miguel-F
Miguel-F

Reputation: 13548

(as Adam suggested here is an answer)

It seems to me that your query getmessages has what you are after. It looks to be all messages from this conversation = chat ordered by conversations_messages.id.

You really shouldn't query for * either, specify the needed columns only. You will get better performance that way.

Also, always use <cfqueryparam> tags in your cfquery blocks.

Also, you cannot nest <cfoutput> tags without a group attribute. Your code is already in a <cfoutput> so no need for another.

If you are on ColdFusion 10 I would definitely give @Adam's answer a look.

Upvotes: 2

Adam Cameron
Adam Cameron

Reputation: 29870

(not sure why everyone is answering in comments rather than as "an answer"?)

The order that your results are returned from the DB are purely down to the order you tell them to be returned. So if you want them chronologically, then don't fetch them ordered by name; order them chronologically.

So don't get user1's messages, and then user2's messages, just get the messages for the entire conversation in conversation order. Then when you're outputting them, look at which user made which comment and do the "you" / "friend" stuff.

That said, I think the approach you're taking is less than ideal. What you should perhaps be doing is fetching the messages incrementally, as they happen, rather than waiting to get all of them (if you see what I mean). So on each person's UI get all the messages since the last displayed message, and display 'em (in chronological order), then wait for some polling interval, and then fetch the next lot of messages that have occurred since the last time (etc).

If you're using ColdFusion 10 all this stuff has already been done for you via the Web Sockets technology CF has. So that would be the way to go if that's an option for you.

Upvotes: 3

Related Questions