Value to place inside a WHERE clause in Sequelize, PostgreSQL to get everything

Question

I have this piece of code:

return User.findAll({where: {}, include:[
  {model: UserInfo, where: {
    gender: genderPreference
  }}
]

I want to pass such a value in genderPreference so that it gives me all the values. Currently I'm passing either "Male" or "Female" which gives me that data from database accordingly; but if there is no preference for any of the genders then i have no idea on what to do. I have tried null, " " , "" ; but none of them work. Is there any solution to this? Thanks.

score 1 · Accepted Answer · answered Sep 08 '18 at 08:36

I actually solved this with a different approach. Placing:

gender: null

actually searches if the gender field in the database is null or not, so I had to solve it with a different approach. I wanted "all the data" in the database. So, this pretty much solved it:

 if(req.body.genderPreference == "none"){
    conditionalUserInfo = {model: UserInfo, where:{}};
} else {
    conditionalUserInfo = {model: UserInfo, where: {gender: req.body.genderPreference}};
}
return User.findAll({where: {}, include:[
  conditionalUserInfo
]

GenderPreference parameter contains either 'Male' or 'Female' or 'none'.

score 1 · Answer 2 · answered Apr 05 '20 at 14:30

There's a much better way to achieve what you need here without the if/else clause

return User.findAll({where: {}, include:[
 {
    model: UserInfo,
    where: req.body.genderPreference ? { gender: req.body.genderPreference } : {}
  }
]

This allows the database search if the body is present, and returns all gender preference if the body isn't present. I believe this is a much cleaner code.

score 0 · Answer 3 · answered Sep 08 '18 at 05:27

0

Just don't use quotes on null, it'll work fine

return User.findAll({where: {}, include:[
  {model: UserInfo, where: {
    gender: null
  }}
]

See the tutorial for more information

answered Sep 08 '18 at 05:27

Evan Carroll

78,363
46
261
468

Hey Evan, thanks for trying but just placing null didn't fix the problem. I still get no data from the database even after putting gender as null. I guess the database tries to match the null value we put in as well. – Emalp Sep 08 '18 at 06:03
not likely, turn out statement logging and check what your sequelize is sending to the database. You can use sequelize https://stackoverflow.com/a/21431627/124486 or postgresql's log_min_statement – Evan Carroll Sep 08 '18 at 06:05
`SELECT "User"."id","User"."email"...(other sql here)........INNER JOIN "UserInfos" AS "UserInfo" ON "User"."id" = "UserInfo"."userId" AND "UserInfo"."gender" IS NULL;` I get this as my sql. Is there anything wrong with it? – Emalp Sep 08 '18 at 08:10

Value to place inside a WHERE clause in Sequelize, PostgreSQL to get everything

3 Answers3