Postgresql: How to write insert query using where clause-CodePudding

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
  }
]

CodePudding user response：

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

CodePudding user response：

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;