0

Here is my sql tables for a splitwise clone:

#1) Table name: sp_user
|user_id    |username    
+--------------------

|1          |user1       
|2          |user2       
|3          |user3       
|4          |user4  



#2) Table name: sp_group
|group_id   |groupname   |bill

+--------------------------------

|1          |shopping       |2000

|2          |lunch          |1000

The expense for the 'movie' group is shared by users - user1, user2 and the expense for 'lunch' group is shared by users - user1, user2, user3, user4.

#3)Table name: sp_usergroup
|group_id   |user_id

+---------------------

|1           |1

|1           |2

|2           |1

|2           |2

|2           |3

|2           |4

Am using request promise to code the end point /user_expense?uname=user1 which will be as below:

router.route("/user_expense").get(function (req, res) {

  var uname = req.param('name');
  // select query to get the user_id for 'user1' and store it as uid
  .......
  // select query to get the list of group_id for uid

  // set user_exp = 0

    for (i= each of the group_id) {
       // select count(*) for group_id[i] to get the number of users in this group and store it as ct

     // select the bill for group_id[i] and store it as b

    // user_exp += ct / b

   }
   res.send(user_exp);
  }

For user1 the expense amount should be 1250/- (1000 + 250) Will setting bulk query for this single request work and how to do it?

Laxmi Sathy
  • 45
  • 1
  • 1
  • 5

1 Answers1

0

yes you can do this in single query

  • first select user_id from sp_user table by username.
  • for this user_id select group_id from sp_usergroup.
  • and count user_id from sp_usergroup table for these group_id.
  • and join the sp_group table and newly created table on group_id
  • get average for every group_id of user .
  • now do sum of all these avg. you get user_exp.

you can run these sql command in single SQL query like this.

SELECT SUM(avg) AS user_exp
    FROM  (
        SElECT sp_group.bill/COUNT(sp_usergroup.user_id) AS avg 
        FROM sp_usergroup 
        INNER JOIN sp_group ON sp_usergroup.group_id = sp_group.group_id 
        GROUP BY sp_usergroup.group_id,sp_group.bill 
        HAVING sp_usergroup.group_id IN  (
            SELECT group_id 
            FROM sp_usergroup 
            WHERE user_id= (
                            SELECT user_id 
                            FROM sp_user 
                            WHERE username= 'user1')
        )
    ) AS result
Sandeep Singh
  • 31
  • 4