NodeJS 序列化OR条件对象

drkbr07n  于 2023-05-06  发布在  Node.js
关注(0)|答案(9)|浏览(116)

通过创建这样的对象

var condition=
{
  where:
  {
     LastName:"Doe",
     FirstName:["John","Jane"],
     Age:{
       gt:18
     }
  }    
}

然后传进来

Student.findAll(condition)
.success(function(students){

})

它可以像这样漂亮地生成SQL

"SELECT * FROM Student WHERE LastName='Doe' AND FirstName in ("John","Jane") AND Age>18"

但是,它都是'AND'条件,我如何通过创建条件对象来生成'OR'条件?

t2a7ltrp

t2a7ltrp1#

看来现在有了另一种格式

where: {
    LastName: "Doe",
    $or: [
        {
            FirstName: 
            {
                $eq: "John"
            }
        }, 
        {
            FirstName: 
            {
                $eq: "Jane"
            }
        }, 
        {
            Age: 
            {
                $gt: 18
            }
        }
    ]
}

会产生

WHERE LastName='Doe' AND (FirstName = 'John' OR FirstName = 'Jane' OR Age > 18)

参见文档:http://docs.sequelizejs.com/en/latest/docs/querying/#where

pepwfjgg

pepwfjgg2#

基于字符串的运算符将来将被弃用(您可能已经在控制台中看到了警告)。
使用符号运算符对我来说相当困惑,我用两个例子更新了文档。

Post.findAll({
  where: {
    [Op.or]: [{authorId: 12}, {authorId: 13}]
  }
});
// SELECT * FROM post WHERE authorId = 12 OR authorId = 13;

Post.findAll({
  where: {
    authorId: {
      [Op.or]: [12, 13]
    }
  }
});
// SELECT * FROM post WHERE authorId = 12 OR authorId = 13;
bxjv4tth

bxjv4tth3#

使用Sequelize.or

var condition = {
  where: Sequelize.and(
    { name: 'a project' },
    Sequelize.or(
      { id: [1,2,3] },
      { id: { lt: 10 } }
    )
  )
};

Reference(搜索Sequelize.or
编辑:同样,这已经被修改,最新的方法见Morio's answer

2ekbmq32

2ekbmq324#

在Sequelize版本5中,您也可以使用这种方式(完全使用运算符Sequelize):

var condition = 
{ 
  [Op.or]: [ 
   { 
     LastName: {
      [Op.eq]: "Doe"
      },
    },
   { 
     FirstName: {
      [Op.or]: ["John", "Jane"]
      }
   },
   {
      Age:{
        [Op.gt]: 18
      }
    }
 ]
}

然后,您必须包括以下内容:

const Op = require('Sequelize').Op

然后把它传进去:

Student.findAll(condition)
.success(function(students){ 
//
})

它可以像这样漂亮地生成SQL:

"SELECT * FROM Student WHERE LastName='Doe' OR FirstName in ("John","Jane") OR Age>18"
1dkrff03

1dkrff035#

对于续集4
查询

SELECT * FROM Student WHERE LastName='Doe' 
AND (FirstName = "John" or FirstName = "Jane") AND Age BETWEEN 18 AND 24

Operators的语法

const Op = require('Sequelize').Op;

var r = await to (Student.findAll(
{
  where: {
    LastName: "Doe",
    FirstName: {
      [Op.or]: ["John", "Jane"]
    },
    Age: {
      // [Op.gt]: 18
      [Op.between]: [18, 24]
    }
  }
}
));

注意事项

  • 避免使用alias operators$(例如$and$or...),因为这些将被弃用
  • 除非在表模型中设置了{freezeTableName: true},否则Sequelize将根据其名称的复数形式进行查询(Student -〉Students
6tr1vspr

6tr1vspr6#

参见docs about querying
这将是:

$or: [{a: 5}, {a: 6}]  // (a = 5 OR a = 6)
0yycz8jy

0yycz8jy7#

where: {
          [Op.or]: [
            {
              id: {
                [Op.in]: recordId,
              },
            }, {
              id: {
                [Op.eq]: recordId,
              },
            },
          ],
        },

这对我有用!

r3i60tvu

r3i60tvu8#

对于那些在进行更复杂的查询时遇到问题的人,例如-

// where email = 'xyz@mail.com' AND (( firstname = 'first' OR lastname = 'last' ) AND age > 18)

将是:

[Op.and]: [
    {
        "email": { [Op.eq]: 'xyz@mail.com' }
        // OR "email": 'xyz@mail.com'
    },
    {
        [Op.and]: [
            {
                [Op.or]: [
                    {
                        "firstname": "first"
                    },
                    {
                        "lastname": "last"
                    }
                ]
            },
            {
                "age": { [Op.gt]: 18 }
            }]
    }
]
iugsix8n

iugsix8n9#

let options: FindOptions<any> = {}
let where: WhereOptions = [];

where.push({filedZ: 10});

if (query.search) {
        let tmp: WhereOptions = {
          [Op.or]: [
            {
              [Op.and]: {
                filedX: { [Op.like]: `%${query.search}%` },
              },
            },
            {
              [Op.and]: {
                filedY: { [Op.like]: `%${query.search}%` },
              },
            },
          ],
        };
        where.push(tmp)
}

options.where = where;

await some.findAndCountAll(options);
let options: FindOptions<any> = {}
let where: WhereOptions = [];

where.push({filedZ: 10});

if (query.search) {
        let tmp: WhereOptions = {
          [Op.or]: [
            {
              [Op.and]: {
                filedX: { [Op.like]: `%${query.search}%` },
              },
            },
            {
              [Op.and]: {
                filedY: { [Op.like]: `%${query.search}%` },
              },
            },
          ],
        };
        where.push(tmp)
}

options.where = where;

await some.findAndCountAll(options);

相关问题