<commit_before>const {knex} = require('../util/database').connect();
import _ from 'lodash';
import {deepChangeKeyCase} from '../util';

function getTeams(opts) {
  const isBanned = opts.client && !!opts.client.isBanned;

  let sqlString = `
    SELECT teams.id, teams.name, teams.image_path,
      SUM(COALESCE(action_types.value, 0)) AS score,
      cities.id AS city
    FROM teams
    LEFT JOIN actions ON teams.id = actions.team_id ${isBanned ? '' : 'AND NOT actions.is_banned'}
    LEFT JOIN action_types ON actions.action_type_id = action_types.id
    JOIN cities ON cities.id = teams.city_id
  `;

  let params = [];
  let whereClauses = [];

  if (opts.city) {
    whereClauses.push('cities.id = ?');
    params.push(opts.city);
  }

  if (whereClauses.length > 0) {
    sqlString += ` WHERE ${ whereClauses.join(' AND ')}`;
  }

  sqlString += `
    GROUP BY teams.id, teams.name, cities.id
    ORDER BY score DESC, teams.id
  `;

  return knex.raw(sqlString, params)
  .then(result => {
    return _.map(result.rows, row => deepChangeKeyCase(row, 'camelCase'));
  });
}

export {
  getTeams
};
<commit_msg>Score counting factoring in votes<commit_after>const {knex} = require('../util/database').connect();
import _ from 'lodash';
import {deepChangeKeyCase} from '../util';

function getTeams(opts) {
  const isBanned = opts.client && !!opts.client.isBanned;

  const voteScoreByTeam = `
      (SELECT
        teams.id AS team_id,
        SUM(votes.value::float) * 3 * (1 - @(SUM(votes.value::float) / GREATEST(COUNT(votes), 25))::float) AS value
      FROM
        votes
        LEFT JOIN feed_items ON feed_items.id = votes.feed_item_id
        LEFT JOIN users ON users.id = feed_items.id
        LEFT JOIN teams ON teams.id = users.team_id
      WHERE
        teams.id IS NOT NULL
      GROUP BY
        teams.id)
  `;

  let sqlString = `
    SELECT teams.id, teams.name, teams.image_path,
      COALESCE(vote_score.value, 0) + SUM(COALESCE(action_types.value, 0)) AS score,
      cities.id AS city
    FROM teams
    LEFT JOIN actions ON teams.id = actions.team_id ${isBanned ? '' : 'AND NOT actions.is_banned'}
    LEFT JOIN action_types ON actions.action_type_id = action_types.id
    LEFT JOIN ${voteScoreByTeam} vote_score ON vote_score.team_id = teams.id
    JOIN cities ON cities.id = teams.city_id
  `;

  let params = [];
  let whereClauses = [];

  if (opts.city) {
    whereClauses.push('cities.id = ?');
    params.push(opts.city);
  }

  if (whereClauses.length > 0) {
    sqlString += ` WHERE ${ whereClauses.join(' AND ')}`;
  }

  sqlString += `
    GROUP BY teams.id, teams.name, cities.id, vote_score.value
    ORDER BY score DESC, teams.id
  `;

  return knex.raw(sqlString, params)
  .then(result => {
    return _.map(result.rows, row => deepChangeKeyCase(row, 'camelCase'));
  });
}

export {
  getTeams
};

{'fraction_non_alphanumeric': 0.13642756680731363, 'fraction_numerical': 0.0031645569620253164, 'mean_word_length': 3.046941678520626, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 9, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '5653476', 'n_tokens_mistral': 1023, 'n_tokens_neox': 974, 'n_words': 234}