Record Query Utility

1var QueryUtils = Class.create();
2QueryUtils.prototype = {
3
4  /**
5   * Get single record by sys_id with error handling
6   * @param {string} table - Table name
7   * @param {string} sysId - Record sys_id
8   * @returns {GlideRecord|null} Record or null if not found
9   */
10  getById: function(table, sysId) {
11    if (!table || !sysId) {
12      gs.warn('QueryUtils.getById: Missing table or sys_id');
13      return null;
14    }
15
16    var gr = new GlideRecord(table);
17    if (gr.get(sysId)) {
18      return gr;
19    }
20
21    return null;
22  },
23
24  /**
25   * Get records with query string
26   * @param {string} table - Table name
27   * @param {string} query - Encoded query string
28   * @param {number} limit - Maximum results (default: no limit)
29   * @returns {array} Array of GlideRecord objects
30   */
31  getRecords: function(table, query, limit) {
32    var records = [];
33
34    var gr = new GlideRecord(table);
35    if (query) {
36      gr.addEncodedQuery(query);
37    }
38
39    if (limit) {
40      gr.setLimit(limit);
41    }
42
43    gr.query();
44
45    while (gr.next()) {
46      // Push a copy of the record
47      var record = new GlideRecord(table);
48      record.get(gr.sys_id);
49      records.push(record);
50    }
51
52    return records;
53  },
54
55  /**
56   * Check if record exists
57   * @param {string} table - Table name
58   * @param {string} field - Field name
59   * @param {string} value - Field value
60   * @returns {boolean} True if record exists
61   */
62  exists: function(table, field, value) {
63    var gr = new GlideRecord(table);
64    gr.addQuery(field, value);
65    gr.query();
66
67    return gr.hasNext();
68  },
69
70  /**
71   * Count records matching query
72   * @param {string} table - Table name
73   * @param {string} query - Encoded query string (optional)
74   * @returns {number} Count of matching records
75   */
76  count: function(table, query) {
77    var ga = new GlideAggregate(table);
78
79    if (query) {
80      ga.addEncodedQuery(query);
81    }
82
83    ga.addAggregate('COUNT');
84    ga.query();
85
86    if (ga.next()) {
87      return parseInt(ga.getAggregate('COUNT'));
88    }
89
90    return 0;
91  },
92
93  /**
94   * Get distinct values for a field
95   * @param {string} table - Table name
96   * @param {string} field - Field name
97   * @param {string} query - Optional encoded query
98   * @returns {array} Array of distinct values
99   */
100  getDistinctValues: function(table, field, query) {
101    var values = [];
102
103    var ga = new GlideAggregate(table);
104
105    if (query) {
106      ga.addEncodedQuery(query);
107    }
108
109    ga.groupBy(field);
110    ga.query();
111
112    while (ga.next()) {
113      var value = ga.getValue(field);
114      if (value) {
115        values.push(value);
116      }
117    }
118
119    return values;
120  },
121
122  /**
123   * Batch update records
124   * @param {string} table - Table name
125   * @param {string} query - Encoded query string
126   * @param {object} updates - Object with field: value pairs
127   * @param {boolean} skipWorkflow - Skip business rules (default: false)
128   * @returns {number} Number of updated records
129   */
130  batchUpdate: function(table, query, updates, skipWorkflow) {
131    var count = 0;
132
133    var gr = new GlideRecord(table);
134    if (query) {
135      gr.addEncodedQuery(query);
136    }
137
138    gr.query();
139
140    while (gr.next()) {
141      // Apply updates
142      for (var field in updates) {
143        if (updates.hasOwnProperty(field)) {
144          gr.setValue(field, updates[field]);
145        }
146      }
147
148      // Set workflow flag if requested
149      if (skipWorkflow) {
150        gr.setWorkflow(false);
151      }
152
153      gr.update();
154      count++;
155    }
156
157    gs.info('QueryUtils.batchUpdate: Updated ' + count + ' records in ' + table);
158    return count;
159  },
160
161  /**
162   * Delete records matching query
163   * @param {string} table - Table name
164   * @param {string} query - Encoded query string
165   * @param {boolean} skipWorkflow - Skip business rules (default: false)
166   * @returns {number} Number of deleted records
167   */
168  batchDelete: function(table, query, skipWorkflow) {
169    var count = 0;
170
171    var gr = new GlideRecord(table);
172    if (query) {
173      gr.addEncodedQuery(query);
174    }
175
176    gr.query();
177
178    while (gr.next()) {
179      if (skipWorkflow) {
180        gr.setWorkflow(false);
181      }
182
183      gr.deleteRecord();
184      count++;
185    }
186
187    gs.info('QueryUtils.batchDelete: Deleted ' + count + ' records from ' + table);
188    return count;
189  },
190
191  /**
192   * Copy record to another table or same table
193   * @param {string} sourceTable - Source table name
194   * @param {string} sourceSysId - Source record sys_id
195   * @param {string} targetTable - Target table name (can be same as source)
196   * @param {array} excludeFields - Fields to exclude from copy
197   * @returns {string|null} New record sys_id or null if failed
198   */
199  copyRecord: function(sourceTable, sourceSysId, targetTable, excludeFields) {
200    excludeFields = excludeFields || ['sys_id', 'sys_created_on', 'sys_created_by', 
201                                       'sys_updated_on', 'sys_updated_by'];
202
203    var source = new GlideRecord(sourceTable);
204    if (!source.get(sourceSysId)) {
205      gs.warn('QueryUtils.copyRecord: Source record not found');
206      return null;
207    }
208
209    var target = new GlideRecord(targetTable);
210    target.initialize();
211
212    // Copy all fields except excluded ones
213    var fieldNames = source.getFields();
214    for (var i = 0; i < fieldNames.size(); i++) {
215      var fieldName = fieldNames.get(i).getName();
216
217      if (excludeFields.indexOf(fieldName) === -1) {
218        target.setValue(fieldName, source.getValue(fieldName));
219      }
220    }
221
222    var newSysId = target.insert();
223
224    if (newSysId) {
225      gs.info('QueryUtils.copyRecord: Created new record ' + newSysId);
226      return newSysId;
227    }
228
229    return null;
230  },
231
232  /**
233   * Get records with pagination
234   * @param {string} table - Table name
235   * @param {string} query - Encoded query string
236   * @param {number} page - Page number (1-indexed)
237   * @param {number} pageSize - Records per page
238   * @returns {object} {records: array, total: number, page: number, totalPages: number}
239   */
240  getPaginated: function(table, query, page, pageSize) {
241    page = page || 1;
242    pageSize = pageSize || 10;
243
244    // Get total count
245    var total = this.count(table, query);
246    var totalPages = Math.ceil(total / pageSize);
247
248    // Get paginated results
249    var gr = new GlideRecord(table);
250    if (query) {
251      gr.addEncodedQuery(query);
252    }
253
254    // Calculate offset
255    var offset = (page - 1) * pageSize;
256    gr.chooseWindow(offset, offset + pageSize);
257    gr.query();
258
259    var records = [];
260    while (gr.next()) {
261      var record = new GlideRecord(table);
262      record.get(gr.sys_id);
263      records.push(record);
264    }
265
266    return {
267      records: records,
268      total: total,
269      page: page,
270      pageSize: pageSize,
271      totalPages: totalPages
272    };
273  },
274
275  type: 'QueryUtils'
276};