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

[eluser]ehicks727[/eluser]
Something is telling me you need to do a sub-query. Because it sounds like you only want the conversation with the latest create date, right? So there could be more than one conversation per user, tied to a conversation post, but you only want the last one. I just don't know how to get that in a single SELECT query.
#12

[eluser]fesweb[/eluser]
EDIT: never mind, I misread my test results...this won't work at all....
Did you try ordering by the same thing you're grouping by first?
Code:
GROUP BY conversations_posts.conversationid
ORDER BY conversations_posts.conversationid, conversations_posts.created DESC
I think that might be all that's required...
#13

[eluser]ehicks727[/eluser]
AH, I think I was on the right track with the HAVING statement. You can't do a GROUP BY and a WHERE on an aggregate function (picking the latest conversation). I just didn't do a function in my statement above.

Try this

Code:
SELECT conversations.* FROM conversations
JOIN conversations_posts ON conversations.conversationid = conversations_posts.conversationid
GROUP BY conversations_posts.conversationid
HAVING MAX(conversations.created)
ORDER BY conversations_posts.created DESC
#14

[eluser]uptime[/eluser]
Why are you using two different tables?

You could have them all under one table with a parent_id field. if parent_id = 0 it would be considered as a new thread. Otherwise set parent_id to the thread's ID. I think it would make your life a little easier. Unless you have another reason, do you?
#15

[eluser]ehicks727[/eluser]
[quote author="uptime" date="1219782902"]Why are you using two different tables?

You could have them all under one table with a parent_id field. if parent_id = 0 it would be considered as a new thread. Otherwise set parent_id to the thread's ID. I think it would make your life a little easier. Unless you have another reason, do you?[/quote]

Because that wouldn't be a properly normalized table. Actually, it's not normalized right now because the userid is in both tables, but that's not that big of a deal.

If you threw this all in one table, you'd most likely be painting yourself into a corner. It'll bite you in the butt later when you need to perform more complex manipulations or reporting on the data. As much normalization as is reasonable is always a best practice.
#16

[eluser]Dan Murfitt[/eluser]
Thanks for your help everyone Smile

@mdowns - I had considered a lastpost field, as this would definitely do the trick, but I wanted to keep the data normalised as much as possible i.e. by not duplicating any data.

@ehicks727 - Sorry, they're still appearing in the order that the conversations were created in, not the order in which the last posts were created (the order of conversationid should be 2, 4, 3, 1).

@fesweb - I did try that, but the problem seems to be that the group by is always run before the order by. This means that the correct results are removed from the group then the final resultset, although ordered correctly, isn't correct. I guess this is why I need the sub-query.

@uptime and ehicks727 - Yup, I wanted to keep things normalised. I do agree, the userid doesn't need to be included in the conversation, as it can be obtained from the posts. My rationale for this was if the first post was deleted, I would still want to know who started the conversation.

Thanks again to everyone, this is the first real tricky thing I have run into with my CodeIgniter app and I'm glad that the support on the forum is so helpful and friendly Smile Here's some code which seems to work. Conversationid 2 appears at the top because it has the latest created post referenced to it from the conversations_posts table:

Code:
SELECT conversations.*
FROM (SELECT * from conversations_posts ORDER BY created DESC) AS conversations_posts
JOIN conversations ON conversations_posts.conversationid = conversations.conversationid
WHERE conversations.teamid = 1
GROUP BY conversationid
ORDER BY conversations_posts.created DESC
#17

[eluser]uptime[/eluser]
[quote author="ehicks727" date="1219784753"][quote author="uptime" date="1219782902"]Why are you using two different tables?

You could have them all under one table with a parent_id field. if parent_id = 0 it would be considered as a new thread. Otherwise set parent_id to the thread's ID. I think it would make your life a little easier. Unless you have another reason, do you?[/quote]

Because that wouldn't be a properly normalized table. Actually, it's not normalized right now because the userid is in both tables, but that's not that big of a deal.

If you threw this all in one table, you'd most likely be painting yourself into a corner. It'll bite you in the butt later when you need to perform more complex manipulations or reporting on the data. As much normalization as is reasonable is always a best practice.[/quote]

This is pretty interesting, I really don't see why should I (or anyone else) use more than one table for that.

Why would reports be hard? It's a simple SELECT FROM WHERE statement so all you would need to do is to do something like this:
Code:
WHERE ... AND post_type = 'thread'
Or like that:
Code:
WHERE ... AND parent_id = '0'

Is there anyone else here thinks that this is not the right way? I would love to hear more about it, seriously.
#18

[eluser]mdowns[/eluser]
It's all about normalizing the data. Read more here.
#19

[eluser]uptime[/eluser]
[quote author="mdowns" date="1219865339"]It's all about normalizing the data. Read more here.[/quote]

That's very interesting, mdowns - thank you for the tip!

I would also assume that MySQL would have better caching when using more tables with JOINs rather than one long table (it ignores the cache once the table has been updated).

Thanks again :-)




Theme © iAndrew 2016 - Forum software by © MyBB