Grouped SQL / CFQuery Problem

jtanner
jtanner used Ask the Experts™
on
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?
===========
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

Open in new window

ishot-3.jpg
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2015

Commented:
> ORDER BY msgid DESC
> <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 ...

Author

Commented:
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
Most Valuable Expert 2015

Commented:
> 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.

Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Author

Commented:
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
Most Valuable Expert 2015

Commented:
> 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.
Most Valuable Expert 2015
Commented:
If you only want the "newest" message in each thread, you'll need a different query.  Ignoring the extra WHERE filters, something like

SELECT  m.*
FROM     tbl_Messages m  INNER JOIN
             (
                SELECT thread_id, MAX(TS) AS MaxDate
                FROM   tbl_Messages
                WHERE ....
                GROUP BY thread_id
             )
            mx ON mx.thread_id = m.thread_id AND mx.MaxDate = m.TS
WHERE  ....

Author

Commented:
Hi _agx_

It started here:

http://www.experts-exchange.com/Software/Server_Software/Web_Servers/ColdFusion/Q_24980084.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

Author

Commented:
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.
<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>

Open in new window

ishot-2.jpg
ishot-1.jpg

Author

Commented:
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
Most Valuable Expert 2015

Commented:
> does that look good to you?

   Yep, that looks right.  All that was missing was the final ORDER BY  date! :)

Author

Commented:
Thank you very much for your help _agx_ :)
Most Valuable Expert 2015

Commented:
Anytime :)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial