sequelize-----关联查询与批量查询

之前的文章说了sequelize对于事物的处理，包括说自动提交的事务以及手动提交回滚的事物。而对于java来说事务相对来说用起来简单一些，个人是这样认为的。

这次来说一下关联查询与批量查询：

都知道，关联关系主要有三种：一对一，一对多，多对多，我记得javayou一个批量的方法就是addbatch进行批量的操作，言归正传sequelize对于一对一的处理你可以用find相关的方法比如说：findByid,findOne,包括说findAll,而一对多来说有hasMany、多对一是有belongsTo,对于多对多有belongsToMany，我想对于这种名字来说是很好理解的，接下来我们类介绍一下这几种方法：

1 findOne:

我们来看一下源码：

/**
   * Search for a single instance. This applies LIMIT 1, so the listener will always be called with a single instance.
   *
   * __Alias__: _find_
   *
   * @param  {Object}                    [options] A hash of options to describe the scope of the search
   * @param  {Transaction}               [options.transaction] Transaction to run query under
   * @param  {String}                    [options.searchPath=DEFAULT] An optional parameter to specify the schema search_path (Postgres only)
   *
   * @see {@link Model.findAll}           for an explanation of options
   * @return {Promise<Model>}
   */
  static findOne(options) {
    if (options !== undefined && !_.isPlainObject(options)) {
      throw new Error('The argument passed to findOne must be an options object, use findById if you wish to pass a single primary key value');
    }
    options = Utils.cloneDeep(options);

    if (options.limit === undefined) {
      const uniqueSingleColumns = _.chain(this.uniqueKeys).values().filter(c => c.fields.length === 1).map('column').value();

      // Don't add limit if querying directly on the pk or a unique column
      if (!options.where || !_.some(options.where, (value, key) =>
        (key === this.primaryKeyAttribute || _.includes(uniqueSingleColumns, key)) &&
          (Utils.isPrimitive(value) || Buffer.isBuffer(value))
      )) {
        options.limit = 1;
      }
    }

    // Bypass a possible overloaded findAll.
    return this.findAll(_.defaults(options, {
      plain: true,
      rejectOnEmpty: false
    }));
  }

其实通过上面的源码我们可以看到的就是，其实findOne底层用到的就是findAll只不过加了限制的limit 1 这样返回的就是一个找到的对象，而且我们可以看到的就是它的返回值是当前找到的一个一部model：当然你在查找的时候需要通过where去添加查找的条件，它不像是findById一样直接填入参数就默认去用id查找，他是需要查找条件的；

2.findAll

源码太长大家可以自己去看，这里我只是拿出一部分来了解一下：它对于demo的列举还是比较详细的，大家可以自己去看；

这里找到的结果可以用与where并列的limit,offerset,order等来进行排序和分页；其中对于[and],[or]来说可以对条件之间的关系进行约束；

/**
   * Search for multiple instances.
   *
   * __Simple search using AND and =__
   * ```js
   * Model.findAll({
   *   where: {
   *     attr1: 42,
   *     attr2: 'cake'
   *   }
   * })
   * ```
   * ```sql
   * WHERE attr1 = 42 AND attr2 = 'cake'
   *```
   *
   * __Using greater than, less than etc.__
   * ```js
   * const {gt, lte, ne, in: opIn} = Sequelize.Op;
   * Model.findAll({
   *   where: {
   *     attr1: {
   *       [gt]: 50
   *     },
   *     attr2: {
   *       [lte]: 45
   *     },
   *     attr3: {
   *       [opIn]: [1,2,3]
   *     },
   *     attr4: {
   *       [ne]: 5
   *     }
   *   }
   * })
   * ```
   * ```sql
   * WHERE attr1 > 50 AND attr2 <= 45 AND attr3 IN (1,2,3) AND attr4 != 5
   * ```
   * See {@link Operators} for possible operators
   *
   * __Queries using OR__
   * ```js
   * const {or, and, gt, lt} = Sequelize.Op;
   * Model.findAll({
   *   where: {
   *     name: 'a project',
   *     [or]: [
   *       {id: [1, 2, 3]},
   *       {
   *         [and]: [
   *           {id: {[gt]: 10}},
   *           {id: {[lt]: 100}}
   *         ]
   *       }
   *     ]
   *   }
   * });
   * ```
   * ```sql
   * WHERE `Model`.`name` = 'a project' AND (`Model`.`id` IN (1, 2, 3) OR (`Model`.`id` > 10 AND `Model`.`id` < 100));
   * ```
   *
   * The promise is resolved with an array of Model instances if the query succeeds.
   *
   * __Alias__: _all_
   *
   * @param  {Object}                                                    [options] A hash of options to describe the scope of the search
   * @param  {Object}                                                    [options.where] A hash of attributes to describe your search. See above for examples.
   * @param  {Array<String>|Object}                                      [options.attributes] A list of the attributes that you want to select, or an object with `include` and `exclude` keys. To rename an attribute, you can pass an array, with two elements - the first is the name of the attribute in the DB (or some kind of expression such as `Sequelize.literal`, `Sequelize.fn` and so on), and the second is the name you want the attribute to have in the returned instance
   * @param  {Array<String>}                                             [options.attributes.include] Select all the attributes of the model, plus some additional ones. Useful for aggregations, e.g. `{ attributes: { include: [[sequelize.fn('COUNT', sequelize.col('id')), 'total']] }`
   * @param  {Array<String>}                                             [options.attributes.exclude] Select all the attributes of the model, except some few. Useful for security purposes e.g. `{ attributes: { exclude: ['password'] } }`
   * @param  {Boolean}                                                   [options.paranoid=true] If true, only non-deleted records will be returned. If false, both deleted and non-deleted records will be returned. Only applies if `options.paranoid` is true for the model.
   * @param  {Array<Object|Model|String>}                                [options.include] A list of associations to eagerly load using a left join. Supported is either `{ include: [ Model1, Model2, ...]}` or `{ include: [{ model: Model1, as: 'Alias' }]}` or `{ include: ['Alias']}`. If your association are set up with an `as` (eg. `X.hasMany(Y, { as: 'Z }`, you need to specify Z in the as attribute when eager loading Y).
   * @param  {Model}                                                     [options.include[].model] The model you want to eagerly load
   * @param  {String}                                                    [options.include[].as] The alias of the relation, in case the model you want to eagerly load is aliased. For `hasOne` / `belongsTo`, this should be the singular name, and for `hasMany`, it should be the plural
   * @param  {Association}                                               [options.include[].association] The association you want to eagerly load. (This can be used instead of providing a model/as pair)
   * @param  {Object}                                                    [options.include[].where] Where clauses to apply to the child models. Note that this converts the eager load to an inner join, unless you explicitly set `required: false`
   * @param  {Boolean}                                                   [options.include[].or=false] Whether to bind the ON and WHERE clause together by OR instead of AND.
   * @param  {Object}                                                    [options.include[].on] Supply your own ON condition for the join.
   * @param  {Array<String>}                                             [options.include[].attributes] A list of attributes to select from the child model
   * @param  {Boolean}                                                   [options.include[].required] If true, converts to an inner join, which means that the parent model will only be loaded if it has any matching children. True if `include.where` is set, false otherwise.
   * @param  {Boolean}                                                   [options.include[].separate] If true, runs a separate query to fetch the associated instances, only supported for hasMany associations
   * @param  {Number}                                                    [options.include[].limit] Limit the joined rows, only supported with include.separate=true
   * @param  {Object}                                                    [options.include[].through.where] Filter on the join model for belongsToMany relations
   * @param  {Array}                                                     [options.include[].through.attributes] A list of attributes to select from the join model for belongsToMany relations
   * @param  {Array<Object|Model|String>}                                [options.include[].include] Load further nested related models
   * @param  {Array|Sequelize.fn|Sequelize.col|Sequelize.literal}        [options.order] Specifies an ordering. Using an array, you can provide several columns / functions to order by. Each element can be further wrapped in a two-element array. The first element is the column / function to order by, the second is the direction. For example: `order: [['name', 'DESC']]`. In this way the column will be escaped, but the direction will not.
   * @param  {Number}                                                    [options.limit]
   * @param  {Number}                                                    [options.offset]
   * @param  {Transaction}                                               [options.transaction] Transaction to run query under
   * @param  {String|Object}                                             [options.lock] Lock the selected rows. Possible options are transaction.LOCK.UPDATE and transaction.LOCK.SHARE. Postgres also supports transaction.LOCK.KEY_SHARE, transaction.LOCK.NO_KEY_UPDATE and specific model locks with joins. See [transaction.LOCK for an example](transaction#lock)
   * @param  {Boolean}                                                   [options.raw] Return raw result. See sequelize.query for more information.
   * @param  {Function}                                                  [options.logging=false] A function that gets executed while running the query to log the sql.
   * @param  {Boolean}                                                   [options.benchmark=false] Pass query execution time in milliseconds as second argument to logging function (options.logging).
   * @param  {Object}                                                    [options.having]
   * @param  {String}                                                    [options.searchPath=DEFAULT] An optional parameter to specify the schema search_path (Postgres only)
   * @param  {Boolean|Error}                                             [options.rejectOnEmpty=false] Throws an error when no records found
   *
   * @link   {@link Sequelize.query}
   * @return {Promise<Array<Model>>}
   */

3 批量查询

 

批量查询我这里用到的是一种条件的方法，要是大家有更好的方法可以分享一下：

首先我们需要sequelize的Op来使用这些条件：关于这些条件大家可以去官方文档查看有很对类似与java的条件拼接的查询，像in、notin等等；一下就是我们需要的Op的导入，后面我们来使用一下：

import Seqeuelize from 'sequelize';

const Op = Seqeuelize.Op;

比如说我们去查找多个商品这样一种批量查询：下面直接给出了例子的代码：

//很简单我们只需要在查询的条件上面将多个条件组成一个数组，在进行查询的将这种条件以in的方式去并列查询，意思就是将在这个数组当中的商品全部查询出来：
let goods = await GoodsModel.findAll({
		where: {
			id: {[Op.in]: goodsIds}
		}
	});

4 关联查询

在这里我们以belongsTo为例子来展示，比如说订单和商品的关系，大家都知道是多对多，这个时候应该去关联第三张表去查询，但是如果我们不是以订单对商品的角度来看，而是以订单对第三张表，以第三张表取对应商品这样两种角度来看的话前者就是一对多，而后者虽然是一个记录对应一个商品，但是这多条记录可能对应的就是一个订单，所以归结起来就是多对一关系，

好了我们直接以关联表对商品来展示，其他的都是一样的道理，好了直接来上代码：

//这里加的外键，值在查询的时候由方法建立的的一个临时的外键，他不会建立在表当中的
let orderGoods = await OrderGooodsModel.findAll({
			where: {
				order_id: order1[i].id
			}, include: [
				{
	association: OrderGooodsModel.belongsTo(GoodsModel, {foreignKey: 'goods_id'}),
				},
			],
		});
//下面我们可以看一下它的源码：应该可以很清楚的看见远与目标的作用
/**
   * Creates an association between this (the source) and the provided target. The foreign key is added on the source.
   *
   * @param {Model}           target
   * @param {object}          [options]
   * @param {boolean}         [options.hooks=false] Set to true to run before-/afterDestroy hooks when an associated model is deleted because of a cascade. For example if `User.hasOne(Profile, {onDelete: 'cascade', hooks:true})`, the before-/afterDestroy hooks for profile will be called when a user is deleted. Otherwise the profile will be deleted without invoking any hooks
   * @param {string}          [options.as] The alias of this model, in singular form. See also the `name` option passed to `sequelize.define`. If you create multiple associations between the same tables, you should provide an alias to be able to distinguish between them. If you provide an alias when creating the association, you should provide the same alias when eager loading and when getting associated models. Defaults to the singularized name of target
   * @param {string|object}   [options.foreignKey] The name of the foreign key in the source table or an object representing the type definition for the foreign column (see `Sequelize.define` for syntax). When using an object, you can add a `name` property to set the name of the column. Defaults to the name of target + primary key of target
   * @param {string}          [options.targetKey] The name of the field to use as the key for the association in the target table. Defaults to the primary key of the target table
   * @param {string}          [options.onDelete='SET NULL|NO ACTION'] SET NULL if foreignKey allows nulls, NO ACTION if otherwise
   * @param {string}          [options.onUpdate='CASCADE']
   * @param {boolean}         [options.constraints=true] Should on update and on delete constraints be enabled on the foreign key.
   * @returns {BelongsTo}
   * @example
   * Profile.belongsTo(User) // This will add userId to the profile table
   */
  static belongsTo(target, options) {} // eslint-disable-line

y以上就是今天的对于几个方法的简单介绍以及使用，后期会去学习其他的来和大家分享，欢迎大家访问；