Ranking query

samplerank.php

/**
	 * Retrieve a particular user's rank and details, considering descending sort order on that column
	 * @param $column The column name used for sorting
	 * @param $user_id The user ID whose rank we want to obtain
	 * @return array
	 * @throws \Exception
	 */
    public function getUserRank( $column, $user_id )
    {
		if( !is_numeric( $user_id ) )
			throw new \Exception( "Integer user id required" );

		$column = preg_replace( '/[^a-zA-Z_]/', "", $column );

	    /** @var MasterSlaveFeature $msf */
	    if( Core::isProduction() )
	    {
		    $msf = $this->getFeatureSet()->getFeatureByClassName( '\Zend\Db\TableGateway\Feature\MasterSlaveFeature' );
		    $sdb = $msf->getSlaveAdapter();
	    }
	    else
	    {
			$sdb = $this->getAdapter();
	    }

        $res = $sdb->query(
            "SELECT rank_number, user_id, max_score, avg_score, max_level, avg_level, games_played
             FROM (
              SELECT @rank:=@rank+1 AS rank_number, user_id, max_score, avg_score, max_level, avg_level, games_played
              FROM (
                    SELECT user_id, max_score, avg_score, max_level, avg_level, games_played
                    FROM game_leaders
                    ORDER BY {$column} DESC
              ) AS rankings, (SELECT @rank:=0) AS r
            ) AS overall_rankings
            WHERE user_id='{$user_id}'
            LIMIT 0, 1",
            Adapter::QUERY_MODE_EXECUTE
        );

        if( ($row = $res->current() ) )
            return $row;

        return null;
    }

	/**
	 * Given a rank X, get the users at position X-1 and X+1
	 *
	 * @param $column
	 * @param $rank
	 * @return null
	 * @throws \Exception
	 */
	public function getRankNeighbors( $column, $rank )
	{
		if( !is_numeric( $rank ) )
			throw new \Exception( "Integer rank required" );

		$column = preg_replace( '/[^a-zA-Z_]/', "", $column );

		/** @var MasterSlaveFeature $msf */
	    if( Core::isProduction() )
	    {
		    $msf = $this->getFeatureSet()->getFeatureByClassName( '\Zend\Db\TableGateway\Feature\MasterSlaveFeature' );
		    $sdb = $msf->getSlaveAdapter();
	    }
	    else
	    {
			$sdb = $this->getAdapter();
	    }

		$res = $sdb->query($q = "SELECT rank_number, user_id, max_score, avg_score, max_level, avg_level, games_played
		 FROM (
		  SELECT @rank:=@rank+1 AS rank_number, user_id, max_score, avg_score, max_level, avg_level, games_played
		  FROM (
				SELECT user_id, max_score, avg_score, max_level, avg_level, games_played
				FROM game_leaders
				ORDER BY {$column} DESC
		  ) AS rankings, (SELECT @rank:=0) AS r
		) AS overall_rankings
		WHERE rank_number IN (" . ($rank - 1) . "," . ($rank + 1) . ")", Adapter::QUERY_MODE_EXECUTE);


		$neighbors = [];
		foreach( $res as $r )
			$neighbors[] = $r;

        return $neighbors;
	}