0
# Data Manipulation
1

2
Methods for modifying, sorting, and transforming query results, including updates, deletions, data aggregation, and result processing.
3

4
## Capabilities
5

6
### Data Modification
7

8
Update or remove records based on query results with event callback support.
9

10
```javascript { .api }
11
/**
12
 * Update records in the query result set
13
 * @param changes - Object containing field updates
14
 * @param runEvent - Whether to trigger event callbacks (default: true)
15
 * @returns Updated QueryResult
16
 */
17
update(changes: object, runEvent?: boolean): QueryResult;
18

19
/**
20
 * Remove records in the query result set
21
 * @param runEvent - Whether to trigger event callbacks (default: true)
22
 * @returns QueryResult (empty after removal)
23
 */
24
remove(runEvent?: boolean): QueryResult;
25
```
26

27
**Usage Examples:**
28

29
```javascript
30
// Update single field
31
db({ id: 1 }).update({ price: 199.99 });
32

33
// Update multiple fields
34
db({ category: "electronics" }).update({
35
  discount: 0.15,
36
  sale: true,
37
  updated: new Date()
38
});
39

40
// Update with event callbacks disabled
41
db({ status: "pending" }).update({ status: "processed" }, false);
42

43
// Remove records
44
db({ expired: true }).remove();
45

46
// Remove with callback events disabled
47
db({ temporary: true }).remove(false);
48

49
// Chain updates
50
db({ department: "Sales" })
51
  .filter({ performance: { lt: 2.0 } })
52
  .update({ needsTraining: true, reviewed: new Date() });
53
```
54

55
### Result Retrieval
56

57
Get query results in various formats for data processing and analysis.
58

59
```javascript { .api }
60
/**
61
 * Get all records as a plain JavaScript array
62
 * @returns Array of record objects
63
 */
64
get(): object[];
65

66
/**
67
 * Get the first record from the result set
68
 * @returns First record object or false if empty
69
 */
70
first(): object | false;
71

72
/**
73
 * Get the last record from the result set
74
 * @returns Last record object or false if empty
75
 */
76
last(): object | false;
77

78
/**
79
 * Get the count of records in the result set
80
 * @returns Number of records
81
 */
82
count(): number;
83

84
/**
85
 * Convert query results to JSON string
86
 * @returns JSON string representation of results
87
 */
88
stringify(): string;
89
```
90

91
**Usage Examples:**
92

93
```javascript
94
// Get all matching records
95
const activeUsers = db({ active: true }).get();
96
console.log('Found', activeUsers.length, 'active users');
97

98
// Get single records (returns false if no results)
99
const firstUser = db().order("created").first();
100
const lastUser = db().order("created").last();
101
const newestUser = db().order("created desc").first();
102

103
// Check if results exist
104
if (firstUser === false) {
105
  console.log("No users found");
106
} else {
107
  console.log("First user:", firstUser.name);
108
}
109

110
// Get counts
111
const totalUsers = db().count();
112
const activeCount = db({ active: true }).count();
113
const inactiveCount = db({ active: false }).count();
114

115
// Export as JSON
116
const jsonData = db({ department: "Engineering" }).stringify();
117
localStorage.setItem('engineeringData', jsonData);
118

119
// Conditional operations
120
if (db({ role: "admin" }).count() === 0) {
121
  console.log("No administrators found!");
122
}
123
```
124

125
### Data Selection and Projection
126

127
Extract specific fields or compute derived values from query results.
128

129
```javascript { .api }
130
/**
131
 * Select specific columns/properties from results
132
 * @param columns - Column names to extract
133
 * @returns Array of values or arrays of values
134
 */
135
select(...columns: string[]): any[] | any[][];
136

137
/**
138
 * Get unique values from one or more columns
139
 * @param columns - Column names to extract unique values from
140
 * @returns Array of unique values (single column) or array of unique value arrays (multiple columns)
141
 */
142
distinct(...columns: string[]): any[] | any[][];
143
```
144

145
**Usage Examples:**
146

147
```javascript
148
// Select single column
149
const names = db({ active: true }).select("name");
150
// Returns: ["Alice", "Bob", "Charlie"]
151

152
// Select multiple columns
153
const userInfo = db({ department: "Sales" }).select("name", "email", "salary");
154
// Returns: [["Alice", "alice@company.com", 75000], ["Bob", "bob@company.com", 68000]]
155

156
// Get unique values from single column
157
const departments = db().distinct("department");
158
// Returns: ["Engineering", "Sales", "Marketing", "HR"]
159

160
const locations = db({ active: true }).distinct("location");
161
// Returns: ["New York", "San Francisco", "Austin"]
162

163
// Get unique combinations from multiple columns
164
const roleDeptCombos = db().distinct("role", "department");
165
// Returns: [["Manager", "Sales"], ["Developer", "Engineering"], ["Manager", "Engineering"]]
166

167
// Combine with filtering
168
const seniorRoles = db({ experience: { gte: 5 } }).distinct("role");
169
const highEarners = db({ salary: { gte: 100000 } }).select("name", "salary", "department");
170
```
171

172
### Data Aggregation
173

174
Perform mathematical operations and statistical calculations on numeric data.
175

176
```javascript { .api }
177
/**
178
 * Calculate sum of numeric columns (all columns are summed together)
179
 * @param columns - Column names to sum
180
 * @returns Single sum value combining all specified columns
181
 */
182
sum(...columns: string[]): number;
183

184
/**
185
 * Find minimum value in a column
186
 * @param column - Column name to analyze
187
 * @returns Minimum value found
188
 */
189
min(column: string): any;
190

191
/**
192
 * Find maximum value in a column
193
 * @param column - Column name to analyze
194
 * @returns Maximum value found
195
 */
196
max(column: string): any;
197
```
198

199
**Usage Examples:**
200

201
```javascript
202
// Calculate sums (single column)
203
const totalSalary = db({ department: "Engineering" }).sum("salary");
204

205
// Calculate combined sum (all columns summed together)
206
const totalCompensation = db({ active: true }).sum("salary", "bonus", "commission");
207
// Returns: single number (salary + bonus + commission for all active users)
208

209
// Find min/max values
210
const youngestAge = db({ active: true }).min("age");
211
const oldestAge = db({ active: true }).max("age");
212
const lowestSalary = db({ department: "Sales" }).min("salary");
213
const highestSalary = db({ department: "Sales" }).max("salary");
214

215
// Aggregate analysis
216
const salesStats = {
217
  count: db({ department: "Sales" }).count(),
218
  totalSalary: db({ department: "Sales" }).sum("salary"),
219
  avgSalary: db({ department: "Sales" }).sum("salary") / db({ department: "Sales" }).count(),
220
  minSalary: db({ department: "Sales" }).min("salary"),
221
  maxSalary: db({ department: "Sales" }).max("salary")
222
};
223

224
// Multiple aggregations
225
const departmentSums = db().distinct("department").map(dept => ({
226
  department: dept,
227
  totalSalary: db({ department: dept }).sum("salary"),
228
  employeeCount: db({ department: dept }).count()
229
}));
230
```
231

232
### Iteration and Transformation
233

234
Process records individually or transform entire result sets.
235

236
```javascript { .api }
237
/**
238
 * Iterate over each record in the result set
239
 * @param callback - Function to call for each record
240
 * @returns QueryResult for chaining
241
 */
242
each(callback: (record: object, index?: number) => any): QueryResult;
243

244
/**
245
 * Transform each record and return new array
246
 * @param callback - Function to transform each record
247
 * @returns Array of transformed values
248
 */
249
map(callback: (record: object, index?: number) => any): any[];
250

251
/**
252
 * Inject record values into string templates
253
 * @param template - String template with {property} placeholders
254
 * @param returnArray - Whether to return array of strings (default: false)
255
 * @returns Single string or array of strings
256
 */
257
supplant(template: string, returnArray?: boolean): string | string[];
258
```
259

260
**Usage Examples:**
261

262
```javascript
263
// Iterate with each
264
db({ active: true }).each(function(user, index) {
265
  console.log(`${index + 1}. ${user.name} - ${user.email}`);
266
  
267
  // Return TAFFY.EXIT to break early
268
  if (index >= 5) return TAFFY.EXIT;
269
});
270

271
// Transform with map
272
const emailList = db({ active: true }).map(function(user) {
273
  return user.email.toLowerCase();
274
});
275

276
const userSummaries = db({ department: "Engineering" }).map(function(user, index) {
277
  return {
278
    id: user.id,
279
    fullName: `${user.firstName} ${user.lastName}`,
280
    seniority: user.experience >= 5 ? "Senior" : "Junior",
281
    position: index + 1
282
  };
283
});
284

285
// Template substitution
286
const emailTemplate = "Hello {name}, your account balance is ${balance}.";
287

288
// Single string (concatenated)
289
const emailBody = db({ id: 123 }).supplant(emailTemplate);
290
// Returns: "Hello John Smith, your account balance is $1250.50."
291

292
// Array of strings (one per record)
293
const emailBodies = db({ active: true }).supplant(emailTemplate, true);
294
// Returns: ["Hello Alice...", "Hello Bob...", "Hello Charlie..."]
295

296
// HTML generation
297
const tableRows = db({ department: "Sales" }).supplant(
298
  "<tr><td>{name}</td><td>{email}</td><td>${salary}</td></tr>",
299
  true
300
);
301
```
302

303
### Asynchronous Operations
304

305
Execute callbacks with optional delays for non-blocking operations.
306

307
```javascript { .api }
308
/**
309
 * Execute callback with query results after optional delay
310
 * @param fn - Callback function to execute
311
 * @param delay - Delay in milliseconds (default: 0)
312
 * @returns QueryResult for chaining
313
 */
314
callback(fn: (results: object[]) => void, delay?: number): QueryResult;
315
```
316

317
**Usage Examples:**
318

319
```javascript
320
// Immediate callback
321
db({ active: true }).callback(function(results) {
322
  console.log('Found', results.length, 'active users');
323
  results.forEach(user => {
324
    console.log('- ' + user.name);
325
  });
326
});
327

328
// Delayed callback
329
db({ needsNotification: true }).callback(function(results) {
330
  results.forEach(user => {
331
    sendNotificationEmail(user.email, user.message);
332
  });
333
}, 1000); // Wait 1 second before executing
334

335
// Chain with other operations
336
db({ status: "pending" })
337
  .update({ status: "processing" })
338
  .callback(function(results) {
339
    console.log('Started processing', results.length, 'items');
340
  })
341
  .filter({ priority: "high" })
342
  .callback(function(results) {
343
    console.log('High priority items:', results.length);
344
  }, 500);
345
```
346

347
### Table Joins
348

349
Perform SQL-like inner joins between TaffyDB instances or query results with flexible condition matching.
350

351
```javascript { .api }
352
/**
353
 * Join current query results with another table using specified conditions
354
 * @param table - TaffyDB instance or QueryResult to join with
355
 * @param conditions - Join conditions (arrays, functions, or mixed)
356
 * @returns QueryResult containing joined records
357
 */
358
join(table: TaffyDatabase | QueryResult, ...conditions: (JoinCondition | JoinFunction)[]): QueryResult;
359

360
// Join condition types
361
type JoinCondition = [string, string] | [string, string, string];  // [leftField, rightField] or [leftField, operator, rightField]
362
type JoinFunction = (leftRow: object, rightRow: object) => boolean;
363
```
364

365
**Usage Examples:**
366

367
```javascript
368
// Basic join on matching fields
369
const users = TAFFY([
370
  { id: 1, name: "Alice", deptId: 10 },
371
  { id: 2, name: "Bob", deptId: 20 },
372
  { id: 3, name: "Charlie", deptId: 10 }
373
]);
374

375
const departments = TAFFY([
376
  { id: 10, name: "Engineering", budget: 500000 },
377
  { id: 20, name: "Sales", budget: 300000 }
378
]);
379

380
// Join users with departments on matching IDs
381
const userDeptInfo = users().join(departments, ["deptId", "id"]);
382
// Results contain combined records with department info merged
383

384
// Join with custom comparison operator
385
const highBudgetUsers = users().join(departments, ["deptId", "===", "id"]);
386

387
// Join with custom function condition
388
const customJoin = users({ name: { like: "A" } }).join(departments, function(user, dept) {
389
  return user.deptId === dept.id && dept.budget > 400000;
390
});
391

392
// Multiple join conditions (all must be true)
393
const complexJoin = users().join(departments, 
394
  ["deptId", "id"],                                    // Basic field matching
395
  function(user, dept) {                               // Custom condition
396
    return user.name.length > 3;
397
  }
398
);
399

400
// Available operators in array conditions
401
const operatorExamples = users().join(departments,
402
  ["deptId", "===", "id"],    // Strict equality (default)
403
  ["deptId", "!==", "id"],    // Not equal
404
  ["deptId", "<", "id"],      // Less than
405
  ["deptId", ">", "id"],      // Greater than
406
  ["deptId", "<=", "id"],     // Less than or equal
407
  ["deptId", ">=", "id"],     // Greater than or equal
408
  ["deptId", "==", "id"],     // Loose equality
409
  ["deptId", "!=", "id"]      // Loose inequality
410
);
411
```
412

413
**Join Result Structure:**
414

415
```javascript
416
// When joining, conflicting field names are prefixed with "right_"
417
const result = users().join(departments, ["deptId", "id"]).get();
418
// Example result record:
419
// {
420
//   id: 1,              // From left table (users)
421
//   name: "Alice",      // From left table (users)
422
//   deptId: 10,         // From left table (users)
423
//   right_id: 10,       // From right table (departments) - prefixed to avoid conflict
424
//   right_name: "Engineering",  // From right table (departments) - prefixed
425
//   budget: 500000      // From right table (departments) - no conflict, no prefix
426
// }
427

428
// Chain joins with other operations
429
const filteredJoin = users({ name: { left: "A" } })
430
  .join(departments, ["deptId", "id"])
431
  .filter({ budget: { gt: 400000 } })
432
  .order("name")
433
  .get();
434
```
435

436
### Advanced Result Processing
437

438
Complex data manipulation combining multiple query operations.
439

440
**Usage Examples:**
441

442
```javascript
443
// Batch processing with pagination
444
function processAllUsers(pageSize = 100) {
445
  const totalUsers = db({ active: true }).count();
446
  const totalPages = Math.ceil(totalUsers / pageSize);
447
  
448
  for (let page = 1; page <= totalPages; page++) {
449
    const users = db({ active: true })
450
      .order("id")
451
      .start((page - 1) * pageSize + 1)
452
      .limit(pageSize)
453
      .get();
454
    
455
    // Process batch
456
    users.forEach(user => {
457
      processUser(user);
458
    });
459
  }
460
}
461

462
// Conditional updates based on aggregations
463
const avgSalary = db({ department: "Engineering" }).sum("salary") / 
464
                  db({ department: "Engineering" }).count();
465

466
db({ department: "Engineering" })
467
  .filter(function() { return this.salary < avgSalary * 0.8; })
468
  .update({ needsReview: true, reviewDate: new Date() });
469

470
// Complex reporting
471
const departmentReport = db().distinct("department").map(dept => {
472
  const deptEmployees = db({ department: dept });
473
  return {
474
    department: dept,
475
    totalEmployees: deptEmployees.count(),
476
    activeEmployees: deptEmployees.filter({ active: true }).count(),
477
    totalSalary: deptEmployees.sum("salary"),
478
    avgSalary: deptEmployees.sum("salary") / deptEmployees.count(),
479
    topPerformer: deptEmployees.order("performance desc").first()
480
  };
481
});
482
```