sql error

[manola]

Hi!

$querya = $vbulletin->db->query_read("
SELECT post.postid, post.pagetext, post.userid, post.username, thread.forumid, thread.title as titulo, thread.threadid, forum.forumid, forum.title
FROM " . TABLE_PREFIX . "post as post
LEFT JOIN " . TABLE_PREFIX . "thread as thread on (post.threadid = thread.threadid)
LEFT JOIN " . TABLE_PREFIX . "forum as forum on (thread.forumid = forum.forumid)
WHERE forum.forumid not in ($excluded)
ORDER BY postid DESC
LIMIT $limit"
);

I got this table
POST 4 - THREAD 1 - USERX
POST 3 - THREAD 2 - USERX
POST 2 - THREAD 1 - USERX
POST 1 - THREAD 3 - USERX

I only want 1 post from every thread, in this example last 4 posts, 2 from thread 1 and other 2 from thread 2 and 3. If I only want to show 1 post from thread 1.. What i have to make?

Thanks!

[bluesoul]

The problem is the normal way of fixing this, using a DISTINCT statement, is ambiguous because, well, which row of the repeating rows will it return? It doesn't seem to have that kind of logic checking. However you can sort of hack it into submission like so.

$querya = $vbulletin->db->query_read("
SELECT MAX(post.postid), post.pagetext, post.userid, post.username, thread.forumid, thread.title AS titulo, thread.threadid, forum.forumid, forum.title
FROM " . TABLE_PREFIX . "post AS post
LEFT JOIN " . TABLE_PREFIX . "thread AS thread ON (post.threadid = thread.threadid)
LEFT JOIN " . TABLE_PREFIX . "forum AS forum ON (thread.forumid = forum.forumid)
WHERE forum.forumid NOT IN ($excluded)
GROUP BY thread.threadid
ORDER BY postid DESC
LIMIT $limit"
);

[manola]

thanks bluesoul for your answer,

At end I found the correct SQL, I only need to use WHERE post.parentid = thread.lastpostid to show only 1 link for 1 thread.

Thanks, u can view the result in www.archivostv.com

[TECK]

manola, you realize how intense is your query to the server, right?
You are doing a huge scan (very bad), even if you stick to indexes only. If you have 1000 members browsing that page into a large forum, it will kill the server instantly, with average loads going over 30-40 easy. Especially if you run Apache (who is known to eat memory like an elephant).
Always use a dateline, when you play with thread or post tables. Give the smallest value possible to it, then you will be ok.

[manola]

Hi teck! I dont use the query at the first post but my query is too similar.

dateline is the value int(10) of each post, how can i give it a smallest value?

Sorry, I dont understand you

[TECK]

Run this into your query analyzer:
SELECT threadid
FROM thread
WHERE postusername = 'John'
AND dateline > (UNIX_TIMESTAMP(NOW()) - 172800)
AND visible = 1
-- you should really add perms here
ORDER BY lastpostid, threadid DESC
LIMIT 15;
This query will scan the table thread for any activity done last 2 days only. It will not scan anything prior to that date event, resulting in a lot less memory usage, load to the server, etc.
In other words, you will not scan the hole table, then drop all results and keep only 15.

When I ment the smallest value, I ment this: 172800 (60 * 60 * 24 * 2)
If you have a lot of threads posted evey day, you might need to set this value 120 (1 hour).
Since you only need few threads to be displayed, experiment until you get the best time window.

Edit: You should not rely only on the dateline cut. Make sure you add another condition that will cut down even further the scanning. (like I did with username John)

[manola]

If u want I make a statue to you.. jejeje (sorry for my english)

Very Thanks for ur advice!

I only made about 150-300 posts/day with about 340.000 posts so my forum isnt big.