How to use json object with where clause?

Question

What I'm trying to achieve

Find all players which is in the authenticated users team.

What is the Problem?

Unable to use the returned json within const findUsers = await User.findAll where clause and I am unsure if this is the correct way.

Database Tables

Users Table : id (PK) , etc

Teams: id (PK) , etc

TeamUsers: id , TeamID (Foreign Key) , UserID (Foreign Key) , etc

Returning Json from FindTeamUsers (Var ob) which is correct

[{"id":2,"TeamID":1,"UserID":1,"createdAt":"2019-08-09","updatedAt":"2019-08-09"},{"id":3,"TeamID":1,"UserID":3,"createdAt":"2019-08-09","updatedAt":"2019-08-09"},{"id":76,"TeamID":1,"UserID":5,"createdAt":"2019-08-22","updatedAt":"2019-08-22"}]

Below is the Route that I am currently using using Nodejs, ExpressJS

router.get('/Team', auth, async function(req, res) {

 // -- Get the Users team that is currently Authenticated (req.user.id (auth) )
     const findTeam = await TeamUsers.findOne({
        where: {
          UserID: req.user.id
        }
      });

      //If the User has a team
      if (findTeam) {

  // -- Get the players Team Mates who have the matching TeamID
        const findTeamUsers = await TeamUsers.findAll({

          where: {
            TeamID: findTeam.TeamID
          }

        });
//Store the object and Display in JSON FORMAT
         var ob = JSON.stringify(findTeamUsers);
         console.log(ob);

        if (!findTeamUsers) {
          console.log('error');
        } else {
          //find the Users Details From the Users Table Model
          //findTeamUsers - Is an array of each record found from const findTeamUsers = await TeamUsers.findAll
          const findUsers = await User.findAll({
            where: {
              id: ob.UserID
            }
          });

          res.status(200).json(findUsers);


        }
      }

    });

What error are you getting? findTeam is undefined perhaps? — silencedogood
– silencedogood, Commented Aug 30, 2019 at 18:50
@silencedogood Error: WHERE parameter "id" has invalid "undefined" value . this is from ob.id within FindUsers . FindTeamUsers work correctly . — John
– John, Commented Aug 30, 2019 at 18:55
had to change my code it was findteamusers.UserID , should have been ob.id . ob being the object and id refers to the user id within the USers table — John
– John, Commented Aug 30, 2019 at 18:56
looking at your ob object, id should be accessed as ob[0].id... — 1565986223
– 1565986223, Commented Aug 30, 2019 at 18:59

1565986223 · Accepted Answer · 2019-08-30 19:14:56Z

1

Your ob is a string so ob.UserID is undefined. findTeamUsers (FindTeamUsers result) is an array of object so findTeamUsers.UserID would be undefined too. (array findTeamUsers does not have property UserID).

You can pass an array of UserIDs to search multiple elements (if you want to find for all UserIDs in the array):

User.findAll({
  where: {
    id: findTeamUsers.map(o => o.UserID)
  }
})

edited Aug 30, 2019 at 19:14

answered Aug 30, 2019 at 19:06

1565986223

6,7483 gold badges24 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

John Over a year ago

Thanks for the reply , I have tried this there now , im receiving this error . TypeError: ob.map is not a function

1565986223 Over a year ago

whops didn't notice, your ob is string, findTeamUsers should be array, use that. updating answer

Collectives™ on Stack Overflow

How to use json object with where clause?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related