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')
  

Chunks given repository by options.chunkLength calling a callback for each chunk. To be used on massive operations over entity with potentially hure number of records. Repository fieldList must include ID attribute, resulted rows are ordered by ID (all orderBy from repo are removed by method)

  
      const docStore = UB.DataStore('doc_document')
function updateDocSQL(startID, endID, chunkData) {
  // callback can do direct SQL execution to bypass all JS logic during migrations, for example
  docStore.execSql(`update doc_document set mi_tr_notes = substring(notes, 0, 2000)
    where notes is not null and mi_tr_notes is null and ID >= :startID: and ID <= :endID:`,
    { startID, endID })
  App.dbCommit() // commit update for each chunk to prevent rollback logs to be huge
}
UB.Repository('doc_document').attrs('ID')
  .where('notes', 'notIsNull')
  .where('mi_tr_notes', 'isNull')
  .chunksByID(updateDocSQL)

// use chunk data by passing { callbackUseData: true } to options and call a mixins methods
function updateDocJs(startID, endID, chunkData) {
  chunkData.forEach((row) => {
    //here row is object with attributes, passed in Repository fieldList
    docStore.update({
      execParams: { ID: row.ID, mi_modifyDate: row.mi_modifyDate, someAttr: 'someVal' })
    })
  })_
  App.dbCommit()
}
UB.Repository('doc_document').attrs('ID', 'mi_modifyDate')
  .where('notes', 'notIsNull')
  .where('mi_tr_notes', 'isNull')
  .chunksByID(updateDocJS, { callbackUseData: true })
  

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')
  

Return:

// todo this is TubCachedData structure!!!

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

  
      const usersIDs = UB.Repository('uba_user'),attrs('ID').limit(100).selectAsArrayOfValues()
   // usersIDs is array of IDs [1, 2, 3, 4]
  

Create new, or use passed as parameter TubDataStore and run TubDataStore.select method passing result of CustomRepository.ubql() as config. Do not work for remote connection.

Select a single row by ID. If ubql result is empty - return {undefined}.

Perform select and return a value of the first attribute from the first row

WARNING method do not check repository contains the single row

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

WARNING method do not check repository contains the single row and always return 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);