C64Power Forum

Inne => Sprawy organizacyjne => Wątek zaczęty przez: Raf w 30 Lipca 2015, 20:17

Tytuł: Kto przerobi SQLa
Wiadomość wysłana przez: Raf w 30 Lipca 2015, 20:17

   $request = $smcFunc['db_query']('substring', '
      SELECT
         m.poster_time, m.subject, m.id_topic, m.id_member, m.id_msg,
         IFNULL(mem.real_name, m.poster_name) AS poster_name, t.id_board, b.name AS board_name,
         SUBSTRING(m.body, 1, 385) AS body, m.smileys_enabled
      FROM {db_prefix}messages AS m
         INNER JOIN {db_prefix}topics AS t ON (t.id_topic = m.id_topic)
         INNER JOIN {db_prefix}boards AS b ON (b.id_board = t.id_board)
         LEFT JOIN {db_prefix}members AS mem ON (mem.id_member = m.id_member)
      WHERE m.id_msg >= {int:likely_max_msg}' .
         (!empty($modSettings['recycle_enable']) && $modSettings['recycle_board'] > 0 ? '
         AND b.id_board != {int:recycle_board}' : '') . '
         AND {query_wanna_see_board}' . ($modSettings['postmod_active'] ? '
         AND t.approved = {int:is_approved}
         AND m.approved = {int:is_approved}' : '') . '
      ORDER BY m.id_msg DESC
      LIMIT ' . $latestPostOptions['number_posts'],
      array(
         'likely_max_msg' => max(0, $modSettings['maxMsgID'] - 50 * $latestPostOptions['number_posts']),
         'recycle_board' => $modSettings['recycle_board'],
         'is_approved' => 1,
      )
   );

trzeba zrobic tak, jakby sie chcialo wyciagnac DISTINCT(m.id_topic)

Tytuł: Re: Kto przerobi SQLa
Wiadomość wysłana przez: Amun-Ra w 31 Lipca 2015, 12:34

Nietestowane a już na pewno nieoptymalne.

$request = $smcFunc['db_query']('substring', '
SELECT
   m.poster_time, m.subject, m.id_topic, m.id_member, m.id_msg,
   IFNULL(mem.real_name, m.poster_name) AS poster_name, t.id_board, b.name AS board_name,
   SUBSTRING(m.body, 1, 385) AS body, m.smileys_enabled
FROM {db_prefix}messages AS m
   INNER JOIN {db_prefix}topics AS t ON (t.id_topic = m.id_topic)
   INNER JOIN {db_prefix}boards AS b ON (b.id_board = t.id_board)
   LEFT JOIN {db_prefix}members AS mem ON (mem.id_member = m.id_member)
WHERE m.id_topic IN(
SELECT
   DISTINCT(m.id_topic)         
FROM {db_prefix}messages AS m
   INNER JOIN {db_prefix}topics AS t ON (t.id_topic = m.id_topic)
   INNER JOIN {db_prefix}boards AS b ON (b.id_board = t.id_board)
   LEFT JOIN {db_prefix}members AS mem ON (mem.id_member = m.id_member)
WHERE m.id_msg >= {int:likely_max_msg}' .
   (!empty($modSettings['recycle_enable']) && $modSettings['recycle_board'] > 0 ? '
   AND b.id_board != {int:recycle_board}' : '') . '
   AND {query_wanna_see_board}' . ($modSettings['postmod_active'] ? '
   AND t.approved = {int:is_approved}
   AND m.approved = {int:is_approved}' : '')
) ORDER BY m.id_msg DESC
LIMIT ' . $latestPostOptions['number_posts'],
      array(
         'likely_max_msg' => max(0, $modSettings['maxMsgID'] - 50 * $latestPostOptions['number_posts']),
         'recycle_board' => $modSettings['recycle_board'],
         'is_approved' => 1,
      )
   );

Tytuł: Re: Kto przerobi SQLa
Wiadomość wysłana przez: Raf w 02 Sierpnia 2015, 15:08

jak sprawdze to sam znac, dziekuje za podjecie tematu