<commit_before>'use strict';

const Boom = require('boom');
const bookshelf = require('../db/bookshelf_init');


module.exports = [
  // returns stats for all hashtags in a country 
  {
    method:'GET',
    path: '/{country}/',
    handler: function(req, res) {
      const knex = bookshelf.knex;
      // force title case
      const country = req.params.country.split(' ').map(l =>
        l[0].toUpperCase() + l.substr(1).toLowerCase())
      return knex.raw("select * FROM countries WHERE name IN ('" + country +"')")
      .then(function (results) {
        var country_id = results.rows[0].id
        return country_id
      }).then(function (country_id) {
        // join changesets with changesets_hashtags
        // do that only where changesets_hashtags.id are those in a given country
        // select all the changesets_ids,hashtag_ids for country
        // next I want to join all in the
        var hashtag_ids = knex.raw(
          "SELECT \
            SUM(road_count_add) AS road_count_add, \
            SUM(road_count_mod) AS road_count_mod, \
            SUM(building_count_add) AS building_count_add, \
            SUM(building_count_mod) AS building_count_mod, \
            SUM(waterway_count_add) AS waterway_count_add, \
            SUM(poi_count_add) AS poi_count_add, \
            SUM(road_km_add) AS road_km_add, \
            SUM(road_km_mod) AS road_km_mod, \
            SUM(waterway_km_add) AS waterway_km_add, \
            filtered.hashtag\
          FROM changesets JOIN\
            (SELECT hashtag, id, changeset_id FROM \
              (SELECT hashtags.id, hashtags.hashtag, changesets_hashtags.changeset_id FROM hashtags \
              JOIN changesets_hashtags ON hashtags.id = changesets_hashtags.hashtag_id) AS hashtag_changesets_joined\
              WHERE changeset_id IN \
              (SELECT changeset_id FROM changesets_countries WHERE country_id = " + country_id + ")) \
               AS filtered \
            ON changesets.id=filtered.changeset_id\
            GROUP by filtered.hashtag;"
          )<commit_msg>#20 add country hashtag route<commit_after>'use strict';

const Boom = require('boom');
const bookshelf = require('../db/bookshelf_init');


module.exports = [
  // returns stats for all hashtags in a country
  {
    method:'GET',
    path: '/{country}/',
    handler: function(req, res) {
      const knex = bookshelf.knex;
      // force title case
      const country = req.params.country.split(' ').map(l =>
        l[0].toUpperCase() + l.substr(1).toLowerCase())
      return knex.raw("select * FROM countries WHERE name IN ('" + country +"')")
      .then(function (results) {
        var country_id = results.rows[0].id
        return country_id
      }).then(function (country_id) {
        // for given country_id
        // returns stats for all hashtags and name of hashtag
        var hashtag_ids = knex.raw(
          "SELECT \
            SUM(road_count_add) AS road_count_add, \
            SUM(road_count_mod) AS road_count_mod, \
            SUM(building_count_add) AS building_count_add, \
            SUM(building_count_mod) AS building_count_mod, \
            SUM(waterway_count_add) AS waterway_count_add, \
            SUM(poi_count_add) AS poi_count_add, \
            SUM(road_km_add) AS road_km_add, \
            SUM(road_km_mod) AS road_km_mod, \
            SUM(waterway_km_add) AS waterway_km_add, \
            filtered.hashtag\
          FROM changesets JOIN\
            (SELECT hashtag, id, changeset_id FROM \
              (SELECT hashtags.id, hashtags.hashtag, changesets_hashtags.changeset_id FROM hashtags \
              JOIN changesets_hashtags ON hashtags.id = changesets_hashtags.hashtag_id) AS hashtag_changesets_joined\
              WHERE changeset_id IN \
              (SELECT changeset_id FROM changesets_countries WHERE country_id = " + country_id + ")) \
               AS filtered \
            ON changesets.id=filtered.changeset_id\
            GROUP by filtered.hashtag;"
          )
{'fraction_non_alphanumeric': 0.09605788423153692, 'fraction_numerical': 0.001996007984031936, 'mean_word_length': 2.2487844408427877, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 5, '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': '2189107', 'n_tokens_mistral': 1269, 'n_tokens_neox': 1218, 'n_words': 289}