Link to home
Start Free TrialLog in
Avatar of jtanner
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?
===========
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
Avatar of _agx_
_agx_
Flag of United States of America image

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

Avatar of jtanner
jtanner

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

Avatar of jtanner

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
> 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.
ASKER CERTIFIED SOLUTION
Avatar of _agx_
_agx_
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of jtanner

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
Avatar of jtanner

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.
<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
Avatar of jtanner

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
> does that look good to you?

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

Avatar of jtanner

ASKER

Thank you very much for your help _agx_ :)
Anytime :)