Server side support UBQL v2 version (value in whereList)

Enumeration of all condition types. This enumeration defines a set of String values. It exists primarily for documentation purposes - in code use the actual string values like '>', don't reference them through this class like WhereCondition.more.

We define several aliases for the same condition. In case of direct HTTP request (without Repository) use only non-aliased values (i.e. more instead of '>' or 'gt')

Adds attribute(s) or expression(s).

Can take expression as a field. In this case entity attribute name must be wrapped into [] brackets. In case of client-side execution the only valid expression is one of:

• 'SUM', 'COUNT', 'AVG', 'MAX', 'MIN', 'CAST', 'COALESCE'
• for PostGIS ST_* (Topological Relationships, Distance Relationships, Measurement Functions) are allowed. Restriction - first argument must be bracket attribute, oll other - either parameters or numeric constant

  
      // chaining
UB.Repository('uba_user').attrs('ID').attrs('name', 'firstName').attrs('disabled').selectAsObject()

// calculate sum over some attribute
UB.Repository('uba_user').attrs('SUM([disabled])').where('disabled', '=', true).selectScalar()

// In case of server-side execution any valid SQL expression is accepted by attr:
UB.Repository('uba_user').attrs('[ID] / 100 + 1').selectAsArray()

// JOIN `uba_userrole.roleID` is a attribute of type Entity. ORM choose `left join` in case attribute is `allowNull: true`
UB.Repository('uba_userrole').attrs(['userID', 'userID.name']).selectAsObject()

// todo Define a way to join for UNITY (@)

// get values for attribute of type MANY
UB.Repository('tst_maindata')
 .attrs('ID', 'manyValue', 'manyValue.caption')
 .where('code', '=', 'Code1')
 .selectAsObject({'manyValue.caption': 'captions'})
// result is `[{"ID":331947938939292,"manyValue":"1,2","captions":"caption 10,caption 20"}]`

// Get attribute value for multi-language ("isMultiLang": true in meta file) attribute other when current session language
UB.Repository('org_employee').attrs(['ID', 'lastName_en^']).selectAsObject()

// PostGIS
UB.Repository('tstgeo_geometry').attrs('ID', 'ST_Area([geoLine])')
  .where('ST_Intersects([geo], ?)', 'custom', '{"type":"LineString","coordinates":[[0,0],[0,2]]}')
  .select()
  

Helper method for CustomRepository.attrs. Calls attrs in case addingCondition is truthy

  
      let isPessimisticLock = !!UB.connection.domain.get('uba_user').attributes.mi_modifyDate
// with whereIf
let repo = UB.Repository('uba_user').attrs('ID').attrsIf(isPessimisticLock, 'mi_modifyDate')
//without whereIf
let repo = UB.Repository('uba_user').attrs('ID')
if (isPessimisticLock) repo = repo.attrs('mi_modifyDate')
  

Remove all sorting. This function mutates the current Repository

  
      let repo1 = UB.Repository('uba_user').attrs('code', 'name).orderBy('name')
let repoWithoutOrder = repo1.clone().clearOrderList()
  

Remove all where conditions (except ones using in joinAs). This function mutates the current Repository

  
      let repo1 = UB.Repository('uba_user').attrs('ID', 'code').where('ID', '>', 15, 'byID')
let repoWithoutWheres = repo1.clone().clearWhereList()
  

Creates a clone of this repository

  
      let repo1 = UB.Repository('uba_user').attrs('ID', 'code').where('ID', '>', 15, 'byID')
let repo2 = repo1.clone()
repo1.orderBy('code')
repo2.attrs('name').where('ID', '>', 100, 'byID')
repo1.selectAsObject() // return ordered users with ID > 15
repo2.selectAsObject() // return unordered users with their names and ID > 100
  

If current repository is used as a sub-query for exists, notExists, in or notIn conditions correlation with a master repository will added

Adds where condition with EXISTS sub-query. Inside a sub-query there are two macros:

• {master} will be replaced by master entity alias
• {self} will be replaced by sub-query entity alias

  
      UB.Repository('uba_user').attrs(['ID', 'name']) //select users
  // who are not disabled
  .where('disabled', '=', 0)
  // which allowed access from Kiev
  .where('trustedIP', 'in',
   UB.Repository('geo_ip').attrs('IPAddr')
     .where('city', '=', 'Kiev')
  )
  // who do not log in during this year
  .notExists(
   UB.Repository('uba_audit')
     .correlation('actionUser', 'name')  // here we link to uba_user.name
     .where('actionTime', '>', new Date(2016, 1, 1))
     .where('actionType', '=', 'LOGIN')
  )
  // but modify some data
  .exists(
   UB.Repository('uba_auditTrail')
     .correlation('actionUser', 'ID') // here we link to uba_user.ID
     .where('actionTime', '>', new Date(2016, 1, 1))
  )
  .select()
  

Adds grouping

  
      UB.Repository('my_entity').attrs('ID')
  .groupBy('code')
UB.Repository('uba_user').attrs('disabled')
  .groupBy('disabled').select()
UB.Repository('uba_user').attrs(['disabled','uPassword','COUNT([ID])'])
  .groupBy(['disabled','uPassword']).select()
  

Force where expressions to be used in join part of SQL statement instead of where part. Applicable only for not cached entities

  
      // will generate
// SELECT A.ID, B.code FROM tst_document A LEFT JOIN tst_category B
//    ON (B.instanceID = A.ID and B.ubUser = 10)
// instead of
// SELECT A.ID, B.code FROM tst_document A LEFT JOIN tst_category B
//    ON B.instanceID = A.ID
//    WHERE B.ubUser = 10
UB.Repository('tst_document').attrs(['ID', '[category.code]'])
 .where('[category.ubUser]', '=', 10, 'wantInJoin')
 .join('wantInJoin')
 .selectAsObject().then(UB.logDebug)
  

Adds join condition

How many rows to select. If 0 - select all rows started from .start()

  
      // will return first two IDs from my_entity
let store = UB.Repository('my_entity').attrs('ID').limit(2).selectAsObject()
  

Arrange named where expressions in logical order. By default, where expressions are joined by AND logical predicate. It is possible to join it in custom order using logic. Pay attention to condition name we pass as a 4-th parameter to .where()

  
      UB.Repository('my_entity').attrs('ID')
  // code in ('1', '2', '3')
  .where('code', 'in', ['1', '2', '3'], 'byCode')
  // name like '%homer%'
  .where('name', 'contains', 'Homer', 'byName')
  //(birthday >= '2012-01-01') AND (birthday <= '2012-01-02')
  .where('birthday', 'geq', new Date()).where('birthday', 'leq', new Date() + 10)
  // (age + 10 >= 15)
  .where('[age] -10', '>=', {age: 15}, 'byAge')
  // (byCode OR byName) AND (all where items, not included in logic)
  .logic('([byCode]) OR ([byName])')
  

Apply miscellaneous options to resulting UBQL

  
      // this server-side call will select all currency, including deleted
UB.Repository('cdn_currency').attrs(['ID'])
  .misc({__allowSelectSafeDeleted: true}).selectAsArray();
  

Helper method for CustomRepository.misc. Calls misc in case addingCondition is truthy

Adds where condition with NOT EXISTS sub-query. See CustomRepository.exists for sample

Sorting. If expression already exists in order list it direction will be changed or, in case direction === null it will be removed

  
      let repo = UB.Repository('my_entity').attrs('ID').orderBy('code')
let orderedData = await repo.selectAsObject() // ordered. await is for client-side only
repo.orderBy('code', null) // remove order by code
let unorderedData = await repo.selectAsObject() // NOT ordered
  

Adds descend sorting. The same as orderBy(attr, 'desc'). To remove such sorting call orderBy(attr, null)

  
      UB.Repository('my_entity').attrs('ID')
  // ORDER BY code, date_create DESC
  .orderBy('code').orderByDesc('date_create')
  

Must be implemented in descendants as a alias to the most appropriate method

Must be implemented in descendants and return (or resolved for async clients) to array of array representation of result, like this

 {"resultData":{"fields":["ID","name","ID.name"],"rowCount":1,"data":[[10,"admin","admin"]]},"total":1,"__totalRecCount": totalRecCountIfWithTotalRequest}

For repository with ONE attribute returns a flat array of attribute values

  
      // get first 100 all ID's of tst_dictionary entity
UB.Repository('tst_dictionary').attrs('ID').limit(100).selectAsArrayOfValues()
// returns array of IDs: [1, 2, 3, 4]
  

Must be implemented in descendants and return (or resolved for async clients) to array of object representation of result, like this

 [{"ID":3000000000004,"code":"uba_user"},{"ID":3000000000039,"code":"uba_auditTrail"}]

Must be implemented in descendants and return (or resolved for async clients) to DataSet class instance, implemented in caller level. It can be:

• {TubDataStore} for in-server context
• {UB.ux.data.UBStore} for UnityBase adminUI client
• array of array data representation for UnityBase remote connection
• etc.

Select a single row by ID. If result is empty - returns undefined If result is not empty - returns object

  
      UB.Repository('uba_user').attrs('name', 'ID').selectById(10).then(UB.logDebug)
// will output: {name: "admin", ID: 10}
  

Execute select and returns a value of the first attribute from the first row

  
      UB.Repository('uba_user')
  .attrs('name')
  .where('ID', '=', 10)
  .selectScalar().then(UB.logDebug) // will output `admin`
**WARNING** does not check if result contains the single row
  

Select a single row. If ubql result is empty - return undefined

  
      UB.Repository('uba_user').attrs('name', 'ID').where('ID', '=', 10)
 .selectSingle().then(UB.logDebug)
// will output: {name: "admin", ID: 10}
**WARNING** method does not check if result contains the single row and always returns a first row from result
  

Retrieve first start rows

  
      let store = UB.Repository('my_entity').attrs('ID')
 //will return ID's from 15 to 25
 .start(15).limit(10).select()
  

Construct a UBQL JSON

  
      let repo = UB.Repository('my_entity').attrs('ID').where('code', '=', 'a')
let inst = UB.DataStore(my_entity)
inst.run('select', repo.ubql())
  

Retrieve data from server using methodName entity method. By default, select method will be used

Adds where expression

• the expression may contain one of the following functions: 'SUM', 'COUNT', 'AVG', 'MAX', 'MIN', 'CAST', 'COALESCE', 'LENGTH', 'LOWER', 'UPPER', 'DAY', 'MONTH', 'YEAR', 'ROUND', 'FLOOR', 'CEILING'

• for PostGIS ST_* (Topological Relationships, Distance Relationships, Measurement Functions) are allowed. Restriction - first argument must be bracket attribute, oll other - either parameters or numeric constant

• for a Date/DateTime attributes special macros #maxdate or #currentdate can be used as a value:

  .where('dateValue', '=', '#maxdate')
  .where('dateTimeValue', '<', '#currentdate')
  

• in and 'notIn` conditions can take a sub-repository as a value parameter value. See CustomRepository.exists for sample

• for details how array parameters binds to DB query see array binding section in database tuning tutorial

  
      UB.Repository('my_entity').attrs('ID')
  // code in ('1', '2', '3')
  .where('code', 'in', ['1', '2', '3'])
  // code in (select code from my_codes where id = 10)
  .where('code', 'in', UB.Repository('my_codes').attrs('code').where('ID', '<', 10))
  // name like '%homer%'
  .where('[name]', 'contains', 'Homer')
  //(birtday >= '2012-01-01') AND (birtday <= '2012-01-02')
  .where('[birtday]', 'geq', new Date()).where('birtday', 'leq', new Date() + 10)
  // (age + 10 >= 15)
  .where('[age] -10', '>=', {age: 15}, 'byAge')
  .where('LENGTH([code])', '<', 5)
  // for condition match expression not need
  .where('', 'match', 'myvalue')
  

Helper method for CustomRepository.where. Calls where in case addingCondition is truthy

  
      let filterString = 'foundAllLikeThis' // or may be empty string
// with whereIf
let repo = UB.Repository('my_entity').attrs('ID').whereIf(filterString, 'myAttr', 'like', filterString)

//without whereIf
let repo = UB.Repository('my_entity').attrs('ID')
if (filterString) repo = repo.where('myAttr', 'like', filterString)
  

Calculate total row number. WARNING!! This is VERY slow operation on DB level in case of many record

Result of calculation is returned in __totalRecCount parameter value in case selectAsArray() client call:

  
      let result = UB.Repository('uba_user')
   .attrs(['ID', 'description'])
   .withTotal().selectAsArray();
console.log('Total count is:', result.__totalRecCount)

// Or into TubDataStore.totalRowCount in case of server side `selectAsStore()` call:
 let store = UB.Repository('uba_user')
   .attrs(['ID', 'description'])
   .withTotal().selectAsStore();
 console.log('Total count is:', store.totalRowCount);