title: Express/Knex/Sql query with dynamic interpolation not working
body: <p>I have an Express/Postgres backend with the following route that returns a leaderboard based on points accumulated over the past X days.  eg, when I hardcode 'points_horizon' in the query below as 7, it returns the leaderboard based on the past 7 days. However, I want to be able to specify the attribute <code>points_horizon</code> for each group such to adjust the number of days the leaderboard includes. However, the following interpolation does not work, returning the error: <code>ReferenceError: points_horizon is not defined</code></p>
  
  <pre><code>// returns total number of acts per user over the last 'points_horizon' days
  router.get('/leaderboard/:group_id', async (req, res) =&gt; {
    let results = await knex.raw('
  SELECT memberships.users_id, users.username, avatar_url, COUNT(acts.users_id) 
  FROM memberships 
  JOIN groups ON memberships.groups_id = groups.id 
  JOIN users ON memberships.users_id = users.id 
  LEFT JOIN acts ON acts.users_id = users.id 
  AND acts.created_at &gt;= (CURRENT_DATE - ' + points_horizon + ') 
  WHERE memberships.groups_id = ' + req.params.group_id + ' 
  GROUP BY memberships.users_id, users.username, avatar_url 
  ORDER BY COUNT(acts.id) DESC');
    console.log('This is the reports/leaderboard query response', results.rows)
    res.json(results.rows);
  });
  </code></pre>
  
  <p>to add more details, here is how points_horizon gets set:</p>
  
  <p>here is the leaderboard component:</p>
  
  <pre><code>&lt;template&gt;
    &lt;div class="table-responsive mt-2"&gt;
      &lt;table class="ui celled table"&gt;
        &lt;thead&gt;
          &lt;tr&gt;&lt;th colspan="4"&gt;Leaderboard for {{ currentGroup.name }}&lt;/th&gt;&lt;/tr&gt;
          &lt;tr&gt;
            &lt;th&gt;Username&lt;/th&gt;
            &lt;th&gt;Last Action&lt;/th&gt;
            &lt;th&gt;Date&lt;/th&gt;
            &lt;th&gt;Score&lt;/th&gt;
          &lt;/tr&gt;
        &lt;/thead&gt;
        &lt;tbody&gt;
          &lt;tr v-for="(leader) in leaderboard" :key="leader.users_id"&gt;
            &lt;td&gt;
              &lt;h4 class="ui image header"&gt;
                &lt;img v-if="leader.avatar_url" :src="leader.avatar_url"  class="ui mini rounded image"&gt;
                &lt;img v-else :src="'https://robohash.org/'+ leader.username" class="ui mini rounded image"/&gt;
                &lt;router-link :to="`/users/${leader.users_id}`" class="content"&gt;
                  {{leader.username}}
                &lt;/router-link&gt;
              &lt;/h4&gt;
            &lt;/td&gt;
            &lt;td&gt;{{ lastUserAct.deed }}&lt;/td&gt;
            &lt;td&gt;&lt;/td&gt;
            &lt;!-- &lt;td&gt;{{ lastAct(leader.id).deed }}&lt;/td&gt;
            &lt;td&gt;{{ moment(lastAct(leader.id).created_at).strftime("%A, %d %b %Y %l:%M %p") }}&lt;/td&gt; --&gt;
            &lt;td&gt;{{leader.count}}&lt;/td&gt;
          &lt;/tr&gt;
        &lt;/tbody&gt;
      &lt;/table&gt;
    &lt;/div&gt;
  &lt;/template&gt;
  
  &lt;script&gt;
  import moment from 'moment-strftime'
  import _ from 'lodash'
  import ReportsService from '@/services/ReportsService'
  import ActsService from '@/services/ActsService'
  export default {
    name: "Leaderboard",
    data() {
      return {
        lastUserAct: {}
      }
    },
    computed: {
      leaderboard () {
        return this.$store.getters.leaderboard;
      },
      currentGroup () {
        return this.$store.getters.currentGroup;
      }
      // ,
      // lastAct (userId) {
      //   return _.orderBy(this.actsByUser(userId), 'created_at')[0];
      // }
    },
    mounted () {
      this.getLeaderboard();
    },
    methods: {
      getLeaderboard: async function () {
        console.log('in LeaderBoard, this is currentGroup: ', this.$store.getters.currentGroup.name)
        this.$store.dispatch("updateLeaderboard", this.currentGroup);
      },
      moment: function (datetime) {
        return moment(datetime);
      }
      ,
      async lastActByUser (leader_id) {
        console.log('in Leaderboard, getting last act for user')
        const response = await ActsService.fetchLastActByUser ({
          userId: leader_id
        });
        this.lastUserAct = response.data
        console.log('in Leaderboard, lastAct response: ', response.data)
      }
    }
  };
  &lt;/script&gt; 
  </code></pre>
  
  <p>here is store.js:</p>
  
  <pre><code>import Vue from 'vue'
  import Vuex from 'vuex'
  import axios from 'axios'
  import PostsService from './services/PostsService'
  import ReportsService from './services/ReportsService'
  ...
  
  Vue.use(Vuex)
  
  export default new Vuex.Store({
    state: {
      ...
      leaderboard: [],
      currentGroup: {},
      lastAct: '',
      ...
    },
    mutations: {
      ...
      setLeaderboard(state, leaderboard) {
        state.leaderboard = leaderboard
      },
      setCurrentGroup(state, group) {
        state.currentGroup = group
      },...
    },
    actions: {
      ...
      getUserGroups({ commit }) {
        GroupsService.getGroups()
          .then(resp =&gt; {
            console.log('in store getUserGroups, this is usergroups: ', resp);
              commit('setCurrentGroup', resp.data[0]);
          });
      },
      updateLeaderboard({ commit }, group) {
        ReportsService.getLeaderboard(group.id)
          .then(resp =&gt; {
            commit('setLeaderboard', resp);
          });
      },...
    },
    getters: {
      ...
      leaderboard: state =&gt; {
        return state.leaderboard;
      },
      currentGroup: state =&gt; {
        return state.currentGroup;
      },
  
    }
  })
  </code></pre>
  
  <p>here is ReportsService which calls the api:</p>
  
  <pre><code>import axios from 'axios'
  
  export default {
    async getLeaderboard (group_id) {
      let response = await axios.get('reports/leaderboard/' + group_id) 
      console.log('In ReportsService, leaderboard response: ', response.data.id);
      if (response.status === 200) {
        return response.data;
      }
    }
  }
  </code></pre>
  
  <p>I guess the question is how to pass the group to the express rout s.t. I can use it in the query.</p>
  
  <p>a few changes, but still not able to retrieve group attributes:</p>
  
  <p>I adjusted the relevant action in store.js to this:</p>
  
  <pre><code>updateLeaderboard({ commit }, group) {
    ReportsService.getLeaderboard(group)
      .then(resp =&gt; {
        commit('setLeaderboard', resp);
      });
  },
  </code></pre>
  
  <p>changed ReportsService to:</p>
  
  <pre><code>import axios from 'axios'
  
  export default {
    async getLeaderboard (group) {
      let response = await axios.get('reports/leaderboard/' + group.id, {
        params: {
          group: group
        }
      }) 
      console.log('In ReportsService, leaderboard response: ', response.data.id);
      if (response.status === 200) {
        return response.data;
      }
    }
  }
  </code></pre>
  
  <p>and in server side route: </p>
  
  <pre><code>// returns total number of acts per user over the last 'points_horizon' days
  router.get('/leaderboard/:group_id', async (req, res) =&gt; {
    console.log('this is req group object: ', req.query.group)
    let results = await knex.raw('SELECT memberships.users_id, users.username, avatar_url, COUNT(acts.users_id) FROM memberships JOIN groups ON memberships.groups_id = groups.id JOIN users ON memberships.users_id = users.id LEFT JOIN acts ON acts.users_id = users.id AND acts.created_at &gt;= (CURRENT_DATE - 13) WHERE memberships.groups_id = ' + req.params.group_id + ' GROUP BY memberships.users_id, users.username, avatar_url ORDER BY COUNT(acts.id) DESC');
    console.log('This is the reports/leaderboard query response', results.rows)
    res.json(results.rows);
  });
  </code></pre>
  
  <p>in console.log, req.query.group returns: </p>
  
  <pre><code>this is req group object:  {"id":2,"name":"Tuesday Group","description":"We meet every other Tuesday in person!","created_at":null,"updated_at":null,"owners_id":null,"max_members":10,"private_group":false,"address":null,"latitude":null,"longitude":null,"points_horizon":14}
  </code></pre>
  
  <p>However, <code>req.query.group.id</code> and <code>req.query.group["id"]</code> returns <code>undefined</code>. </p>
  
  <p><code>req.query.group.constructor.name</code> returns <code>String</code></p>
  
  <p>last update:
  <code>JSON.parse(req.query.group).points_horizon</code> returns the correct value. However, I don't understand why req.query.group does not return json, and I don't know if this is the right way to do this...</p>