Count and Group by in IPB query

[ShaneV]

I gonne try to explain it the best i can, my english is not that good.

 

What i want?

 

A list of user that have the most topics in 1 or more category's that i setup in ACP

 

I have made this query but always get an 500 internal server error, so error from IPB i have one error but meanwhile the query has changed a bit.

 

The query

 

				$this->DB->build( array( 'select' => 't.*, COUNT( t.starter_id ) as starter_total',  
												 'from' 		=> array('topics' => 't'),
												 'limit'    	=> array( 0, $this->settings['bs_advancedstats_01_showitems'] ),
	                                                 'order' 		=> 'starter_total DESC',
												 'add_join' => array( array( 'select' => 'm.member_id, m.members_display_name, m.member_group_id, m.members_seo_name',
																			 'from'   => array( 'members' => 'm' ),
																			 'type'   => 'left'
																			),
																	)
												 'where'  => 't.forum_id IN(' . $this->settings['bs_advancedstats_01_fora'] . ') AND m.member_id=t.starter_id GROUP BY (t.starter_id)'
												 ) ); 

 

Is it also possible to use the starter_total as a variable for like in a template?

 

 Date: Sat, 16 Jan 2010 21:43:59 +0000
Error Number: 1064
Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER BY starter_total DESC LIMIT 0,15' at line 1
IP Address: 87.65.163.204
Page: /private/index.php?app=advancedstats&getContent=01
Debug: array (
 1 => 
 array (
   'file' => '/home/**net/**.net/private/ips_kernel/classDb.php',
   'line' => 1019,
   'function' => 'query',
   'class' => 'db_driver_mysql',
 ),
 3 => 
 array (
   'file' => '/home/**net/**.net/private/admin/sources/base/ipsController.php',
   'line' => 291,
   'function' => 'doExecute',
   'class' => 'public_advancedstats_advancedstatsmod_advancedstatssec',
 ),
)
mySQL query error: SELECT t.*, COUNT( t.starter_id ) as starter_total,m.member_id, m.members_display_name, m.member_group_id, m.members_seo_name,t FROM ibf_topics t  LEFT JOIN ibf_members m  ORDER BY starter_total DESC LIMIT 0,15

 

I'm learning allot but don't now how to fix it.

[Michael]

I've modified it a bit, try this code. A join where was added and group by was moved.

 

        $this->DB->build( array( 'select' => 't.*, COUNT( t.starter_id ) as starter_total',  
                                'from'   => array('topics' => 't'),
                                'limit'  => array( 0, $this->settings['bs_advancedstats_01_showitems'] ),
                        		 'order'  => 'starter_total DESC',
                        		 'group'  => 't.starter_id',

                                 'add_join' => array( 
							  array( 'select' => 'm.member_id, m.members_display_name, m.member_group_id, m.members_seo_name',
                                        'from'   => array( 'members' => 'm' ),
                                       'type'   => 'left',
                                       'where'  => 'm.member_id=t.starter_id'
									),
                                 ),
                                 'where' => "t.forum_id IN(' . $this->settings['bs_advancedstats_01_fora'] . ')"
                                 ) );

[ShaneV]

Thanks for editing the query :thumbsup: works fine now, also now i know what i did wrong :)