In order to explore the various methods of querying an array table similar to how MySQL works, I have devised this example.
Among the 8 queries presented below, the one that is challenging me is Query (6), which involves using Array.find() in a MySQL DateTime range SELECT.
const tblUsers = [{
id: 101,
user: 'user1',
password: 'password1',
created: '2021-01-01 00:00:01',
modified: '2021-01-01 23:59:59',
status: 'Active',
firstName: 'Sean',
lastName: 'Connery'
},
{
id: 102,
user: 'user2',
password: 'password2',
created: '2021-01-05 00:00:01',
modified: '2021-01-05 23:59:59',
status: 'Inactive',
firstName: 'Bill',
lastName: 'Murray'
},
{
id: 103,
user: 'user3',
password: 'password3',
created: '2021-01-10 00:00:01',
modified: '2021-01-10 23:59:59',
status: 'Active',
firstName: 'Jeniffer',
lastName: 'Connelly'
},
];
let user4: any = {
id: 104,
user: 'user4',
password: 'password4',
created: '2021-01-15 00:00:01',
modified: '2021-01-15 23:59:59',
status: 'Active',
firstName: 'Michelle',
lastName: 'Pfeiffer'
};
// (0) - INSERT INTO tblUsers (id, user, password, ...) VALUES (104, 'user4', 'password4', ...);
tblUsers.push(user4);
// (1) - Select * FROM tblUsers;
let selectAlUsers: any = tblUsers || null;
console.log(tblUsers);
// (2) - Select * FROM tblUsers WHERE id = 103;
let selectUserById: any = tblUsers.find(record => record.id === 102) || null;
console.log(selectUserById);
// (3) - Select * FROM tblUsers WHERE firstName = 'Bill';
let selectUserByFirstName: any = tblUsers.find(record => record.firstName === 'Bill') || null;
console.log(selectUserByFirstName);
// (4) - Select * FROM tblUsers WHERE lastName = 'Murray';
let selectUserByLastName: any = tblUsers.find(record => record.lastName === 'Bill') || null;
console.log(selectUserByLastName);
// (5) - Select * FROM tblUsers WHERE status = 'Inactive';
let selectUsersByStatus: any = tblUsers.find(record => record.status === 'Inactive') || null;
console.log(selectUsersByStatus);
// (6) - Select * FROM tblUsers WHERE created BETWEEN '2021-01-05 00:00:01' AND '2021-01-10 00:00:01';
// let userRecordsByDateTimeRange: any = tblUsers.find(record => record.created === '2021-01-05 00:00:01' && record.created === '2021-01-10 23:59:59');
// console.log(userRecordsByDateTimeRange);
// (7) - UPDATE tblUsers SET modified = '2021-01-06 00:00:00', status = 'Active', ... WHERE id = 102;
function updateUserById(): boolean {
let selectUserById: any = tblUsers.find(record => record.id === 102) || null;
if(!selectUserById) return false;
let user2: any = {
// id: 102,
// user: 'user2',
// password: 'password2',
// created: '2021-01-15 00:00:01',
// modified: '2021-01-15 23:59:59',
status: 'Active',
// firstName: 'Bill',
// lastName: 'Murray'
};
// Execute Update
// selectUserById.user = String(user2.user);
// selectUserById.password = String(user2.password);
// selectUserById.created = String(user2.created);
// selectUserById.modified = String(user2.modified);
selectUserById.status = String(user2.status);
// selectUserById.firstName = String(user2.firstName);
// selectUserById.lastName = String(user2.lastName);
return true;
}
console.log(updateUserById());
// (8) - DELETE FROM tblUsers WHERE id = 102;
function deleteUserById(): boolean {
let selectUserById: any = tblUsers.find(record => record.id === 102) || null;
let userArrayIndex: any = tblUsers.findIndex(record => record.id === 102) || null;
if(!selectUserById) return false;
if(!userArrayIndex) return false;
// Execute Delete
tblUsers.splice(userArrayIndex, 1); // remove 1 element at userArrayIndex
return true;
}
console.log(deleteUserById());