Page 1 of 2

Ordering by a different table?

Posted: Sun Sep 30, 2007 3:50 pm
by Smasher

Code: Select all

	SELECT t.id FROM `Thread` t 
			JOIN `Post` p ON (p.thread_id = t.id)
			WHERE t.`forum_id` = $id 
			GROUP BY p.`thread_id` 
			ORDER BY p.`updated` DESC
Basically, Im returning a list of threads per forum via a query, however the list of threads should be returned by the post updated time.

However it seems this query just returns the thread ids either asc or desc and doesnt use the p.updated asc/desc

Anyone have an idea of how I'm doing this wrong?

edit, technically I'd need two orders, one order to order the posts and then another to order the thread, however they'd both be the updated field

Posted: Sun Sep 30, 2007 4:37 pm
by superdezign
Try removing the GROUP BY clause.

Posted: Sun Sep 30, 2007 4:43 pm
by Smasher
Makes no difference :s

Posted: Sun Sep 30, 2007 5:06 pm
by superdezign
From the look of your query, you're just... Doing it wrong. There's multiple posts per thread, right? Your join accounts for the first post for each thread, when you probably want the last post.

Posted: Mon Oct 01, 2007 4:20 am
by Smasher
Thats right, thats what I did the orderby updated bit.

I know I'm doing it wrong, I just cant figure out whats the right way todo it.

Posted: Tue Oct 02, 2007 5:09 pm
by Smasher
Do you have an advise on how todo it right?

Posted: Wed Oct 03, 2007 4:40 pm
by ReverendDexter
Okay, I think this is what you're wanting:

Code: Select all

SELECT t.id
FROM thread AS t, post AS p
WHERE p.thread_id = t.id
GROUP BY p.thread_id
ORDER BY p.updated DESC
That *should* give you the thread IDs in reverse chronological order (newest first). If that's not right, trying changing the group by to t.id instead of p.thread_id.

Hope it helps!

Posted: Fri Oct 05, 2007 3:39 am
by Smasher
hmm it still ordered by the thread id :/

Posted: Fri Oct 05, 2007 11:21 am
by ReverendDexter
okay, what did you get when you pull out the "group by p.thread_id"?

Posted: Fri Oct 05, 2007 11:24 am
by feyd
Can you post the table structures (CREATE TABLE ....) some sample data and the expect output for said data?

Posted: Fri Oct 05, 2007 4:04 pm
by Smasher
feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


[quote="ReverendDexter"]okay, what did you get when you pull out the "group by p.thread_id"?[/quote]

The same


[quote="feyd"]Can you post the table structures (CREATE TABLE ....) some sample data and the expect output for said data?[/quote]

Sure, here

[syntax="sql"]CREATE TABLE `Post` (
  `id` int(8) NOT NULL auto_increment,
  `post` text NOT NULL,
  `user_id` int(8) NOT NULL default '0',
  `thread_id` int(8) NOT NULL default '0',
  `smilies` int(1) NOT NULL default '0',
  `ip` varchar(30) NOT NULL default '',
  `updated` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `created` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`id`),
  FULLTEXT KEY `post` (`post`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=24 ;

-- 
-- Dumping data for table `Post`
-- 

INSERT INTO `Post` VALUES (1, 'This is the first post.', 1, 1, 1, '81.179.79.90', '2007-09-04 22:20:38', '2007-09-04 22:20:38');
INSERT INTO `Post` VALUES (2, 'This is the second post.', 7, 1, 1, '86.131.249.69', '2007-09-05 16:05:35', '2007-09-05 16:05:35');
INSERT INTO `Post` VALUES (3, 'testers', 1, 10, 1, '86.20.130.212', '2007-11-30 16:52:59', '2007-09-30 16:52:22');
INSERT INTO `Post` VALUES (4, 'That ring tone innit', 1, 11, 1, '86.20.130.212', '2007-09-30 19:13:22', '2007-09-30 19:13:22');
INSERT INTO `Post` VALUES (5, 'Special Ring UP!', 1, 11, 1, '86.20.130.212', '2007-09-30 19:39:49', '2007-09-30 19:39:49');
INSERT INTO `Post` VALUES (6, 'Tester Up!', 1, 2, 1, '86.20.130.212', '2007-09-30 19:40:23', '2007-09-30 19:40:23');
INSERT INTO `Post` VALUES (7, 'Why am I not up? ', 1, 11, 1, '86.20.130.212', '2007-09-30 19:40:35', '2007-09-30 19:40:35');
INSERT INTO `Post` VALUES (23, 'rfgrftg', 1, 10, 1, '86.20.130.212', '2007-10-04 18:19:05', '2007-10-04 18:19:05');
INSERT INTO `Post` VALUES (22, 'make me1', 1, 11, 1, '86.20.130.212', '2007-10-04 18:18:55', '2007-10-04 18:18:55');
INSERT INTO `Post` VALUES (21, 'godamit', 1, 10, 1, '86.20.130.212', '2007-10-04 18:18:20', '2007-10-04 18:18:20');
INSERT INTO `Post` VALUES (20, 'im ere', 1, 11, 1, '86.20.130.212', '2007-10-04 18:18:05', '2007-10-04 18:18:05');
INSERT INTO `Post` VALUES (19, 'im here', 1, 11, 1, '86.20.130.212', '2007-10-04 18:17:59', '2007-10-04 18:17:59');
INSERT INTO `Post` VALUES (18, 'Im new', 1, 11, 1, '86.20.130.212', '2007-09-30 21:34:38', '2007-09-30 21:34:38');
INSERT INTO `Post` VALUES (17, 'TEster should move up', 1, 10, 1, '86.20.130.212', '2007-09-30 21:32:54', '2007-09-30 21:32:54');


-- 
-- Table structure for table `Thread`
-- 

CREATE TABLE `Thread` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(100) NOT NULL default '',
  `forum_id` int(10) NOT NULL default '0',
  `icon_id` int(8) NOT NULL default '0',
  `views` int(8) NOT NULL default '0',
  `posts` int(8) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=12 ;

-- 
-- Dumping data for table `Thread`
-- 

INSERT INTO `Thread` VALUES (1, 'Pete Burns', 1, 1, 233, 41);
INSERT INTO `Thread` VALUES (2, 'Dorethy', 1, 2, 18, 55);
INSERT INTO `Thread` VALUES (10, 'tester', 3, 1, 40, 9);
INSERT INTO `Thread` VALUES (11, 'Special Ring', 3, 1, 33, 12);


-- 
-- Table structure for table `Forum`
-- 

CREATE TABLE `Forum` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(100) NOT NULL default '',
  `description` text NOT NULL,
  `icon` int(11) NOT NULL default '0',
  `category_id` int(8) NOT NULL default '0',
  `order` tinyint(4) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

-- 
-- Dumping data for table `Forum`
-- 

INSERT INTO `Forum` VALUES (1, 'General Discussion', 'A place to shoot from the hip', 1, 2, 1);
INSERT INTO `Forum` VALUES (2, 'Off Topic Banter', 'A place to spam away like there is no tomorrow.', 1, 2, 2);
INSERT INTO `Forum` VALUES (3, 'Forum Discussion', 'A place to discuss Wuggawoo and its development.', 3, 1, 2);
INSERT INTO `Forum` VALUES (4, 'News Discussion', 'A place to discuss serious news articles.', 1, 1, 1);

feyd | Please use[/syntax]

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]

Posted: Fri Oct 05, 2007 7:23 pm
by feyd
What about the expected output of the final query? (Or an approximation, as best you can mock up.)

Posted: Sat Oct 06, 2007 3:53 am
by Smasher
http://wuggawoo.co.uk/betaForum/?page=f ... =view&id=3

At the moment it orders by the id asc or desc *depending whats put at the end of the query* whereas it should order by the last post.

Posted: Thu Oct 11, 2007 4:43 am
by Smasher
Did anyone look into this? I could provide some code if it'd help test.

Posted: Wed Oct 24, 2007 4:54 pm
by Smasher
Bumpen :)