News:

Bored?  Looking to kill some time?  Want to chat with other SMF users?  Join us in IRC chat or Discord

Main Menu

order by subject disordered

Started by Anguz, August 07, 2003, 12:16:40 AM

Previous topic - Next topic

Anguz

I modified MessageIndex to sort a certain board by subject, got the code from part of this mod

the problem is that the threads are ordered in a way that seems pretty weird to me.. you can see it here

so I'm asking you, oh so much more knowledged than I, guys :D... what could the problem be? and how should I fix it?

this is the block of code that deals with this:
   // Grab the appropriate topic information

   if ($board == 28) # notas y articulos laakademia.com
      $ordrsql = 'm.subject DESC' ;
   else
      $ordrsql = 'm.posterTime DESC' ;
   
   $stickyOrder = ($modSettings['enableStickyTopics'] == 1 ? 't.isSticky DESC,' : '');
   $result = mysql_query("
      SELECT t.ID_TOPIC
      FROM {$db_prefix}topics AS t, {$db_prefix}messages AS m
      WHERE m.ID_MSG=t.ID_LAST_MSG
         AND t.ID_BOARD=$currentboard
      ORDER BY $stickyOrder $ordrsql
      LIMIT $start,$maxindex") or database_error(__FILE__, __LINE__);
   $topics = array();

   while ($row = mysql_fetch_assoc($result))
      $topics[] = $row['ID_TOPIC'];
   if (count($topics))
   {
      $result = mysql_query("
         SELECT t.ID_LAST_MSG, t.ID_TOPIC, t.numReplies, t.locked, m.posterName, m.ID_MEMBER, IFNULL(mem.realName, m.posterName) AS posterDisplayName, t.numViews, m.posterTime, m.modifiedTime, t.ID_FIRST_MSG, t.isSticky, t.ID_POLL, m2.posterName as mname, m2.ID_MEMBER as mid, IFNULL(mem2.realName, m2.posterName) AS firstPosterDisplayName, m2.subject as msub, m2.icon as micon, IFNULL(lt.logTime, 0) AS isRead, IFNULL(lmr.logTime, 0) AS isMarkedRead, m2.posterTime AS mtime
         FROM {$db_prefix}topics as t, {$db_prefix}messages as m, {$db_prefix}messages as m2
            LEFT JOIN {$db_prefix}members AS mem ON (mem.ID_MEMBER=m.ID_MEMBER)
            LEFT JOIN {$db_prefix}members AS mem2 ON (mem2.ID_MEMBER=m2.ID_MEMBER)
            LEFT JOIN {$db_prefix}log_topics AS lt ON (lt.ID_TOPIC=t.ID_TOPIC AND lt.ID_MEMBER=$ID_MEMBER)
            LEFT JOIN {$db_prefix}log_mark_read AS lmr ON (lmr.ID_BOARD=$currentboard AND lmr.ID_MEMBER=$ID_MEMBER)
         WHERE t.ID_TOPIC IN (" . implode(',', $topics) . ")
            AND m.ID_MSG=t.ID_LAST_MSG
            AND m2.ID_MSG=t.ID_FIRST_MSG
         ORDER BY $stickyOrder $ordrsql") or database_error(__FILE__, __LINE__);



thanks for your time again :)
Cristián Lávaque http://cristianlavaque.com

[Unknown]

Freaky.  What happens if stickies are off?

-[Unknown]

Anguz

QuoteFreaky.

isn't it? I'm like "wft?"  :o

QuoteWhat happens if stickies are off?

what do you mean?
Cristián Lávaque http://cristianlavaque.com

[Unknown]

I was thinking that maybe $stickyOrder is messing it up somehow.

-[Unknown]

Anguz

I'll try replacing
$stickyOrder = ($modSettings['enableStickyTopics'] == 1 ? 't.isSticky DESC,' : '');
with
$stickyOrder = '';

just for testing

although when ordering by dates (like date replied to, or started) it works fine, even with the stickies
Cristián Lávaque http://cristianlavaque.com

Anguz

#5
no, the weird sorting was just the same, only without the sticky thread at the top

???

I really can't understand...

is it possible to order the threads AFTER getting them from the db? probably PHP does a better job at it than MySQL... ::)
Cristián Lávaque http://cristianlavaque.com

[Unknown]

Not in YaBB SE 1.5.x...

Try sorting by posterName and seeing how badly that does...

Very, very odd.

-[Unknown]

Anguz

I tried it too, when I first installed the A.M.A.'s mod in my test board, but it did the same

when I saw was that dates were sorted correctly, but text wasn't, like in poster name (started or replied) and subject

I also noticed that when changing the subject in the board I linked to above, many threads (maybe all, I didn't check that with particular attention) stayed in the same place, even though I'd just changed the begging of the thread from text to numbers

I was wondering if it's using something else to order the threads, instead of the actual subject string... but I have no idea what it could be... if you check the last page of that board, most of the threads are in proper order

in the test board where I tried the subject thing first, most of the threads were correctly ordered, even if it was text, but there was one completely out of place... lemme look for the url...

http://llama.lewismedia.com/~anguz/forums/index.php?board=19

it's sorted the same way as the one in the first message of this thread

... why isn't it possible to sort the threads with php? can't they be stored in an array and then sorted before displaying them?
Cristián Lávaque http://cristianlavaque.com

Anguz

the board I linked to in the first post is not ordered by subject anymore, at least while I find a solution for this

the board I linked to in the previous post is still the same, so you can see the problem there
Cristián Lávaque http://cristianlavaque.com

A.M.A

Your code is ok .. I guess
The problem will accrue when there is RE:xxx in the first thread! It will sort according to the subject (if there is more than one in the same thread it will sort the last one)!
You cannot determine the first subject of a thread by using (xxx_messages) table only! It must be linked to (xxx_topics) using the (ID_FIRST_MSG) field!
Really sorry .. real life is demanding my full attention .. will be back soon hopefully :)

Grudge

What A.M.A said.

If you sort my subject it is looking at the title of the last post in the thread. I would recommend changing

if ($board == 28) # notas y articulos laakademia.com
      $ordrsql = 'm.subject DESC' ;

and asign $ordersql to m2.subject DESC

and you can either add an extra line to the first query getting another 'FROM {$db_prefix}messages as m2' and in the WHERE part adding m2.ID_MSG=t.ID_FIRST_MSG

That will add slightly to the thread load time I guess so you could just make up some if's to decide whether to look at the last or first message in that query.

That may not be the whole bug but it must be a big part of it...
I'm only a half geek really...

Anguz

#11
the query things were already there, but I change the 'm.subject DESC' to 'm2.subject DESC' like you said, Grudge, and didn't work, but it was a similar error to when I wanted to order by date started, so I fixed it like A.M.A. sugested for that one, putting it a few lines ahead, so it now looks like this:

   // Grab the appropriate topic information

      $ordrsql = 'm.posterTime DESC' ;
   
   $stickyOrder = ($modSettings['enableStickyTopics'] == 1 ? 't.isSticky DESC,' : '');
   $result = mysql_query("
      SELECT t.ID_TOPIC
      FROM {$db_prefix}topics AS t, {$db_prefix}messages AS m
      WHERE m.ID_MSG=t.ID_LAST_MSG
         AND t.ID_BOARD=$currentboard
      ORDER BY $stickyOrder $ordrsql
      LIMIT $start,$maxindex") or database_error(__FILE__, __LINE__);
   $topics = array();

   if ($board == 28) # notas y articulos laakademia.com
      $ordrsql = 'm2.subject DESC' ;

   while ($row = mysql_fetch_assoc($result))
      $topics[] = $row['ID_TOPIC'];
   if (count($topics))
   {
      $result = mysql_query("
         SELECT t.ID_LAST_MSG, t.ID_TOPIC, t.numReplies, t.locked, m.posterName, m.ID_MEMBER, IFNULL(mem.realName, m.posterName) AS posterDisplayName, t.numViews, m.posterTime, m.modifiedTime, t.ID_FIRST_MSG, t.isSticky, t.ID_POLL, m2.posterName as mname, m2.ID_MEMBER as mid, IFNULL(mem2.realName, m2.posterName) AS firstPosterDisplayName, m2.subject as msub, m2.icon as micon, IFNULL(lt.logTime, 0) AS isRead, IFNULL(lmr.logTime, 0) AS isMarkedRead, m2.posterTime AS mtime
         FROM {$db_prefix}topics as t, {$db_prefix}messages as m, {$db_prefix}messages as m2
            LEFT JOIN {$db_prefix}members AS mem ON (mem.ID_MEMBER=m.ID_MEMBER)
            LEFT JOIN {$db_prefix}members AS mem2 ON (mem2.ID_MEMBER=m2.ID_MEMBER)
            LEFT JOIN {$db_prefix}log_topics AS lt ON (lt.ID_TOPIC=t.ID_TOPIC AND lt.ID_MEMBER=$ID_MEMBER)
            LEFT JOIN {$db_prefix}log_mark_read AS lmr ON (lmr.ID_BOARD=$currentboard AND lmr.ID_MEMBER=$ID_MEMBER)
         WHERE t.ID_TOPIC IN (" . implode(',', $topics) . ")
            AND m.ID_MSG=t.ID_LAST_MSG
            AND m2.ID_MSG=t.ID_FIRST_MSG
         ORDER BY $stickyOrder $ordrsql") or database_error(__FILE__, __LINE__);


it now orders the threads correctly in each page, but not not as a whole... I mean, each page is ordered independent of the other pages in that board, with whatever threads appear in that page... how can I make it work with the board as a whole?

I'm so glad it's starting to work... it was really annoying not knowing what the heck was going on! thank you guys :)

Cristián Lávaque http://cristianlavaque.com

Grudge

I meant for you to change the first statement as well :) I have changed your code to what I think *may* work. I've basically moved the if $board=28 statement back up top and added an extra line to the first query

   // Grab the appropriate topic information

      $ordrsql = 'm.posterTime DESC' ;
       if ($board == 28) # notas y articulos laakademia.com
      $ordrsql = 'm2.subject DESC' ;
   $stickyOrder = ($modSettings['enableStickyTopics'] == 1 ? 't.isSticky DESC,' : '');
   $result = mysql_query("
      SELECT t.ID_TOPIC
      FROM {$db_prefix}topics AS t, {$db_prefix}messages AS m, {$db_prefix}messages AS m2
      WHERE m.ID_MSG=t.ID_LAST_MSG
         AND m2.ID_MSG=t.ID_FIRST_MSG
         AND t.ID_BOARD=$currentboard
      ORDER BY $stickyOrder $ordrsql
      LIMIT $start,$maxindex") or database_error(__FILE__, __LINE__);
   $topics = array();


   while ($row = mysql_fetch_assoc($result))
      $topics[] = $row['ID_TOPIC'];
   if (count($topics))
   {
      $result = mysql_query("
         SELECT t.ID_LAST_MSG, t.ID_TOPIC, t.numReplies, t.locked, m.posterName, m.ID_MEMBER, IFNULL(mem.realName, m.posterName) AS posterDisplayName, t.numViews, m.posterTime, m.modifiedTime, t.ID_FIRST_MSG, t.isSticky, t.ID_POLL, m2.posterName as mname, m2.ID_MEMBER as mid, IFNULL(mem2.realName, m2.posterName) AS firstPosterDisplayName, m2.subject as msub, m2.icon as micon, IFNULL(lt.logTime, 0) AS isRead, IFNULL(lmr.logTime, 0) AS isMarkedRead, m2.posterTime AS mtime
         FROM {$db_prefix}topics as t, {$db_prefix}messages as m, {$db_prefix}messages as m2
            LEFT JOIN {$db_prefix}members AS mem ON (mem.ID_MEMBER=m.ID_MEMBER)
            LEFT JOIN {$db_prefix}members AS mem2 ON (mem2.ID_MEMBER=m2.ID_MEMBER)
            LEFT JOIN {$db_prefix}log_topics AS lt ON (lt.ID_TOPIC=t.ID_TOPIC AND lt.ID_MEMBER=$ID_MEMBER)
            LEFT JOIN {$db_prefix}log_mark_read AS lmr ON (lmr.ID_BOARD=$currentboard AND lmr.ID_MEMBER=$ID_MEMBER)
         WHERE t.ID_TOPIC IN (" . implode(',', $topics) . ")
            AND m.ID_MSG=t.ID_LAST_MSG
            AND m2.ID_MSG=t.ID_FIRST_MSG
         ORDER BY $stickyOrder $ordrsql") or database_error(__FILE__, __LINE__);


That should work I believe. The only problem I have with the code is it's a little inefficient as you are always pulling out on variable you don't need but I can't believe you'll notice a load time difference.
I'm only a half geek really...

Anguz

it's working! it's working!   :o

THANK YOU! :D
Cristián Lávaque http://cristianlavaque.com

Advertisement: