sql - Displaying messsage in specific format in mysql php -

-

i have created messaging system users allow users send message another.

for have created 2 tables.

  conversation(conversation_id,user_id1,user_id2)   messages(message_id,conversation_id,sender_id,receiver_id,message,created_time)

if users talking first time, conversation_id created user_id1(who initiate chat) , user_id2(to user_id1 sending message)

messages table contain info related message.

now want is, create message summary page logged in user can view conversation list between other users order created_time, group conversation_id.

here table data:

conversation_id | user_id1 | user_id2      1                100       103      2                101       103      3                103       102           message_id| conversation_id| sender_id| receiver_id| message | created_time    1             1            100       103           msg    2012-06-08 08:38:57    2             1            103       100           msg b    2012-06-08 08:39:40    3             2            101       103           msg c    2012-06-08 08:40:20    4             3            102       103           msg d    2012-06-08 08:41:10

and here output looking for: lets logged in user id: 103

conversation_id| conversation_with | last_message | created_time       3              102              msg d         2012-06-08 08:41:10       2              101              msg c         2012-06-08 08:40:20       1              100              msg b         2012-06-08 08:39:40

so output ordering created_time, grouping conversation_id , displaying id of user in conversation_with , userid 103 having conversation.

can provide mysql query need output.

this fun one. so, have 3 main things solve.

  1. alternate conversation_with column depending on desired user shows amongst 2 columns.
  2. greatest-n-per-group find recent message.
  3. least-n-per-group find earliest message created_time.

the first 1 solved querying twice, alternating user_id column, unioning results, , ordering unioned results created_time. think can solve 1 query , no unions, works now.

2 , 3 little more involved. here's sql fiddle query: http://sqlfiddle.com/#!2/bf2b7/1

tl;dnr

select * (     select c.conversation_id, c.user_id2 conversation_with, m1.message last_message, m3.created_time      conversation c       join messages m1 on c.conversation_id = m1.conversation_id       left outer join messages m2 on (c.conversation_id = m2.conversation_id                                           , (m1.created_time < m2.created_time or m1.created_time = m2.created_time , m1.message_id < m2.message_id))       join messages m3 on c.conversation_id = m3.conversation_id       left outer join messages m4 on (c.conversation_id = m4.conversation_id                                          , (m3.created_time > m4.created_time or m3.created_time = m4.created_time , m3.message_id > m4.message_id))     user_id1 = 103 , m2.message_id null , m4.message_id null   union     select c.conversation_id, c.user_id1 conversation_with, m1.message last_message, m3.created_time      conversation c       join messages m1 on c.conversation_id = m1.conversation_id       left outer join messages m2 on (c.conversation_id = m2.conversation_id                                           , (m1.created_time < m2.created_time or m1.created_time = m2.created_time , m1.message_id < m2.message_id))       join messages m3 on c.conversation_id = m3.conversation_id       left outer join messages m4 on (c.conversation_id = m4.conversation_id                                          , (m3.created_time > m4.created_time or m3.created_time = m4.created_time , m3.message_id > m4.message_id))     user_id2 = 103 , m2.message_id null , m4.message_id null                                         ) conversations order created_time desc

Comments

Post a Comment

Popular posts from this blog

django - How can I change user group without delete record -

-
on website user have 1 group. , user can change group. it's made user.groups.clear() and user.groups.add(new_group) but it's not efficient, because there 2 sql query: delete, insert. how can change group update query? user , group related each other using manytomanyfield . means intersection table exists relating both entities, , if don't specify model map (using through attribute) django creates 1 you. looking @ sources django.contrib.auth.models see that's case. fortunatly, can access intermediary model using through attribute of manager (in case, user.groups.through ). can use regular model. example: >>> alice = user.objects.create_user('alice', 'alice@example.com', 'alicepw') >>> employee = group.objects.create(name='employee') >>> manager = group.objects.create(name='manager') >>> alice.groups.add(employee) >>> alice.groups.all() [<group: employee...
Read more

java - Need to add SOAP security token -

-
i need set custom soap header attribute on jax-ws generated webservice client. case webservice calls must go through proxy server requiring specific token (recieved web request header) present in soap request header. e.g.: 1 carserviceservice service = null; 2 service = new carserviceservice(new url(url), new qname(qname); 3 carserviceendpoint port = service.getcarserviceport(); it seems in line 3 wsdl retrieved , call fails due missing security token. 1 point direction on how done? a detailed example has been mentioned here: creating , deploying jax-ws web service on tomcat 6 this article shows how create , use security token .
Read more

java - EclipseLink JPA Object is not a known entity type -

-
i have strange problem eclipselink , object want persist. have 1 object (keypointlistimpl) stores object keypointimpl in list. persisting keypointimpl objects works great if try persist keypointlistimpl object java.lang.illegalargumentexception says object keypointimpl isn't known entity type. here keypointimpl code: @entity @table(name="keypoints") public class keypointimpl implements keypoint { @id @generatedvalue(strategy = generationtype.identity) private int id; @enumerated(enumtype.string) private detectortype keypointtype; private float x; private float y; private float size; private float angle; private float response; private int octave; private int classid; ... } here keypointlistimpl code: @entity @table(name="keypointlists") public class keypointlistimpl implements keypointlist { @id @generatedvalue(strategy = generationtype.identity) private int id; @onetoone(cascade={ca...
Read more