| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
|
06-20-08, 13:54
|
|
Registered User
|
|
Join Date: Jun 2007
Location: London
Posts: 117
|
|
|
Threaded Emails
|
|
Hello all,
I want to make a messaging system where emails sent are linked, like the way google/yahoo do theirs. So i send an email and then you reply, i want the email i sent and the one you sent to be linked so that I am able to make them into a thread.
Is this design correct? Any more i can do?
http://img157.imageshack.us/my.php?i...bdesignxa1.png
Thanks all 
|
|
06-20-08, 23:49
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,524
|
|
what's a convo?
those tables look too similar to be separate tables
|
|
06-20-08, 23:49
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,524
|
|
|
|
what's a convo?
those tables look too similar to be separate tables
|
|
06-21-08, 09:08
|
|
Registered User
|
|
Join Date: Jun 2007
Location: London
Posts: 117
|
|
Hi r937,
Apologies for my terrible slang! Convo is supposed to be short for conversation.
My thinkng behind this is that I have a table ( message) which holds all the first messages and then another table ( convo) which holds all the other messages which are linked to the message table by the msgID.
Actually thinking about this again, how will i know the order of the conversation? I can compute it by the date field but is there another way i can do this naturally through a good db design?
Thanks
|
|
06-21-08, 09:33
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,524
|
|
sure, there's another way, just have the reply link to the email it's replying to using its id
and they can both be in the same table
|
|
06-21-08, 17:44
|
|
Registered User
|
|
Join Date: Jun 2007
Location: London
Posts: 117
|
|
Oh cool! Thanks for that. It will work like that but there is still a fair bit of coding to determine which message is linked to which reply.
I mean if we picked any message (say 1) then i would look in the replyfield to see if it had any replies, if it did then go find that mesage and do the same again. No question this will work, but if the table gets really large then it will be really inefficient right?
We could break it down, but the only way i thought was the above and it was a bit silly.
|
|
06-21-08, 19:13
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,524
|
|
here is a suggestion
read the following article, and where is says "category" i want you to think email, and where it says "subcategory" i want you to think reply
other than these names, it is exactly the same database structure, and you will be using the exact same queries
--> Categories and Subcategories
|
|
06-28-08, 08:39
|
|
Registered User
|
|
Join Date: Jun 2007
Location: London
Posts: 117
|
|
I am not ignoring you, that was a great article, looks like its exactly what i need. I have also been reading some of the resources after the article, they spoke about alternatives but I didn't understand them!
Writing the SQL seems complicated even though I have an example, but I am learning about LEFT OUTER JOINS now so that hopefully it becomes easier.
|
|
06-28-08, 14:30
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,524
|
|
don't forget that when you try it out, and if you have problems, you can always come back here for help
|
|
07-03-08, 06:28
|
|
Registered User
|
|
Join Date: Jun 2007
Location: London
Posts: 117
|
|
Table test
| msgID | msg | reply ID |
--------------------------
| 1 | m | 0 |
--------------------------
| 2 | m | 1 |
--------------------------
| 3 | m | 2 |
--------------------------
| 4 | m | 3 |
--------------------------
| 5 | m | 0 |
--------------------------
| 6 | m | 5 |
Code:
SELECT tester.msgID as msg_ID,
up1.msgID as up1_msgID,
up2.msgID as up2_msgID,
up3.msgID as up3_msgID
from test as tester
left outer
join test as up1
on up1.msgID = tester.replyID
left outer
join test as up2
on up2.msgID = up1.replyID
left outer
join test as up3
on up3.msgID = up2.replyID
where tester.msgID = 2
order by msg_ID
After reading joins i attempted to customise the sql under the
"The path to the root: the breadcrumb trail" section. It works great.
But I want the above to able to search both up and down so that all messages
retrieved no matter which msg the user clicks on.
I would be very grateful for more help.
|
|
07-03-08, 07:15
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,524
|
|
Quote:
|
Originally Posted by compsci
But I want the above to able to search both up and down so that all messages
retrieved no matter which msg the user clicks on.
|
could you explain how both up and down would be used together in a real situation?
|
|
07-03-08, 07:28
|
|
Registered User
|
|
Join Date: Jun 2007
Location: London
Posts: 117
|
|
Quote:
|
Originally Posted by r937
could you explain how both up and down would be used together in a real situation?
|
It is most likely that my understanding is flawed.
But for example say you have a messaging system: A B C D E - this is the series of the convo (letters are people and their email) - say that person C clicked on a old notification that an email was sent to them. I need to be able to get the past messages and the messages that followed after that - this is why i need an up and down search to get complete list.
On my previous post, where i drew the table - if I used php to call the msgID 3 and all related messages - i need to get messages 1,2,3,4.
|
|
07-03-08, 07:39
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,524
|
|
SELECT columns FROM (
query going up
UNION ALL
query going down
) AS both
|
|
07-03-08, 11:00
|
|
Registered User
|
|
Join Date: Jun 2007
Location: London
Posts: 117
|
|
The above looks good to me, but before i even reached the stage of using it, I got stuck in trying to do the statement for going down.
Referring back to your great article  - It says I have to specify the number of levels to go down (Under the section: "Displaying all categories and subcategories: site maps and navigation bars"), but it is variable - so how do I make the SQL understand this?
|
|
07-03-08, 11:18
|
|
Registered User
|
|
Join Date: Jun 2007
Location: London
Posts: 117
|
|
Before I even try the concept I - my sql is crap! Is there a syntax error here?
Code:
SELECT msgID, msg, replyID FROM (
SELECT tester.msgID as msg_ID,
up1.msgID as up1_msgID,
up2.msgID as up2_msgID,
up3.msgID as up3_msgID
from test as tester
left outer
join test as up1
on up1.msgID = tester.replyID
left outer
join test as up2
on up2.msgID = up1.replyID
left outer
join test as up3
on up3.msgID = up2.replyID
UNION ALL
select root.msgID as root_msgID
, down1.msgID as down1_msgID
, down2.msgID as down2_msgID
, down3.msgID as down3_msgID
from test as root
left outer
join test as down1
on down1.replyID = root.msgID
left outer
join test as down2
on down2.replyID = down1.msgID
left outer
join test as down3
on down3.replyID = down2.msgID
where root.replyID is null
) AS both
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Threaded Emails
