jtanner
asked on
Grouped SQL / CFQuery Problem
I have a problem whereby i have a table that holds messages sent to and from individuals.
When they view their inbox or sent box i was the messages grouped by thread_id.
my query is: (ignoring it uses * instead of naming the columns)
<cfquery name="inbox" datasource="#dsn#" username="#dsnusername#" password="#dsnpassword#">
SELECT * FROM tbl_Messages WHERE msg_to=#session.userid# AND msg_deleted <> 1 ORDER BY msgid DESC
</cfquery>
<cfquery name="sentbox" datasource="#dsn#" username="#dsnusername#" password="#dsnpassword#">
SELECT * FROM tbl_Messages WHERE msg_from='#session.userid# ' AND msg_deleted_from <> 1 ORDER BY msgid DESC
</cfquery>
ive tried adding group by but it always errors about cannot group aggregates ?
Anyway, i output using
<cfoutput query="inbox" group="thread_id">
Now, this appeared to be working....... untill in a different month. (even though dares arent referenced)
Ive attached a screen shot that should help see what i mean.
The 2 messages user thread_id's on 1001 (sam jackson) & 1002 (deniro).
Any communication with jackson uses the same 1001 thread_id and deniro uses 1002. I expected to see the most recent at the top, i wasnt expecting it to "break" the results.
Any thoughts on what im doing wrong, or not doing as the case may be?
When they view their inbox or sent box i was the messages grouped by thread_id.
my query is: (ignoring it uses * instead of naming the columns)
<cfquery name="inbox" datasource="#dsn#" username="#dsnusername#" password="#dsnpassword#">
SELECT * FROM tbl_Messages WHERE msg_to=#session.userid# AND msg_deleted <> 1 ORDER BY msgid DESC
</cfquery>
<cfquery name="sentbox" datasource="#dsn#" username="#dsnusername#" password="#dsnpassword#">
SELECT * FROM tbl_Messages WHERE msg_from='#session.userid#
</cfquery>
ive tried adding group by but it always errors about cannot group aggregates ?
Anyway, i output using
<cfoutput query="inbox" group="thread_id">
Now, this appeared to be working....... untill in a different month. (even though dares arent referenced)
Ive attached a screen shot that should help see what i mean.
The 2 messages user thread_id's on 1001 (sam jackson) & 1002 (deniro).
Any communication with jackson uses the same 1001 thread_id and deniro uses 1002. I expected to see the most recent at the top, i wasnt expecting it to "break" the results.
Any thoughts on what im doing wrong, or not doing as the case may be?
===========
Table Data:
===========
msgid msg_to msg_from msg_subject msg_body msg_read msg_deleted msg_alerted thread_id msg_deleted_from TS
34 34 15 SUBJECT test message :) hope your well my friend 1 0 1 1001 0 1/28/2010 12:21:19 PM
35 15 34 Re: SUBJECT haha im good buddy, u? 1 0 1 1001 0 1/28/2010 12:31:30 PM
36 34 15 Re: SUBJECT yeah im good thanks, same old. New film (...) 1 0 1 1001 0 1/28/2010 12:33:19 PM
37 15 34 Re: SUBJECT sounds awesome! do you need a super cool (...) 1 0 1 1001 0 1/28/2010 12:34:12 PM
38 34 15 Re: SUBJECT erm, i dunno what to say erm haha 1 0 1 1001 0 1/29/2010 10:42:21 AM
39 34 15 Re: SUBJECT woo, ok, so now the "inline" reply works (...) 1 0 1 1001 0 1/29/2010 11:59:28 AM
40 34 15 Re: SUBJECT test tes gtholgfs 1 0 1 1001 0 1/29/2010 11:59:43 AM
41 15 34 Re: SUBJECT kiss it mo-fo 1 0 1 1001 0 1/29/2010 12:00:52 PM
42 34 15 Re: SUBJECT j;gfd.jbfd jkgjkdaf 1 0 1 1001 0 1/29/2010 12:01:48 PM
43 15 34 Re: SUBJECT fjkoghfdjo'gh esghs/ 1 0 1 1001 0 1/29/2010 12:02:13 PM
44 34 15 Re: SUBJECT hello mofo 1 0 1 1001 0 1/29/2010 12:02:50 PM
45 15 34 Re: SUBJECT tiurgkjdfghjkfdsbgjkfdbn,dgfbgdhsd 1 0 1 1001 0 1/29/2010 12:03:05 PM
46 34 15 Re: SUBJECT test 1 0 1 1001 0 1/29/2010 2:01:47 PM
47 15 33 Test Subject TEST TEST TEST TEST TEST TEST 1 0 1 1002 0 1/28/2010 12:37:00 PM
48 33 15 Test Subject you talking to me? 0 0 0 1002 0 2/1/2010 11:59:34 AM
49 15 34 Re: SUBJECT yo mofo hows tricks? 1 0 1 1001 0 2/1/2010 2:01:23 PM
51 34 15 Re: SUBJECT uyfhbj;l 1 0 0 1001 0 2/1/2010 3:18:20 PM
52 15 33 SUBJECT BODY 0 0 0 1002 0 2/1/2010 3:30:04 PM
================
TABLE STRUCTURE:
================
Name Data Type Length Precision Allow nulls Identity
msgid int False True
msg_to int True False
msg_from int True False
msg_subject text True False
msg_body text True False
msg_read int False False
msg_deleted int False False
msg_alerted int False False
thread_id int True False
msg_deleted_from int True False
TS datetime True False
ishot-3.jpg
ASKER
Hi _agx_
Thankyou for your response.... ok, I understand that causes a problem, so my question now is,
How can i arrange in the correct order but keep it grouped by Thread_ID?
I need to display the newest at the top
Thankyou for your response.... ok, I understand that causes a problem, so my question now is,
How can i arrange in the correct order but keep it grouped by Thread_ID?
I need to display the newest at the top
> How can i arrange in the correct order but keep it grouped by Thread_ID?
It depends on what you're thinking. You can't order by two things at once. So the closest you'll be able to get
is either ORDER BY Thread_id, (then date). Or order by the newest date in each thread. That's a little different.
I'm not sure which you're looking for.
It depends on what you're thinking. You can't order by two things at once. So the closest you'll be able to get
is either ORDER BY Thread_id, (then date). Or order by the newest date in each thread. That's a little different.
I'm not sure which you're looking for.
ASKER
Well, ok, i changed the SQL to:
<cfquery name="inbox" datasource="#dsn#" username="#dsnusername#" password="#dsnpassword#">
SELECT * FROM tbl_Messages WHERE msg_to=#session.userid# AND msg_deleted <> 1 ORDER BY Thread_ID ASC
</cfquery>
(screenie attached)
Now it groups correctly, but deniro should the FIRST in the list as its newer. Trouble was, when i tried:
<cfquery name="inbox" datasource="#dsn#" username="#dsnusername#" password="#dsnpassword#">
SELECT * FROM tbl_Messages WHERE msg_to=#session.userid# AND msg_deleted <> 1 ORDER BY Thread_ID ASC, TS DESC
</cfquery>
(TS being TimeStamp)
it goes back to duplicting.
also, if i do Thread_ID DESC it shows in the correct order, however the messsage shown is the oldest so thats not what i want either.
i may be stuck in the middle a bit im thinking!
ishot-1.jpg
<cfquery name="inbox" datasource="#dsn#" username="#dsnusername#" password="#dsnpassword#">
SELECT * FROM tbl_Messages WHERE msg_to=#session.userid# AND msg_deleted <> 1 ORDER BY Thread_ID ASC
</cfquery>
(screenie attached)
Now it groups correctly, but deniro should the FIRST in the list as its newer. Trouble was, when i tried:
<cfquery name="inbox" datasource="#dsn#" username="#dsnusername#" password="#dsnpassword#">
SELECT * FROM tbl_Messages WHERE msg_to=#session.userid# AND msg_deleted <> 1 ORDER BY Thread_ID ASC, TS DESC
</cfquery>
(TS being TimeStamp)
it goes back to duplicting.
also, if i do Thread_ID DESC it shows in the correct order, however the messsage shown is the oldest so thats not what i want either.
i may be stuck in the middle a bit im thinking!
ishot-1.jpg
> Trouble was, when i tried:
> (TS being TimeStamp)
But why are you using "group" here? That's typically used when you need to display a header (once) and then a bunch of related details records beneath it like
Produce (Category)
- Apples (in Produce Category...)
- Oranges
- Pears
I must be missing something. Because if you're just displaying all mails by thread and date, why do you need the group? Just ORDER and output them.
> (TS being TimeStamp)
But why are you using "group" here? That's typically used when you need to display a header (once) and then a bunch of related details records beneath it like
Produce (Category)
- Apples (in Produce Category...)
- Oranges
- Pears
I must be missing something. Because if you're just displaying all mails by thread and date, why do you need the group? Just ORDER and output them.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi _agx_
It started here:
https://www.experts-exchange.com/questions/24980084/CFQuery-ignore-duplicates.html
where someone suggested i used the group by in the query as way of grouping the results.
Ill have a go with your example and get back to you
It started here:
https://www.experts-exchange.com/questions/24980084/CFQuery-ignore-duplicates.html
where someone suggested i used the group by in the query as way of grouping the results.
Ill have a go with your example and get back to you
ASKER
Hi again _agx_
Your query worked much better.... and i think so nearlly does what id expect.
Ive attached 2 pictures, the inbox & the outbox. Now, the Inbox page at the time of example, is in the correct order, however the Sentbox is wrong, i would expect the message to Samuel Jackson to appear at the top as its more recent.
Ive added my current querys to the code part.
Is it a case of adding a order by somewhere? i tried but didnt work but most likely will be me being wrong as my SQL skills arent so strong.
ishot-1.jpg
Your query worked much better.... and i think so nearlly does what id expect.
Ive attached 2 pictures, the inbox & the outbox. Now, the Inbox page at the time of example, is in the correct order, however the Sentbox is wrong, i would expect the message to Samuel Jackson to appear at the top as its more recent.
Ive added my current querys to the code part.
Is it a case of adding a order by somewhere? i tried but didnt work but most likely will be me being wrong as my SQL skills arent so strong.
<cfquery name="inbox" datasource="#dsn#" username="#dsnusername#" password="#dsnpassword#">
SELECT m.* FROM tbl_Messages m INNER JOIN
(SELECT thread_id, MAX(TS) AS MaxDate FROM tbl_Messages WHERE msg_to=#session.userid# AND msg_deleted <> 1 GROUP BY thread_id)
mx ON mx.thread_id = m.thread_id AND mx.MaxDate = m.TS WHERE msg_to=#session.userid# AND msg_deleted <> 1
</cfquery>
<cfquery name="sentbox" datasource="#dsn#" username="#dsnusername#" password="#dsnpassword#">
SELECT m.* FROM tbl_Messages m INNER JOIN
(SELECT thread_id, MAX(TS) AS MaxDate FROM tbl_Messages WHERE msg_from='#session.userid#' AND msg_deleted_from <> 1 GROUP BY thread_id)
mx ON mx.thread_id = m.thread_id AND mx.MaxDate = m.TS WHERE msg_from='#session.userid#' AND msg_deleted_from <> 1
</cfquery>
ishot-2.jpgishot-1.jpg
ASKER
Actually, i think ive done it...
<cfquery name="inbox" datasource="#dsn#" username="#dsnusername#" password="#dsnpassword#">
SELECT m.* FROM tbl_Messages m INNER JOIN
(SELECT thread_id, MAX(TS) AS MaxDate FROM tbl_Messages WHERE msg_to=#session.userid# AND msg_deleted <> 1 GROUP BY thread_id)
mx ON mx.thread_id = m.thread_id AND mx.MaxDate = m.TS WHERE msg_to=#session.userid# AND msg_deleted <> 1 ORDER BY mx.MaxDate DESC
</cfquery>
<cfquery name="sentbox" datasource="#dsn#" username="#dsnusername#" password="#dsnpassword#">
SELECT m.* FROM tbl_Messages m INNER JOIN
(SELECT thread_id, MAX(TS) AS MaxDate FROM tbl_Messages WHERE msg_from='#session.userid# ' AND msg_deleted_from <> 1 GROUP BY thread_id)
mx ON mx.thread_id = m.thread_id AND mx.MaxDate = m.TS WHERE msg_from='#session.userid# ' AND msg_deleted_from <> 1 ORDER BY mx.MaxDate DESC
</cfquery>
Appears to work.... does that look good to you?
Many thanks again for all you help so far
<cfquery name="inbox" datasource="#dsn#" username="#dsnusername#" password="#dsnpassword#">
SELECT m.* FROM tbl_Messages m INNER JOIN
(SELECT thread_id, MAX(TS) AS MaxDate FROM tbl_Messages WHERE msg_to=#session.userid# AND msg_deleted <> 1 GROUP BY thread_id)
mx ON mx.thread_id = m.thread_id AND mx.MaxDate = m.TS WHERE msg_to=#session.userid# AND msg_deleted <> 1 ORDER BY mx.MaxDate DESC
</cfquery>
<cfquery name="sentbox" datasource="#dsn#" username="#dsnusername#" password="#dsnpassword#">
SELECT m.* FROM tbl_Messages m INNER JOIN
(SELECT thread_id, MAX(TS) AS MaxDate FROM tbl_Messages WHERE msg_from='#session.userid#
mx ON mx.thread_id = m.thread_id AND mx.MaxDate = m.TS WHERE msg_from='#session.userid#
</cfquery>
Appears to work.... does that look good to you?
Many thanks again for all you help so far
> does that look good to you?
Yep, that looks right. All that was missing was the final ORDER BY date! :)
Yep, that looks right. All that was missing was the final ORDER BY date! :)
ASKER
Thank you very much for your help _agx_ :)
Anytime :)
> <cfoutput query="inbox" group="thread_id">
It doesn't work because you're ordering the cfquery's by a different column than your cfoutput. The two MUST be the same or the output won't work properly. So you'll get weird results like you're seeing now ...