php - MySQL - Querying for unread messages along with mail messages -


i in need retrieve messages in 1 query name of users , in addition count of unread messages. query being used 1 below , name of user retrieved , remaining count of unread messages. how can unread messages email_message (message_status='u') query have information alongside other details.

select e.mail_no, e.biz_id, e.from_add, e.to_add, e.email_subject,         date_format(e.updated_date,'%d %b %y, %i:%i %p') date, e.mail_status,        concat(ufrom.user_first_name,' ',ufrom.user_last_name) u_name email e   left join user_config ufrom      on ufrom.user_id = e.from_add  left join user_config uto      on uto.user_id = e.to_add       e.from_add=: e.from_add   order max(e.updated_date) desc, ufrom.user_first_name desc  limit 0, 10

tables 

create table if not exists user_config(   user_id int unsigned not null,   user_first_name varchar(100) character set 'utf8' collate 'utf8_unicode_ci' not null,    user_last_name varchar(100) character set 'utf8' collate 'utf8_unicode_ci' not null,    primary key (user_id),   index idx_user_config_id1 (user_id asc) ) engine = innodb default character set = utf8 collate = utf8_unicode_ci;  create table if not exists email (  mail_no int unsigned not null,   biz_id varchar(35) character set 'latin1' collate 'latin1_bin' not null,   from_add int unsigned not null,  -- email_type eucp, agent id  to_add int unsigned default null, -- email_type eucp, candidate_id  email_subject varchar(75) character set 'utf8' collate 'utf8_unicode_ci' default null,   mail_status char(1) character set 'latin1' collate 'latin1_bin' default 'a',   updated_date datetime on update current_timestamp,  primary key (mail_no,biz_id),  index idx_email_id1 (from_add asc),  index idx_email_id2 (to_add asc),  index idx_email_id3 (biz_id asc),  index idx_email_id4 (mail_status asc),  index idx_email_id5 (email_subject asc),   index idx_email_id6 (updated_date asc)   ) engine = innodb default character set = utf8 collate = utf8_unicode_ci;

enter image description here

create table if not exists email_message (   message_no int unsigned not null,    biz_id varchar(35) character set 'latin1' collate 'latin1_bin' not null,    sender int unsigned default null,    message text character set 'utf8' collate 'utf8_unicode_ci' default null,    attachment varchar(255) character set 'latin1' collate 'latin1_bin' default null,    message_status char(1) character set 'latin1' collate 'latin1_bin' default 'u',    read_date datetime default null,    send_date datetime default current_timestamp,    primary key (message_no,biz_id),   index idx_email_message_id1 (sender asc),   index idx_email_message_id2 (message_status asc) ) engine = innodb default character set = utf8 collate = utf8_unicode_ci;

if have query table on own, use 

select count(*) unread_message_count email_message    biz_id= :bizid , message_status = 'u';

but struggling incorporate combined query given above. me on this?

enter image description here

lasted email recieved , count of unread (recieved).

  select e.mail_no, e.biz_id, e.from_add, e.to_add, e.email_subject,     date_format(e.updated_date,'%d %b %y, %i:%i %p') date, e.mail_status,    concat(ufrom.user_first_name,' ',ufrom.user_last_name) u_name,if(unread_message_count null,0,unread_message_count) email e   left join user_config ufrom  on ufrom.user_id = e.to_add left join (select count(*) unread_message_count,to_add email_message         inner join email using (biz_id) message_status = 'u' group to_add) t on t.to_add= e.to_add   e.to_add=:to_add order max(e.updated_date) desc, ufrom.user_first_name desc  limit 0, 10

emails sent , count of unread sent messages:

  select e.mail_no, e.biz_id, e.from_add, e.to_add, e.email_subject,     date_format(e.updated_date,'%d %b %y, %i:%i %p') date, e.mail_status,    concat(ufrom.user_first_name,' ',ufrom.user_last_name) u_name,if(unread_message_count null,0,unread_message_count) email e   left join user_config ufrom  on ufrom.user_id = e.from_add  left join (select count(*) unread_message_count,from_add email_message         inner join email using (biz_id) message_status = 'u' group from_add) t on t.from_add= e.from_add   e.from_add=:from_add order max(e.updated_date) desc, ufrom.user_first_name desc  limit 0, 10

edited: - added if count=null 0, , added left join ensure results show up.

edit 2: changed subquery count unread sent user.

edit 3: updated 1st query , added 2nd query depending on required results. should trick. want first one.


Comments

Post a Comment

Popular posts from this blog

c# - Validate object ID from GET to POST -

i have method allows user access comment have made on site. have post method lets them update comment. my normal solution send entire comment model through, let them update it, update on database when post back. involve sending commentid through in hiddenfor , can manipulated. how can verify commentid sent in get method same i'm getting in post , not able alter comment wish? how can verify commentid sent in method same i'm getting in post? basically have validate following things in post - user logged-in user. authenticated users post comments. commentid in post , should valid commentid , should present in database. userid associated logged-in user should same userid associated comment . comment should contain userid column, can check @ time of update. to make sure update happens comment has been sent in get - hold commentid in session , in post action compare commentid value in session .
Read more

node.js - Custom Model Validator SailsJS -

when using custom model validator checks record within model. return within spread doesn't seem end control flow. //post.js model /** * custom validator **/ types: { isuservalid: function(user_id) { var promise = require('bluebird'); promise.all([ user.findone({id: user_id}) ]) .spread(function(user) { console.log(user); if (user === null || user === undefined) { console.log('failed'); return false; }else{ console.log('passed'); return true; } }); } }, my response standard validation failed response. { "error": "e_validation", "status": 400, "summary": "1 attribute invalid", "model": "post", "invalidattributes": { "owner": [ { "rule": "isuservalid", ...
Read more

php - Find a regex to take part of Email -

i have following emails need extract part from: bestellnummer xxx: 1 von xxx, 1er pack ------------- anfang der nachricht ------------- foo bar baz foo bar baz // <<<<< need text here ------------- ende der nachricht ------------- ------------- anfang der nachricht ------------- foo bar baz foo bar baz ------------- ende der nachricht ------------- there 0 unlimited occurences of ------------- anfang der nachricht ------------- ------------- ende der nachricht ------------- and i'm able extract first part regex: $re = "/------------- .*? -------------.?(.*?).?------------- .*? -------------/s"; but, i'm quite new on learning regex, i'm pretty sure there must better regex extract part (foo bar baz foo bar baz) of text between ------------- anfang der nachricht ------------- ------------- ende der nachricht ------------- as can in different languages, i'm using .? to match between hyphens. i...
Read more