Joins

[tomcant]

I'm currently designing a forum style application and I'm having some trouble retrieving the information I want from the database. I have tables called "posts" and "users". When displaying the forums threads I want to retrieve information about the user who created each of them, and the user who posted the last reply. I have a "last_id" field in the posts table which references the id of the last user to reply. Here is the SQL I tried:

 

SELECT * FROM posts AS Post
LEFT JOIN users AS User ON (Post.user_id = User.id)
LEFT JOIN users AS LastUser ON (Post.last_id = LastUser.id)
WHERE parent_id IS NULL ORDER BY Post.created desc

 

This returns the correct data on the first join, but the second comes back blank. Here is what I mean:

 

Array
(
  [0] => Array
    (
      [Post] => Array
        (
          [id] => 279
          [parent_id] => 
          [user_id] => 2
          [last_id] => 282
          [title] => Major update
          [body] => blah blah blah
          [created] => 2009-12-26 16:56:07
        )

      [user] => Array
        (
          [id] => 2
          [username] => tomcant
          [password] => 8da78cf8f91c5755208eeaed0652f5c4aaf86d43
          [group_id] => 4
          [created] => 2009-12-29 06:50:59
          [logged] => 0
          [lastactive] => 2009-12-29 06:50:59
        )

      [LastUser] => Array
        (
          [id] => 
          [username] => 
          [password] => 
          [group_id] => 
          [created] => 
          [logged] => 
          [lastactive] => 
        )
    )
)

 

I noticed that if I change the second join to be a "right join" then it does retrieve the correct data for the last user to reply, but the other data (Post and User) are blank.

 

I would really appreciate some help here. Thanks.

[ignace]

Are you sure your lastuser column has a value for the second post?

[tomcant]

Are you sure your lastuser column has a value for the second post?

Sorry ignace, I'm not sure what you're asking here. Could you elaborate?

[tomcant]

Oh crap! My mistake. It's been a long morning! "last_id" actually references the id of the last post, not the last user. Sorry for wasting your time, ignace.

 

However, perhaps you can help me further. I changed the SQL to:

 

SELECT * FROM posts AS Post
LEFT JOIN users AS User ON (Post.user_id = User.id)
LEFT JOIN posts AS LastPost ON (Post.last_id = LastPost.id)
WHERE Post.parent_id IS NULL ORDER BY Post.created desc

 

... and this returns all the correct data. When displaying the threads to the user I want to display the last user to reply AS WELL AS the time of the post. I have the time, I don't have the user. Is there a way to do get this data in the same SQL statement? I know I can get it using a second statement because we have the id of the last user now.

 

Sorry to have been a pain!

[tomcant]

I figured it out. Here's the new SQL statement:

 

SELECT * FROM posts AS Post
LEFT JOIN users AS User ON (Post.user_id = User.id)
LEFT JOIN posts AS LastPost ON (Post.last_id = LastPost.id)
LEFT JOIN users AS LastPostUser ON (LastPostUser.id = LastPost.user_id)
WHERE Post.parent_id IS NULL ORDER BY Post.created desc

 

It just needed one more join. I don't know if this is a performance bottle-neck or not. Does anyone have an opinion? It would be really good if I could just retrieve the relevant fields from the joins. In the second join I'm only interested in "posts.created" and in the third I'm only interested in "users.username". Does syntax exist for this?

[ignace]

Can you post a db dump of the relevant tables?

 

CREATE TABLE table (
    column ...
);

[tomcant]

Thanks for replying. Here are the dumps.

 

CREATE TABLE `posts` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `parent_id` int(10) default NULL COMMENT 'id of the post to which this post is a reply.',
  `user_id` int(10) unsigned NOT NULL COMMENT 'id of the user who created this post',
  `last_id` int(10) unsigned default NULL COMMENT 'id of the last reply in this thread (null if this post is not a thread)',
  `title` varchar(100) default NULL,
  `body` text NOT NULL,
  `created` datetime NOT NULL,
  `num_replies` int(3) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=316 DEFAULT CHARSET=latin1;

CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `username` varchar(45) NOT NULL,
  `password` varchar(45) NOT NULL,
  `group_id` smallint(5) unsigned NOT NULL,
  `created` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `logged` smallint(5) unsigned NOT NULL,
  `lastactive` timestamp NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;