www.webdeveloper.com
Page 1 of 2 12 LastLast
Results 1 to 15 of 22

Thread: Need Help Joining these Queries

  1. #1
    Join Date
    Jun 2006
    Location
    Under your bed
    Posts
    357

    Need Help Joining these Queries

    I am trying to construct a query that deals with threads and replies.

    It involves two tables: one for private message threads, the other for private message replies. When a user first messages another user, a thread is created. The thread information is stored in private_threads and it contains the thread ID number, sender's ID number, message title, message content, and a couple other things. Any subsequent correspondence between the two users is added as a reply to this thread. These subsequent messages go into private_replies and they contain the author's ID number, a unix timestamp,the message content, and a couple other things.

    When users go to their inbox I want them to see a table that lists the threads along with who last replied to them. This is why I need the join query. I'm thinking the query would tell MySQL something like "Find all private message threads received by this user and pull them up. Then find the latest reply for each thread. If a reply is found, get the author's ID number. If not, then return the thread creator's ID number."

    This is the query I have so far. It does't do any joins yet:
    Code:
    SELECT `thread_id`, `title`, `message`, `sender_id`, `timestamp`, `read_by_receiver`, `read_by_sender` FROM `private_threads` WHERE `receiver_id` = "1" ORDER BY `timestamp` DESC
    Does this make sense?
    The better I get at programming, the more I appreciate arrays. Handy dandy things they are.

  2. #2
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,326
    I'd probably use a sub-query.
    Code:
    SELECT 
      `thread_id`,
      `title`,
      `message`,
      `sender_id`,
      `timestamp`,
      `read_by_receiver`,
      `read_by_sender`
      (
        SELECT `user_name`
        FROM `private_replies`
        WHERE `thread_id` = `private_threads`.`thread_id`
        ORDER BY `timestamp` DESC LIMIT 1 -- only want the latest reply
      )
    FROM `private_threads` 
    WHERE `receiver_id` = "1" 
    ORDER BY `timestamp` DESC
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  3. #3
    Join Date
    Jun 2006
    Location
    Under your bed
    Posts
    357
    I tried to run that query and I got this:

    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT `user_name` FROM `private_replies` WHERE `thread_id` = `private_t' at line 10
    Edit, I think my MySQL version is 5.5.29-log
    Last edited by evenstar7139; 01-29-2013 at 04:51 PM.
    The better I get at programming, the more I appreciate arrays. Handy dandy things they are.

  4. #4
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,326
    Probably the missing comma after `read_by_sender` (plus whatever other mistakes I made).
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  5. #5
    Join Date
    Jun 2006
    Location
    Under your bed
    Posts
    357
    Adding that comma in fixed it. That's great. Now I'm wondering:

    #1 If no replies are found, is there a way to have the subquery return the sender_id for the thread? (The whole idea is it returns whoever made the last post. Obviously that's the thread creator if there are no replies.)

    #2 Do you think this query will be faster than doing two separate queries?
    The better I get at programming, the more I appreciate arrays. Handy dandy things they are.

  6. #6
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,326
    #1. I might just leave that to the application code to figure out (a simple if/else should be able to handle that).

    #2. All things being equal (which they never are, of course), I'd tend to favor the single query when feasible as far as performance goes, but assuming you keep the DB connection open between queries, there's probably not all that much additional overhead, so I'd probably not sweat unless/until you determine you are having a performance bottleneck in that particular area. Sometimes I opt for maintainability over raw performance, in which case I might run separate queries when things start to get too convoluted.
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  7. #7
    Join Date
    Jun 2006
    Location
    Under your bed
    Posts
    357
    I tried to do a join in the subquery and MySQL said "Operand should contain 1 column(s)". I guess you can't do joins in subqueries?

    This was the code:

    PHP Code:
     $query $this->db->query("
    SELECT 
          `thread_id`,
          `title`,
          `message`,
          `sender_id`,
          `timestamp`,
          `status_with_receiver`,
          `status_with_sender`,
          (
             SELECT `message_replies`.`author_id`, `user_accounts`.`display_name`
             FROM `message_replies`, `user_accounts`
             WHERE `message_replies`.`thread_id` = `message_threads`.`thread_id` and `message_replies`.`author_id` = `user_accounts`.`user_id`
             ORDER BY `timestamp` DESC LIMIT 1 -- only want the latest reply
          )
            FROM `message_threads` 
            WHERE `receiver_id` = "
    .$this->db->escape($user_id)." or `sender_id` = ".$this->db->escape($user_id)." AND `timestamp` ".$sign.$timeframe.
            ORDER BY `timestamp` DESC"
    ); //$sign.$timeframe will make something like "> 1209600" 
    How could I re-write this code to make it acceptable to MySQL?
    The better I get at programming, the more I appreciate arrays. Handy dandy things they are.

  8. #8
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,326
    In that type of sub-query usage, it can only select one column -- or multiple columns concatenated or otherwise combined into a single field. In other words, the sub-query is being used to populate a single column in the result rows.
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  9. #9
    Join Date
    Jun 2006
    Location
    Under your bed
    Posts
    357
    Okay, I have the following now. I have two problems:

    #1 For "`timestamp` > 1358748798" I want it to look at each timestamp for each message reply that is retrieved, and if there are none, look at the timestamp in the thread.

    #2 To order the results by the timestamps in the last replies (and in cases where there isn't a last reply, use the thread timestamp)

    Code:
    SELECT 
    `thread_id`, 
    `title`, 
    `message`, 
    `sender_id`, 
    `timestamp`, 
    `status_with_receiver`, 
    `status_with_sender`, 
    ( SELECT `message_replies`.`author_id` FROM `message_replies` WHERE `message_replies`.`thread_id` = `message_threads`.`thread_id` ORDER BY `timestamp` DESC LIMIT 1 ), 
    ( SELECT `user_accounts`.`display_name` FROM `user_accounts`, `message_replies` WHERE `message_replies`.`thread_id` = `message_threads`.`thread_id` and `message_replies`.`author_id` = `user_accounts`.`user_id` )	 
    FROM `message_threads` 
    WHERE `receiver_id` = '1' or `sender_id` = '1' AND `timestamp` > 1358748798 ORDER BY `timestamp` DESC
    Last edited by evenstar7139; 02-04-2013 at 02:45 AM.
    The better I get at programming, the more I appreciate arrays. Handy dandy things they are.

  10. #10
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,326
    Before we try to figure this out, if it's not to late in the game for this, I'd suggest a DB redesign that would get rid of this problem: use one table for all of the messages, so that the original message text is treated the same as any/all replies -- it's essentially just the first reply to that thread.
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  11. #11
    Join Date
    Jun 2006
    Location
    Under your bed
    Posts
    357
    I changed the structure of the tables so message_threads only houses information about the thread and every message goes in message_messages. I also changed my query a lot and it's not working like I want. It's returning one result for every reply, instead of one result for every thread. So, if a thread has say 5 replies, it will return 5 results for that thread. It should only return one result and that result should just have information about the thread's last reply.

    Here's the query:

    PHP Code:
    $query $this->db->query("SELECT a.*, LEFT(b.`message`,77) as `message`, b.`timestamp`, b.`author_id`, c.`display_name` as `author_dn`
    FROM `message_threads` a
    JOIN `message_messages` b ON a.`thread_id` = b.`thread_id`
    JOIN `user_accounts` c ON b.`author_id` = c.`user_id`
    WHERE `creator_id` = "
    .$this->db->escape($this->user_info['user_id'])." or `receiver_id` = ".$this->db->escape($this->user_info['user_id'])."
    ORDER BY b.`timestamp` DESC"
    ); 
    The better I get at programming, the more I appreciate arrays. Handy dandy things they are.

  12. #12
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,326
    Looks like you just need a "LIMIT 1" after the "DESC" of your ORDER BY clause.
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  13. #13
    Join Date
    Jun 2006
    Location
    Under your bed
    Posts
    357
    I tried that. Then it only brings up one thread, regardless of how many there are. I want it to bring up all the threads, just one reply per thread. Does that make sense?

    (Looking at the timestamp in the reply is the only way to figure out when was the last activity on the thread.)
    Last edited by evenstar7139; 02-05-2013 at 02:34 PM.
    The better I get at programming, the more I appreciate arrays. Handy dandy things they are.

  14. #14
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,326
    Ah...I see. One way is to add a sub-query within your where clause.
    Code:
    WHERE <other where clause stuff> AND
    b.timestamp = (SELECT MAX(timestamp) FROM message_messages WHERE thread_id = b.thread_id)
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  15. #15
    Join Date
    Jun 2006
    Location
    Under your bed
    Posts
    357
    *scratches head* I sent messages in between my main account and my tester account. On my main it's correctly showing one listing per thread. On my tester it's showing one listing per reply. Here, look:

    Main Account: http://i22.photobucket.com/albums/b3...psbca038d9.png

    Tester Account: http://i22.photobucket.com/albums/b3...ps5c9f0e2b.png

    Also, here's what's in the message_threads table: http://i22.photobucket.com/albums/b3...psb4787b9c.png (hope you can read it...stupid photobucket resized it)

    message_messages table: http://i22.photobucket.com/albums/b3...psce27c1af.png

    And here's our query, as it currently stands:
    PHP Code:
    $query $this->db->query("SELECT a.*, LEFT(b.`message`,77) as `message`, b.`timestamp`, b.`author_id`, c.`display_name` as `author_dn`
    FROM `message_threads` a
    JOIN `message_messages` b ON a.`thread_id` = b.`thread_id`
    JOIN `user_accounts` c ON b.`author_id` = c.`user_id`
    WHERE `creator_id` = "
    .$this->db->escape($this->user_info['user_id'])." or `receiver_id` = ".$this->db->escape($this->user_info['user_id'])." AND
    b.timestamp = (SELECT MAX(timestamp) FROM messages_messages WHERE thread_id = b.thread_id)
    ORDER BY b.`timestamp` DESC"
    ); 
    Last edited by evenstar7139; 02-05-2013 at 06:06 PM.
    The better I get at programming, the more I appreciate arrays. Handy dandy things they are.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles