查询语言

ThinkORM的查询语法遵循 对象:属性 的模式：

  1. userModel.where({id:1}).find(); //查询一条ID为1的数据

and

  1. userModel.where({ name: 'walter', state: 'new mexico' }).find();
  3. userModel.where({ age: { '>=': 30 , '<=': 60}}).find();

or

  1. // select * from think_user where (name = 'walter') or (occupation = 'teacher')
  2. userModel.where({
  3.     or : [
  4.         { name: 'walter' },
  5.         { occupation: 'teacher' }
  6.     ]
  7. }).find();
  9. //select * from think_user where (id = 1 and name = walter) or (occupation ='teacher')
  10. userModel.where({
  11.     or : [
  12.         { name: 'walter' , id: 1},
  13.         { occupation: 'teacher' }
  14.     ]
  15. }).find();

in

  1. userModel.where({
  2.     name : ['Walter', 'Skyler']
  3. }).find();

not in

  1. userModel.where({
  2.     name: { 'notin' : ['Walter', 'Skyler'] }
  3. }).find();
  5. userModel.where({
  6.     notin: { 'name' : ['Walter', 'Skyler'] , 'id': [1, 3]}
  7. }).find();

is null

  1. userModel.where({
  2.     name: null }
  3. }).find();

is not null

  1. userModel.where({
  2.     not: {name: null} }
  3. }).find();
  5. userModel.where({
  6.     name: {"!=": null} }
  7. }).find();
  10. userModel.where({
  11.     name: {"<>": null} }
  12. }).find();

less than

  1. userModel.where({ age: { '<': 30 }}).find();

less than or equal

  1. userModel.where({ age: { '<=': 30 }}).find();

greater than

  1. userModel.where({ age: { '>': 30 }}).find();

greater than or equal

  1. userModel.where({ age: { '>=': 30 }}).find();

not equal

  1. userModel.where({ age: { '<>': 30 }}).find();
  3. userModel.where({ age: { '!=': 30 }}).find();

not

  1. userModel.where({ age: { 'not': 30 }}).find();
  3. userModel.where({ not: { 'age': 30, 'name': 'aa' }}).find();

like

  1. userModel.where({ name: { 'like': '%walter' }}).find();
  2. userModel.where({ name: { 'like': 'walter%' }}).find();
  3. userModel.where({ name: { 'like': '%walter%' }}).find();

高级查询

字段名为key合并表达式

  1. userModel.where({name: {'<>': '', not: 'aa', in: ['111', '222'], notin: ['aa', 'rrr'], like: '%a'}}).find()

OR 组合及嵌套

  1. userModel.where({
  2.     or : [
  3.         { name: 'walter' , id: 1},
  4.         { occupation: 'teacher' }
  5.     ]
  6. }).find();
  8. userModel.where({
  9.     or : [
  10.         { name: 'walter' , id: 1},
  11.         { occupation: 'teacher' },
  12.         { or: [
  13.                 {...},
  14.                 {...}
  15.             ]
  16.         }
  17.     ]
  18. }).find();

join查询

  1. //将join表字段写到field方法内，join表条件写入where
  2. userModel
  3. .field(['id','name','Demo.id','Demo.name'])
  4. .join([{from: 'Demo', on: {demoid: 'id'}, type: 'inner'}])
  5. .where({id: 1, 'Demo.name': 'test'})
  6. .find()
  8. //将join表字段声明在join方法的field属性内
  9. userModel
  10. .join([{from: 'Demo', alias: 'demo', on: {demoid: 'id'}, field: ['id', 'name'], type: 'inner'}])
  11. .where({id: 1, 'demo.name': 'test'})
  12. .find()

join方法传入的是一个数组，每一个数组元素均表示join一个表。

from : 需要的join的模型名

alias : 需要的join的模型查询别名

on : join的on条件

field : join表筛选的字段

type : join的类型，目前支持 inner,left,right三种

group查询

group查询支持传入单个或多个字段名，多个字段名以数组的形式作为实参

  1. userModel.group('username').find()
  3. userModel.group(['username', 'age']).find()