API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Jul 21st, 2017
54
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
MySQL 6.84 KB | None | 0 0
raw download clone embed print report
  1. /* Выборка входящих сообщений */
  2. SELECT
  3.     user_details.user_id,
  4.     user_details.first_name,
  5.     user_details.last_name,
  6.     user_details.hideme,
  7.     IF (user_details.hideme != 0, 0, IF (unix_timestamp()- unix_timestamp(user_details.lastaccess) < 300, 1, 0)) as online,
  8.  
  9.     UNIX_TIMESTAMP(private_messages.date) as date,
  10.     private_messages.to_user_id,
  11.     private_messages.from_user_id,
  12.     private_messages.subject,
  13.     private_messages.message,
  14.     private_messages.unread,
  15.     private_messages.message_id,
  16.     private_messages.message_type,
  17.  
  18.     (SELECT IF(avatar_history.is_del=1, 0, avatar_filename) FROM avatar_history WHERE avatar_history.user_id = private_messages.from_user_id AND avatar_history.user_type = 'user' ORDER BY avatar_history.creation_date DESC LIMIT 1) as avatar_filename
  19.  
  20. FROM
  21.     private_messages
  22.     LEFT JOIN user_details ON user_details.user_id = private_messages.from_user_id
  23. WHERE
  24.     private_messages.to_user_id = ?
  25.         AND
  26.     private_messages.del_to = 0
  27. ORDER BY private_messages.date desc
  28. LIMIT ?, ?
  29.  
  30.  
  31. /* Взять предыдущие сообщение */
  32. SELECT
  33.     to_user_id,
  34.     (UNIX_TIMESTAMP()-UNIX_TIMESTAMP(date)) as diff
  35. FROM
  36.     private_messages
  37. WHERE
  38.     message_id = (SELECT max(message_id) FROM private_messages WHERE from_user_id = ?)
  39.  
  40.  
  41. /* Выборка не прочитанных сообщений */
  42. SELECT
  43.     user_details.user_id,
  44.     user_details.first_name,
  45.     user_details.last_name,
  46.     IF (user_details.hideme != 0, 0, IF(unix_timestamp()- unix_timestamp(user_details.lastaccess) < 300, 1, 0)) as online,
  47.  
  48.     UNIX_TIMESTAMP(private_messages.date) as date,
  49.     private_messages.to_user_id,
  50.     private_messages.from_user_id,
  51.     private_messages.subject,
  52.     private_messages.message,
  53.     private_messages.unread,
  54.     private_messages.message_id,
  55.     private_messages.message_type,
  56.  
  57.     (SELECT IF(avatar_history.is_del=1, 0, avatar_filename) FROM avatar_history WHERE avatar_history.user_id = private_messages.from_user_id AND avatar_history.user_type = 'user' ORDER BY avatar_history.creation_date DESC LIMIT 1) as avatar_filename
  58. FROM
  59.     private_messages
  60.     LEFT JOIN user_details ON user_details.user_id = private_messages.from_user_id
  61. WHERE
  62.     private_messages.to_user_id = ?
  63.         AND
  64.     private_messages.del_to = 0
  65.         AND
  66.     private_messages.unread = 1
  67. ORDER BY private_messages.date desc
  68. LIMIT ?, ?
  69.  
  70.  
  71. /* Выборка рекомендаций */
  72. SELECT
  73.     user_details.user_id,
  74.     user_details.first_name,
  75.     user_details.last_name,
  76.     IF (user_details.hideme != 0, 0, IF(unix_timestamp()- unix_timestamp(user_details.lastaccess) < 300, 1, 0)) as online,
  77.    
  78.     UNIX_TIMESTAMP(private_messages.date) as date,
  79.     private_messages.to_user_id,
  80.     private_messages.from_user_id,
  81.     private_messages.subject,
  82.     private_messages.message,
  83.     private_messages.unread,
  84.     private_messages.message_id,
  85.     private_messages.message_type,
  86.    
  87.     (SELECT IF(avatar_history.is_del=1, 0, avatar_filename) FROM avatar_history WHERE avatar_history.user_id = private_messages.from_user_id AND avatar_history.user_type = 'user' ORDER BY avatar_history.creation_date DESC LIMIT 1) as avatar_filename
  88. FROM
  89.     private_messages
  90.     LEFT JOIN user_details ON user_details.user_id = private_messages.from_user_id
  91. WHERE
  92.     private_messages.to_user_id = ?
  93.         AND
  94.     private_messages.del_to = 0
  95.         AND
  96.     private_messages.message_type != 'normal'
  97. ORDER BY private_messages.date desc
  98. LIMIT ?, ?
  99.  
  100.  
  101. /* Выборка исходящих сообщений */
  102. SELECT
  103.     user_details.user_id,
  104.     user_details.first_name,
  105.     user_details.last_name,
  106.     IF (user_details.hideme != 0, 0, IF(unix_timestamp()- unix_timestamp(user_details.lastaccess) < 300, 1, 0)) as online,
  107.    
  108.     UNIX_TIMESTAMP(private_messages.date) as date,
  109.     private_messages.to_user_id,
  110.     private_messages.from_user_id,
  111.     private_messages.subject,
  112.     private_messages.message,
  113.     private_messages.unread,
  114.     private_messages.message_id,
  115.     private_messages.message_type,
  116.    
  117.     (SELECT IF(avatar_history.is_del=1, 0, avatar_filename) FROM avatar_history WHERE avatar_history.user_id = private_messages.to_user_id AND avatar_history.user_type = 'user' ORDER BY avatar_history.creation_date DESC LIMIT 1) as avatar_filename
  118. FROM
  119.     private_messages
  120.     LEFT JOIN user_details ON user_details.user_id = private_messages.to_user_id
  121. WHERE
  122.     private_messages.from_user_id = ?
  123.         AND
  124.     private_messages.del_from = 0
  125. ORDER BY private_messages.date desc
  126. LIMIT ?, ?
  127.  
  128.  
  129. /* Взять одно сообщение */
  130. SELECT
  131.     user_details.user_id,
  132.     user_details.first_name,
  133.     user_details.last_name,
  134.     IF (user_details.hideme != 0, 0, IF(unix_timestamp()- unix_timestamp(user_details.lastaccess) < 300, 1, 0)) as online,
  135.    
  136.     UNIX_TIMESTAMP(private_messages.date) as date,
  137.     private_messages.to_user_id,
  138.     private_messages.from_user_id,
  139.     private_messages.subject,
  140.     private_messages.message,
  141.     private_messages.unread,
  142.     private_messages.message_id,
  143.     private_messages.message_type,
  144.    
  145.     (SELECT IF(avatar_history.is_del=1, 0, avatar_filename) FROM avatar_history WHERE avatar_history.user_id = IF(private_messages.to_user_id = ?, private_messages.from_user_id, private_messages.to_user_id) AND avatar_history.user_type = 'user' ORDER BY avatar_history.creation_date DESC LIMIT 1) as avatar_filename
  146. FROM
  147.     private_messages
  148.     LEFT JOIN user_details ON user_details.user_id = IF(private_messages.to_user_id = ?, private_messages.from_user_id, private_messages.to_user_id)
  149. WHERE
  150.     (private_messages.to_user_id = ? OR private_messages.from_user_id = ?)
  151.         AND
  152.     private_messages.message_id = ?
  153.         AND
  154.     private_messages.message_type = 'normal'
  155.         AND
  156.     IF (private_messages.to_user_id = ?, private_messages.del_to, private_messages.del_from) = 0
  157. LIMIT 1
  158.  
  159.  
  160. /* Выборка истории сообщений */
  161. SELECT
  162.     user_details.user_id,
  163.     user_details.first_name,
  164.     user_details.last_name,
  165.    
  166.     UNIX_TIMESTAMP(private_messages.date) as date,
  167.     private_messages.to_user_id,
  168.     private_messages.from_user_id,
  169.     private_messages.subject,
  170.     private_messages.message,
  171.     private_messages.unread,
  172.     private_messages.message_id,
  173.     private_messages.message_type
  174. FROM
  175.     private_messages
  176.     LEFT JOIN user_details ON user_details.user_id = private_messages.from_user_id
  177. WHERE
  178.     (from_user_id = ? AND to_user_id = ? AND del_to = 0)
  179.         OR
  180.     (to_user_id = ? AND from_user_id = ?  AND del_from = 0)
  181. ORDER BY date desc
  182. LIMIT ?, ?
  183.  
  184.  
  185. /* Подсчет сообщений */
  186. SELECT count(*) as numMessages FROM private_messages WHERE to_user_id = ? AND del_to = 0
  187. SELECT count(*) as numMessages FROM private_messages WHERE to_user_id = ? AND del_to = 0 AND unread = 1
  188. SELECT count(*) as numMessages FROM private_messages WHERE from_user_id = ? AND del_from = 0
  189. SELECT count(*) as numMessages FROM private_messages WHERE (from_user_id = ? AND to_user_id = ? AND del_to = 0) OR (to_user_id = ? AND from_user_id = ?  AND del_from = 0)
  190. SELECT count(*) as numMessages FROM private_messages WHERE message_type != 'normal' AND to_user_id = ? AND del_to = 0
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!