Need Help Joining these Queries

This site is best viewed in a modern browser with JavaScript enabled.

evenstar7139

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:

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?

NogDog

I'd probably use a sub-query.

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

evenstar7139

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

NogDog

Probably the missing comma after read_by_sender (plus whatever other mistakes I made).

evenstar7139

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?

NogDog

#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.

evenstar7139

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:

 $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?

NogDog

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.

evenstar7139

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)

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

NogDog

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.

evenstar7139

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:

$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");

NogDog

Looks like you just need a "LIMIT 1" after the "DESC" of your ORDER BY clause.

evenstar7139

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.)

NogDog

Ah...I see. One way is to add a sub-query within your where clause.

WHERE <other where clause stuff> AND
b.timestamp = (SELECT MAX(timestamp) FROM message_messages WHERE thread_id = b.thread_id)

evenstar7139

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/b306/evenstar7139/my_account_zpsbca038d9.png

Tester Account: http://i22.photobucket.com/albums/b306/evenstar7139/tester_zps5c9f0e2b.png

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

message_messages table: http://i22.photobucket.com/albums/b306/evenstar7139/message_messages_zpsce27c1af.png

And here's our query, as it currently stands:

$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");

evenstar7139

Additional information:

On the line next to last, I changed it to this: b.timestamp = (SELECT MAX(timestamp) FROM furrymail_messages WHERE a.thread_id = b.thread_id)

and then it just pulls up the thread that had a reply the most recently. This is still wrong, but I thought you'd like to know.

evenstar7139

I still cannot get this query to work. Is it better to just use a PHP foreach() and send multiple queries?

Something like this:

$query = $this->db->query("SELECT `thread_id`, `title` FROM `message_threads` WHERE `receiver_id` = '1' or `sender_id` = '1'");

$threads = $query->result_array();

foreach ($threads as $thread)
{
$query2 = $this->db->query("SELECT * FROM `message_messages` WHERE `thread_id` = ".$this->db->escape($thread['thread_id'])." ORDER BY `timestamp` DESC LIMIT 1);
$messages = $query2->result_array();
}

And then somehow add these results to the array that has the thread information in it.

evenstar7139

Anybody know what to do?

NogDog

Eh...my brain is way too fried now to try to figure this out -- but generally speaking, looping in PHP to generate an open-ended number of queries is not a preferred solution.

If it's not too much of a hassle and you don't mind posting it here, could you do a dump of the DB tables involved (just the structure) and paste the resulting SQL here? That way if I have the time (and energy) I can build it locally and try a few things out. It might also help to re-cap exactly what you want to pull out of the DB, how it should be sorted, grouped, etc., so I don't have to take my lazy butt through the whole thread again.

evenstar7139

message_threads table:

-- phpMyAdmin SQL Dump
-- version 3.4.11.1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Mar 13, 2013 at 05:35 AM
-- Server version: 5.5.30
-- PHP Version: 5.2.17

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `website_main`
--

-- --------------------------------------------------------

--
-- Table structure for table `message_threads`
--

DROP TABLE IF EXISTS `message_threads`;
CREATE TABLE IF NOT EXISTS `message_threads` (
  `thread_id` int(20) NOT NULL AUTO_INCREMENT,
  `title` varchar(80) NOT NULL,
  `creator_id` int(20) NOT NULL,
  `receiver_id` int(20) NOT NULL COMMENT 'receiver is who received the message when the thread was first created',
  `creator_status` enum('read','unread') NOT NULL DEFAULT 'unread',
  `receiver_status` enum('read','unread') NOT NULL DEFAULT 'unread' COMMENT 'receiver is who received the message when the thread was first created',
  PRIMARY KEY (`thread_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `message_threads`
--

INSERT INTO `message_threads` (`thread_id`, `title`, `creator_id`, `receiver_id`, `creator_status`, `receiver_status`) VALUES
(1, 'A test title', 1, 36, 'unread', 'unread'),
(2, 'This is a second message and I am making my title long. ! ! ! ! ! ! ! ', 36, 1, 'unread', 'unread');

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

message_messages table:

-- phpMyAdmin SQL Dump
-- version 3.4.11.1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Mar 13, 2013 at 05:32 AM
-- Server version: 5.5.30
-- PHP Version: 5.2.17

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `website_main`
--

-- --------------------------------------------------------

--
-- Table structure for table `message_messages`
--

DROP TABLE IF EXISTS `messages_messages`;
CREATE TABLE IF NOT EXISTS `messages_messages` (
  `reply_id` int(20) NOT NULL AUTO_INCREMENT,
  `thread_id` int(20) NOT NULL,
  `message` text NOT NULL,
  `timestamp` int(20) NOT NULL,
  `author_id` int(20) NOT NULL,
  `author_ip` int(20) NOT NULL,
  PRIMARY KEY (`reply_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

--
-- Dumping data for table `messages_messages`
--

INSERT INTO `messages_messages` (`reply_id`, `thread_id`, `message`, `timestamp`, `author_id`, `author_ip`) VALUES
(1, 1, 'I am writing a very long message, so I can test and see if I can get it to truncate down to 80 characters for display when somebody hovers over the title of the thread. So, I need to talk. Let''s see here. Steve is coming over tomorrow and I''m pretty excited about that. I wanna hug the **** out of him and not let go for a very long time. \r\n\r\nI was already missing him but I had a bad dream about him last night. The jist of the dream was that he''d cheated on me and there was pretty much nothing left to do but dump him. How could I keep an unfaithful boyfriend? Things would have never been the same. So, I was very sad in that dream. I woke up feeling very thankful that it wasn''t true. I don''t want to lose Steve!', 1360067405, 1, 1195237642),
(2, 2, 'Yep, here''s the message!', 1360073308, 36, 1195237642),
(3, 2, 'Hey Tester how''s it going?', 1360077593, 36, 1195237642),
(4, 2, 'I am doing quite swell! Thanks for asking! By the way do you know when there will be pictures added for the Dachshund breed?', 1360110473, 36, 1195237642),
(5, 1, 'Reply to thread #1', 1360127268, 1, 87867686),
(6, 1, 'Evenstar replying to thread #1', 1360456196, 1, 1232423432);

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Here's the last query I tried to use, and what happened:
http://www.webdeveloper.com/forum/showthread.php?272165-Need-Help-Joining-these-Queries&p=1248843&highlight=#post1248843