Welcome Guest, Not a member yet? Register   Sign In
Tricky SQL query
#1

[eluser]Dan Murfitt[/eluser]
Hi

I was wondering if someone could offer some help with a tricky SQL query - I'm writing a messaging system which operates in a similar way to a forum. Conversations are basically forum threads, and posts are posts within a thread. What I need to be able to do is get the conversations related to a specific team, ordered by the latest updated conversations (determined by the conversation with the latest post 'created'). So, here's the schema:

Conversations table:
Code:
CREATE TABLE `conversations` (
  `conversationid` int(11) NOT NULL auto_increment,
  `teamid` int(11) NOT NULL,
  `userid` int(11) NOT NULL,
  `created` int(11) NOT NULL,
  `topic` varchar(255) NOT NULL,
  PRIMARY KEY  (`conversationid`)
);

Code:
CREATE TABLE `conversations_posts` (
  `postid` int(11) NOT NULL auto_increment,
  `conversationid` int(11) NOT NULL,
  `userid` int(11) NOT NULL,
  `created` int(11) NOT NULL,
  `message` longtext NOT NULL,
  PRIMARY KEY  (`postid`)
);

Here's what I've tried so far (using example teamid 18):

Code:
SELECT conversations.* FROM conversations
JOIN conversations_posts ON conversations.conversationid = conversations_posts.conversationid
WHERE conversations.teamid = 18
GROUP BY conversations_posts.conversationid
ORDER BY conversations_posts.created DESC

This produces a strange result - as soon as I add the GROUP BY (like above) the order becomes broken. Without the GROUP BY they are in the correct order, but there are duplicates as it shows every conversation for every post.

Does anyone know how to remidy this? I just need the conversations, for a particular team, ordered by the latest 'created' date from the joined 'conversations_posts' table. I've tried lots of variations but I just can't get it working. Any help here appreciated Smile

Thanks
#2

[eluser]Sumon[/eluser]
Hopefully meet your requirement
Code:
SELECT conversations.*, conversations_posts.* FROM conversations
LEFT JOIN conversations_posts ON conversations.conversationid = conversations_posts.conversationid
WHERE conversations.teamid = 1
ORDER BY conversations_posts.postid DESC
LIMIT 0,1 //if you need last record only.
Or
Code:
SELECT conversations.*, conversations_posts.* FROM conversations
LEFT JOIN conversations_posts ON conversations.conversationid = conversations_posts.conversationid
WHERE conversations.teamid = 1
ORDER BY conversations_posts.created DESC
#3

[eluser]Dan Murfitt[/eluser]
Hi

Thanks for the swift reply Smile

This still produces duplicate results, as it's showing every conversation for every post - this is why I need the GROUP BY clause, so that it only returns one result for every conversationid. The problem is that the ORDER BY is performed after the GROUP BY clause. So, even though the ORDER BY is working (as the final result is ordered by the created field) the one post returned from the GROUP BY clause is incorrect. I hope this makes sense Confused

Any more ideas?
#4

[eluser]Sumon[/eluser]
If you remove GROUP BY AND use LIMIT then what result it produce? Make sure you use ORDER BY CLAUSE (either conversations_posts.created DESC or conversations_posts.postid DESC
#5

[eluser]Dan Murfitt[/eluser]
I need the GROUP BY so that the results only return unique conversations, otherwise it will return the conversation relating to every posts, and since there are many posts per conversations you get duplicate results.

I've done a bit more searching and I seem to have found a possible solution:

Code:
SELECT conversations.*,COUNT(*) AS posts
FROM (SELECT * from conversations_posts ORDER BY created DESC) AS conversations_posts
JOIN conversations ON conversations_posts.conversationid = conversations.conversationid
WHERE conversations.teamid = 18
GROUP BY conversationid
ORDER BY conversations_posts.created DESC

By doing this it seems I am able to run the order before the group as well as after.

Thanks for your help
#6

[eluser]ehicks727[/eluser]
Can you post the create with a couple lines of data for each table?

I could be wrong, but I didn't think it was correct to use an ORDER BY with a JOIN. Something is just sparking a memory about that for some reason.

If you post me a create with some data, I think I can probably figure this out for you.
#7

[eluser]ehicks727[/eluser]
ah, I take that back, yes you can use ORDER BY with a JOIN. Maybe you need to specify the JOIN, like a LEFT JOIN or something like that. I didn't really read the question closely enough... haven't had my coffee yet this morning Smile
#8

[eluser]Dan Murfitt[/eluser]
[quote author="ehicks727" date="1219776513"]ah, I take that back, yes you can use ORDER BY with a JOIN. Maybe you need to specify the JOIN, like a LEFT JOIN or something like that. I didn't really read the question closely enough... haven't had my coffee yet this morning Smile[/quote]

Thanks for your help Smile I think it was the group by which I am using incorrectly, as I'm using it to only show one result for each conversation. Here's an SQL dump from phpmyadmin which contains some sample data. This contains 4 conversations, which were created in order as their topic will suggest, but conversationid 2 has 2 posts. The second post in conversationid 2 was the last to be updated, so it should appear at the top of the resultset making the order 2, 4, 3, 1 (in accordance with the conversations_posts table, ordered by the created field).

If I only created a join (but no group by) there would be 2 results for conversationid 2, as there are two posts - so to limit this to 1 result per conversation I group the conversationid. Here's the SQL:

Code:
-- phpMyAdmin SQL Dump
-- version 2.11.7
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Aug 26, 2008 at 02:57 PM
-- Server version: 5.0.51
-- PHP Version: 5.2.6

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Database: `temp`
--

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

--
-- Table structure for table `conversations`
--

CREATE TABLE IF NOT EXISTS `conversations` (
  `conversationid` int(11) NOT NULL auto_increment,
  `teamid` int(11) NOT NULL,
  `userid` int(11) NOT NULL,
  `created` int(11) NOT NULL,
  `topic` varchar(255) NOT NULL,
  PRIMARY KEY  (`conversationid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=53 ;

--
-- Dumping data for table `conversations`
--

INSERT INTO `conversations` (`conversationid`, `teamid`, `userid`, `created`, `topic`) VALUES
(4, 1, 1, 6, 'Last conversation'),
(3, 1, 1, 5, 'Third conversation'),
(2, 1, 1, 4, 'Second conversation'),
(1, 1, 1, 3, 'First conversation');

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

--
-- Table structure for table `conversations_posts`
--

CREATE TABLE IF NOT EXISTS `conversations_posts` (
  `postid` int(11) NOT NULL auto_increment,
  `conversationid` int(11) NOT NULL,
  `userid` int(11) NOT NULL,
  `created` int(11) NOT NULL,
  `message` longtext NOT NULL,
  PRIMARY KEY  (`postid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=108 ;

--
-- Dumping data for table `conversations_posts`
--

INSERT INTO `conversations_posts` (`postid`, `conversationid`, `userid`, `created`, `message`) VALUES
(1, 1, 1, 3, 'Fusce et nulla eu arcu vehicula mollis. Aliquam leo. Curabitur convallis. Vestibulum tellus. Nulla odio ligula, eleifend quis, sollicitudin vehicula, vestibulum eu, sapien. Nunc ligula eros, euismod venenatis, auctor at, ultrices vel, elit. Maecenas feugiat arcu id lorem. Donec sodales consectetuer augue. Duis tortor ipsum, faucibus in, gravida sed, mattis sit amet, elit. Cras ut libero!'),
(2, 2, 1, 4, 'Cras in dolor in dolor mollis lobortis? Proin urna pede, convallis eu, mattis vehicula, fringilla et, lectus! Mauris a dolor in augue vehicula vulputate. Mauris rutrum, dui eu sodales eleifend, arcu diam vehicula ante, at pulvinar risus ipsum ac justo. Phasellus eget turpis? Aliquam vel magna ac sapien sagittis cursus. Nunc sit amet est vitae ligula ullamcorper congue. Vestibulum ante ipsum primis in faucibus orci luctus et ultrices posuere cubilia Curae; Phasellus vel mauris a nisl fringilla semper. Vivamus convallis mattis tortor. Aenean dolor lacus, interdum a, adipiscing a; pellentesque et, ipsum. Ut id urna vel metus imperdiet vehicula. Aenean ullamcorper elit non nulla. Morbi in pede ac libero malesuada eleifend!'),
(3, 3, 1, 5, 'Nullam dictum felis eu ligula. Integer faucibus, orci at accumsan ultricies, ipsum leo adipiscing dolor, vel laoreet diam nulla eu tortor? Integer eget est! Aliquam consequat quam sed justo. Nunc ut est. Phasellus purus velit, ultricies sed, sagittis vel, ultrices vitae, nunc? Duis pharetra felis quis libero. Suspendisse lobortis neque at massa. Proin eleifend imperdiet mauris? Ut tristique ante eget lorem. Suspendisse potenti. Sed non pede! Phasellus non turpis.'),
(4, 4, 1, 6, 'Nunc vehicula velit a arcu. Curabitur turpis diam, facilisis at; commodo quis, dapibus sed, elit. Pellentesque mattis! Maecenas purus enim; euismod et, vestibulum vitae, adipiscing in, sem! Suspendisse potenti. Maecenas massa. Maecenas pharetra, elit id venenatis varius, libero leo molestie dolor, sed aliquet urna nisl vel magna. Praesent auctor rutrum eros. Nulla felis neque; scelerisque cursus, ullamcorper ut, porttitor in, pede. Curabitur sem eros, pulvinar nec, vehicula vitae, sodales a; nulla.'),
(5, 2, 1, 7, 'Quisque viverra tellus non metus. Etiam ornare neque quis odio. Integer at nisl eu est sollicitudin vestibulum. Donec eget mi. Curabitur interdum leo non nibh. Proin pretium. Aliquam pulvinar vehicula diam. Fusce libero odio, dignissim eget, facilisis sit amet, rutrum vel, est. Pellentesque ligula felis, rutrum non, scelerisque sit amet, congue at, risus. Nullam diam ipsum, sodales at, tempus vitae, vulputate vitae, erat.');

Thanks again!
#9

[eluser]mdowns[/eluser]
Why not add a column to the conversations table called LastPost which would be a datetime? Each time you insert a row into the conversations_posts table, update the LastPost date in the conversation table (either manually or by creating a trigger). That would make the query a lot easier.
#10

[eluser]ehicks727[/eluser]
Does this producing the result you expect? Or is it the same as the WHERE... GROUP BY

Code:
SELECT conversations.* FROM conversations
JOIN conversations_posts ON conversations.conversationid = conversations_posts.conversationid
GROUP BY conversations_posts.conversationid
HAVING conversations.teamid = 1
ORDER BY conversations_posts.created DESC




Theme © iAndrew 2016 - Forum software by © MyBB