SQL fails
Sama34 Offline
Senior Member
****
Posts: 490
Joined: May 2011
Post: #1
SQL fails
I'm trying to fetch the most popular threads to show them in the portal, this is the query I have:

SQL Code
1
2
3
4
5
6
SELECT t.subject, t.tid, t.views, t.icon, t.prefix
                         FROM ".TABLE_PREFIX."threads t
                         LEFT JOIN ".TABLE_PREFIX."ratemf_log l ON (l.pid=t.firstpost)
                         WHERE t.visible='1' {$overview_unviewwhere} AND t.closed NOT LIKE 'moved|%'
                         ORDER BY t.views DESC
                         LIMIT 0, {$settings['overview_max']};


As you can see, I'm joining a different tables to order the threads by the number of "rates" the thread has had. In the above I just joined, the query still works as usual. The problem comes here:

SQL Code
1
2
3
4
5
6
SELECT t.subject, t.tid, t.views, t.icon, t.prefix, COUNT(l.uid) AS logs
                         FROM ".TABLE_PREFIX."threads t
                         LEFT JOIN ".TABLE_PREFIX."ratemf_log l ON (l.pid=t.firstpost)
                         WHERE t.visible='1' {$overview_unviewwhere} AND t.closed NOT LIKE 'moved|%'
                         ORDER BY t.views DESC
                         LIMIT 0, {$settings['overview_max']};


I added:

SQL Code
, COUNT(l.uid) AS logs
, COUNT(l.pid) AS logs
, COUNT(l.lid) AS logs


That is suppose to count the number of rows for that thread, but that alone shouldn't affect the query at all, just that it does, the thread being pulled are no the same as the one before adding that piece of code.

This doesn't seem to work either.

SQL Code
, COUNT(l.*) AS logs


But ", l.lid", ", l.*", or ", l.pid" does Erf

Any clue?


Support PM's will be ignored. Yipi
Plugins: Announcement Bars - Custom Reputation - Mark PM As Unread
08-22-2012 09:13 AM
Visit this user's website Find all posts by this user Quote this message in a reply
ZiNgA BuRgA Online
Fag
*******
Posts: 3,357
Joined: Jan 2008
Post: #2
RE: SQL fails
You need to use grouping (GROUP BY clause) if you want multiple rows returned with an aggregate function (eg COUNT).

My Blog
08-22-2012 10:36 AM
Find all posts by this user Quote this message in a reply
Sama34 Offline
Senior Member
****
Posts: 490
Joined: May 2011
Post: #3
RE: SQL fails
I see, do you think this would work?

PHP Code:
1
2
3
4
5
6
    $query = $db->query("SELECT t.subject, t.tid, t.views, t.icon, t.prefix, l.installs
		FROM ".TABLE_PREFIX."threads t
		LEFT JOIN (SELECT pid, COUNT(lid) AS installs FROM ".TABLE_PREFIX."ratemf_log WHERE rid='1' GROUP BY pid) l ON (l.pid=t.firstpost)
		WHERE t.visible = '1' {$where} AND t.closed NOT LIKE 'moved|%'
		ORDER BY l.installs DESC, t.views DESC
		LIMIT 0,{$limit};");


$where holds the disabled forums list.

There are no visible errors now, just need to wait for posts being rated as installed.


Support PM's will be ignored. Yipi
Plugins: Announcement Bars - Custom Reputation - Mark PM As Unread
(This post was last modified: 08-22-2012 01:08 PM by Sama34.)
08-22-2012 12:59 PM
Visit this user's website Find all posts by this user Quote this message in a reply
ZiNgA BuRgA Online
Fag
*******
Posts: 3,357
Joined: Jan 2008
Post: #4
RE: SQL fails
Probably should.  Might not be the best performance wise, as the inner select is probably doing a full table scan, but might be acceptable to you.

My Blog
08-22-2012 02:13 PM
Find all posts by this user Quote this message in a reply
Sama34 Offline
Senior Member
****
Posts: 490
Joined: May 2011
Post: #5
RE: SQL fails
So I would need to filter the scan in the inner select WHERE statement? But the only filters I can use are "rid (reputation type, in this case 'installs')" and pid (which should be the same as 'firstpost'), but that last is already being used in the ON (...) statement. Will "firstpost" work in the inner select? Will try as soon as I can connect to the FTP.

Support PM's will be ignored. Yipi
Plugins: Announcement Bars - Custom Reputation - Mark PM As Unread
(This post was last modified: 08-22-2012 06:23 PM by Sama34.)
08-22-2012 06:22 PM
Visit this user's website Find all posts by this user Quote this message in a reply
ZiNgA BuRgA Online
Fag
*******
Posts: 3,357
Joined: Jan 2008
Post: #6
RE: SQL fails
The MyBB solution is to cache the count in the threads table.
But maybe I'm wrong, check MySQL's explain function to see how it's running the query (note that small result sets may be inaccurate).

My Blog
08-23-2012 08:32 AM
Find all posts by this user Quote this message in a reply
Sama34 Offline
Senior Member
****
Posts: 490
Joined: May 2011
Post: #7
RE: SQL fails
I'm using the Overview plugin which cache the HTML output and updates it every X amount of time, do you think that would be enough?

I tried using i$mybb->debug_mode = true and $db->explain_query() but well, I'm not that good, I don't understand what the information it outputs means nor what to do with it -.-

Support PM's will be ignored. Yipi
Plugins: Announcement Bars - Custom Reputation - Mark PM As Unread
(This post was last modified: 08-25-2012 04:27 AM by Sama34.)
08-25-2012 04:24 AM
Visit this user's website Find all posts by this user Quote this message in a reply
ZiNgA BuRgA Online
Fag
*******
Posts: 3,357
Joined: Jan 2008
Post: #8
RE: SQL fails
Well if it's not causing issues for you, then I wouldn't worry about it.  No point in over-engineering something that isn't needed...

My Blog
08-25-2012 11:30 AM
Find all posts by this user Quote this message in a reply
Sama34 Offline
Senior Member
****
Posts: 490
Joined: May 2011
Post: #9
RE: SQL fails
Thank you Zinga Burga.

Support PM's will be ignored. Yipi
Plugins: Announcement Bars - Custom Reputation - Mark PM As Unread
08-25-2012 04:52 PM
Visit this user's website Find all posts by this user Quote this message in a reply

« Next Oldest | Next Newest »

 Standard Tools
Forum Jump: