rewrite queries using knex to avoid object values

1 files changed, 78 insertions, 62 deletions

diff --git a/routes/admin.js b/routes/admin.js
index 8e136c43..566de322 100644
--- a/routes/admin.js
+++ b/routes/admin.js
@@ -5,7 +5,7 @@ var debug = require('debug')('admin')
 const Sequelize = require('sequelize')
 const Op = Sequelize.Op;
 
-const { v4: uuidv4 } = require('uuid')
+const { v4: uuidv4 } = require('uuid');
 
 // usage:  GET '/users'
 //
@@ -15,9 +15,14 @@ router.get(
   "/users",
   auth,
   ah(async (req, res) => {
-    var users = await db.User.findAll({
-      attributes: ['username', 'email', 'sms', 'verified', 'createdAt']
-    })
+    var users = await knex
+      .select('username', 'email', 'sms', 'verified', knex.raw('LEFT(createdAt,10) as created_at'))
+      .from('users')
+      
+    // Alternative Sequelize query syntax
+    //   await db.User.findAll({
+    //   attributes: ['username', 'email', 'sms', 'verified', 'createdAt']
+    // })
 
     debug('users: ' + JSON.stringify(users))
     return res.send({users: users})
@@ -32,44 +37,29 @@ router.get(
   "/referrals",
   auth,
   ah(async (req, res) => {
-    var referrals = await db.Referral.findAll({
-      include: [ 
-        {model: db.User, as: 'user', attributes: [['username', 'user']]},
-        {model: db.User, as: 'sponsor', attributes: [['username', 'sponsor']]}
-      ]
-    })
+    var referrals = await knex
+      .select(
+        'token', 
+        'users.username as user', 
+        'sponsor.username as sponsor', 
+        knex.raw('LEFT(created_at, 10) as created_at'), 
+        'referrals.status'
+      )
+      .from('referrals')
+      .leftJoin('users', 'users.id', 'referrals.user_id')
+      .leftJoin('users as sponsor', 'sponsor.id', 'referrals.sponsor_id')
 
-    debug('referrals: ' + JSON.stringify(referrals))
-    return res.send({referrals: referrals})
-  })
-);
+    // Alternative Sequelize query syntax (returns sub-tables as hash)
+    // var referrals = await db.Referral.findAll({
+    //   include: [ 
+    //     {model: db.User, as: 'user', attributes: [['username', 'user']]},
+    //     {model: db.User, as: 'sponsor', attributes: [['username', 'sponsor']]}
+    //   ]
+    // })
 
-// usage:  GET '/user_accounts'
-//
-// Returns: 
-//    { referrals: <list of referral tokens> }
-router.get(
-  "/user_accounts",
-  auth,
-  ah(async (req, res) => {
-    var accounts = await db.User.findAll({
-      // attributes: ['id', 'username'], // Cannot specify attributes when using joins ! (?)
-      include: [
-        // { model: db.Invoice, as: 'invoices' },
-        // { model: db.Payment, as: 'payments' },
-        // { model: db.Order, as: 'orders'},
-        { 
-          model: db.Account, 
-          as: 'accounts', 
-          attributes: [['id', 'account_id'], 'user_id', 'ticker', 'balance', 'createdAt', 'updatedAt'], 
-          required: true
-          // where: { balance: { [Op.gt]: 0 }}
-        }
-      ]
-    })
 
-    debug('accounts: ' + JSON.stringify(accounts))
-    return res.send({users: accounts})
+    debug('Referrals: ' + JSON.stringify(referrals))
+    return res.send({referrals: referrals})
   })
 );
 
@@ -81,15 +71,21 @@ router.get(
   "/accounts",
   auth,
   ah(async (req, res) => {
-    var accounts = await db.Account.findAll({
-      attributes: ['id', 'name', 'ticker', 'balance', 'createdAt'],
-      include: [
-        { model: db.User, as: 'user', attributes: ['username'] }
-      ]
-    })
+    const {nonZero} = req.query
+
+    var accounts = knex
+      .select('username', 'accounts.id as account_id', 'ticker', 'balance', knex.raw('LEFT(accounts.createdAt, 10) as created_at'))
+      .from('accounts')
+      .leftJoin('users', 'users.id', 'accounts.user_id')
+
+    if (nonZero) {
+      accounts = accounts
+        .where('balance', '>', 0)
+    }
+    var found = await accounts
 
-    debug('accounts: ' + JSON.stringify(accounts))
-    return res.send({accounts: accounts})
+    debug('accounts: ' + JSON.stringify(found))
+    return res.send({accounts: found})
   })
 );
 
@@ -110,16 +106,40 @@ router.get(
   "/user_transactions",
   auth,
   ah(async (req, res) => {
-    var transactions = await db.User.findAll({
-      include: [
-        { model: db.Invoice, as: 'invoices' },
-        { model: db.Payment, as: 'payments' },
-        { model: db.Order, as: 'orders'}
-      ]
-    })
+    var transactions = knex
+      .select(
+        'username',
+        'email',
+        knex.raw('COUNT(invoices.id) as invoices'),
+        knex.raw('COUNT(payments.id) as payments'),
+        knex.raw('COUNT(orders.id) as orders')
+      )
+      .from('users')
+      .leftJoin('accounts', 'users.id', 'accounts.user_id')
+      .leftJoin('invoices', 'invoices.account_id', 'accounts.id')
+      .leftJoin('payments', 'payments.account_id', 'accounts.id')
+      .leftJoin('orders', 'orders.user_id', 'users.id')
+      .groupBy('users.id')
 
-    debug('transactions: ' + JSON.stringify(transactions))
-    return res.send({users: transactions})
+    // Alternative Sequelize query syntax (returns sub-tables as hash)
+    // var referrals = await db.Referral.findAll({
+    //   include: [ 
+    //     {model: db.User, as: 'user', attributes: [['username', 'user']]},
+    //     {model: db.User, as: 'sponsor', attributes: [['username', 'sponsor']]}
+    //   ]
+    // })
+
+    // var transactions = await db.User.findAll({
+    //   include: [
+    //     { model: db.Invoice, as: 'invoices' },
+    //     { model: db.Payment, as: 'payments' },
+    //     { model: db.Order, as: 'orders'}
+    //   ]
+    // })
+
+    var found = await transactions
+    debug('transactions: ' + JSON.stringify(found))
+    return res.send({transactions: found})
   })
 );
 
@@ -135,12 +155,8 @@ router.get(
   "/user_kyc",
   auth,
   ah(async (req, res) => {
-    var kyc = await db.User.findAll({
-      // include: [
-      //   { model: db.Kyc, as: 'kyc' }
-      // ]
-    })
-
+    
+    var kyc = { details: 'Not yet defined' }
     debug('kyc: ' + JSON.stringify(kyc))
     return res.send({users: kyc})
   })