Postgresql: How to write insert query using where clause

Question

Let say we have a table called user and it has userId column.

user
----
userId

we have another table called Team and it has 2 columns userId and leadId.

Team
----
userId
leadId

condition 1: I want to insert data into Teams table and that values, should be in user table. In other words userId and leadId of Teams table should of userId of user table. Otherwise it throws an error that id does not exists in user table.

condition 2: one more condition is userId and leadId can't be same. This is done by using if check.

here is my API code

router.post('/', checkToken, async (req, res) => {
    try {
        const { body } = req;
        const {
            userId,
            leadId
        } = body

        const dataToInsert = {
            userId,
            leadId
        }

        if (userId == leadId) {
            res.status(300).json({ error: "Lead id and user can't be same." })
        }
        else {
            const data = await db.Team.create(dataToInsert)

            res.status(200).json({
                data
            })
        }

    } catch (e) {
        console.log("Error in inserting team lead", e)
        res.status(500).json({
            error: "Internal Server Error",
            status: false,
        })

    }
})

export default router;

Please help how to handle the condition 1.

update: this is what i tried now

  const IdCount= await db.User.findAll({
            attributes: [[db.Sequelize.fn('count', db.Sequelize.col('id')), 'getIdCount']],
            where: { "id": userId && leadId }
        });

output of IdCount:

here is the output, the getIdCount value is 0 which is correct but it send me whole object, how i can get only getIdCount value?

[
  user {
    dataValues: { getIdCount: '0' },
    _previousDataValues: { getIdCount: '0' },
    _changed: Set(0) {},
    _options: {
      isNewRecord: false,
      _schema: null,
      _schemaDelimiter: '',
      raw: true,
      attributes: [Array]
    },
    isNewRecord: false
  }
]

So leadId or teamId? Please correct your post

Anatoly
– Anatoly

2022-01-14 08:15:43 +00:00
Commented Jan 14, 2022 at 8:15 — Anatoly
– Anatoly, Commented Jan 14, 2022 at 8:15
corrected, it is a leadId.

TimeToCode
– TimeToCode

2022-01-14 09:10:05 +00:00
Commented Jan 14, 2022 at 9:10 — TimeToCode
– TimeToCode, Commented Jan 14, 2022 at 9:10

Anatoly · Accepted Answer · 2022-01-14 10:14:11Z

1

You just need to make sure both users exist in the Users table (execute two queries against the Users table) and then insert a record into the Teams table.
It's better to use an explicit transaction to get both users and insert a record into Teams.

To get either you have a user in DB you can use count:

const whereClause = userId ?  {
            where: {
                id: userId,
            }
} : {}
const userCount = await db.User.count({
            where: {
                id: userId
            }
})
const userExists = count === 1;

The same way you can check leadId

edited Jan 14, 2022 at 10:14

answered Jan 14, 2022 at 9:15

Anatoly

23.2k3 gold badges33 silver badges46 bronze badges

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

6 Comments

TimeToCode Over a year ago

I want some hint how to do this, i update my question what i'm trying to do, please check that.

Anatoly Over a year ago

see the updated answer

TimeToCode Over a year ago

please check my updated question, help if u can their.

Anatoly Over a year ago

Why didn't you try User.count?

TimeToCode Over a year ago

check my uploaded answer, i used count, thank you for your help!

|

TimeToCode · Accepted Answer · 2022-01-14 12:04:12Z

here is what i have done, it giving me correct responses on all condtions

router.post('/admin/assign', checkToken, authorize('admin'), async (req, res) => {
    try {
        const { body } = req;
        const {
            userId,
            leadId
        } = body

        const dataToInsert = {
            userId,
            leadId
        }
        const idCount = await db.User.count({ where: { [Op.or]: [{ id: userId }, { id: leadId }] } })

        if (userId == leadId) {
            res.status(400).json({ error: "Lead id and user can't be same." })

        }
        else if (idCount == 2) {
            const data = await db.Team.create(dataToInsert)

            res.status(200).json({
                data
            })
        }
        else {
            res.status(400).json({ error: "UserId or LeadId doesn't exist" })
        }



    } catch (e) {
        console.log("Error in inserting team lead", e)
        res.status(500).json({
            error: "Internal Server Error",
            status: false,
        })

    }
})

export default router;

Collectives™ on Stack Overflow

Postgresql: How to write insert query using where clause

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related