Backend Knowledge Sharing #19

Google Spreadsheet Functions, JavaScript Execution Context and Hoisting, Objection.js: ORM for Node.js

Table of Contents

1. Useful Google Spreadsheet Functions
2. JavaScript Execution Context and hoisting
3. Building models and querying with objection.js

Useful Google Spreadsheet Functions

Most spreadsheet app, has a bunch of built-in formulas for accomplishing a number of statistical and data manipulation tasks. You can also combine formulas to create more powerful calculations and string tasks together.

This week Akita Nakarmi shared some of here favorite and useful functions that makes life easier while working with excel sheet provided by a client and manipulate and extract data easily.

UNIQUE

Returns unique rows in the provided source range, discarding duplicates. Rows are returned in the order in which they first appear in the source range.

Sample Usage

UNIQUE(B2:B7)
UNIQUE({1, 2; 3, 4; 5, 6})

Syntax

UNIQUE(range)   //range - The data to filter by unique entries.

VLOOKUP

Vertical lookup. Searches down the first column of a range for a key and returns the value of a specified cell in the row found.

Sample Usage

VLOOKUP(10003, A2:B26, 2, FALSE)

Syntax

VLOOKUP(search_key, range, index, [is_sorted])
// example 
VLOOKUP(A9, $A$2:$B$5, 2, FALSE)

Note: If is_sorted is set to TRUE or omitted, and the first column of the range is not in sorted order, an incorrect value might be returned. If VLOOKUP doesn’t appear to be giving correct results, check that the last argument is set to FALSE.

CONCAT and CONCATENATE

CONCAT

Returns the concatenation of two values. Equivalent to the `&` operator.

Syntax
CONCAT(value1, value2)

Sample Usage

CONCAT(“de”,”mystify”) // "demystify"
CONCAT(17,76)          //1776

CONCATENATE

Appends strings to one another.

Syntax
CONCATENATE(string1, [string2, …])

Sample Usage

CONCATENATE(“Welcome”, “ “, “to”, “ “, “Sheets!”)
CONCATENATE(A1,A2,A3)
CONCATENATE(A2:B7)

Faster Multiple Criteria Lookups with VLOOKUP and CONCATENATE

we can use VLOOKUP and pair it with another CONCATENATE to do the multiple criteria part of the lookup instead of using expensive array formulas.

Take an example formula is as follows:

=VLOOKUP(CONCATENATE(A2,B2),A5:N1159,9,FALSE)

The CONCATENATE(A2,B2) is the lookup_value and it specifies that we are looking for “ILX2.4”, which is the combined values of Model and Displacement for the Acura ILX 2.4 liter car.

The A5:N1159 is the range for the data table (called the table_array), starting with the first row and column in A5 and ending at the bottom right with cell N1159.

The number 9 is the col_index_num, which means we want the 9th column in the table_array.

The FALSE input is the range_lookup field. FALSE means we want it to find an exact match and not just a close one.

JavaScript Execution Context and Hoisting

This week Ashish Shakya shared with us some basic but important parts of JavaScript and how it actually executes.

Execution Context

In JS, Execution Context is like a box, a container or a wrapper that stores variables and in which a piece of our code is evaluated and executed.

The default execution context is always a Global Execution Context , i.e,

• Code that is not inside any function
• associated with the global object
• In the browser, that’s the window object

Any code that is not inside any function is said to be in the global execution context. Here declaration of a variable and functions namely first(), second() and third() are in the global execution context.

Then what about the code that is inside the function?

Each time we call a function, it gets its brand new execution context. When we call the function first(), it gets its own execution context. This brand new execution context is put on top of the existing global execution context, forming a Execution Stack .

Inside the first() function, the variable a is stored in its variable context for this function. Then when we call the second() function, a new execution context will be created and put on top of Execution Stack . The same process is carried out for the third() function too, which creates a new execution context put on top of Execution Stack like shown in the figure above.

Hoisting

In JavaScript, a variable can be used before it has been declared.

Example 1:

var a; // Declare a
a = 50; // Assign 50 to a
elem = document.getElementById("output"); // Find an element
elem.innerHTML = a;        // Display the value of a in the element

Example 2:

a = 50; // Assign 50 to a
elem = document.getElementById("output"); // Find an element
elem.innerHTML = a;        // Display the value of a in the element
var a; // Declare a

Example 1 gives the same result as Example 2.

This is called “hoisting”. Hoisting is JavaScript’s default behavior of moving all declarations to the top of the current scope (to the top of the current script or the current function).

Variables and constants declared with let or const are not hoisted. Read more about theme here.

Building models and querying with objection.js in Node.js

Objection.js is an ORM for Node.js that aims to stay out of your way and make it as easy as possible to use the full power of SQL and the underlying database engine while still making the common stuff easy and enjoyable.

Even though ORM is the best commonly known acronym to describe objection, a more accurate description is to call it a relational query builder. You get all the benefits of an SQL query builder but also a powerful set of tools for working with relations.

This week Pankaj Nepal detailed us about how we can use objection.js with node.js to define models for our application and use the powerful builtin query builder to store and retrieve data with ease.

Defining models and relationships

A Model subclass represents a database table and instances of that class represent table rows. A Model class can define relationships (aka. relations, associations) to other models using the static relationMappings property.

Models can optionally define a jsonSchema object that is used for input validation. Each model must have an identifier column. The identifier column name can be set using the idColumn property. idColumn defaults to “id”.

User Model:

const { Model } = require('objection');
class User extends Model {
  static get tableName() {
    return 'users';
  }
static get idColumn() {
    return 'id';
  }
}
export default User

Learn about Models in objection.js here.

Basic queries

All queries are started with one of the Model methods query, $query or $relatedQuery. All these methods return a QueryBuilder instance that can be used just like a knex QueryBuilder but they also have a bunch of methods added by objection.

Find queries
Find queries can be created by calling Model.query() and chaining query builder methods for the returned QueryBuilder instance.

// Fetch an item by id
const user = await User.query().findById(1);
console.log(user.email)
//Fetch all people from the database
const users = await User.query();
console.log(user[0] instanceof User); // → true

Insert queries

Insert queries are created by chaining the insert method to the query. The inserted objects are validated against the model’s jsonSchema. If validation fails the Promise is rejected with a ValidationError.

const user = await User
  .query()
  .insert({ firstName: 'John', lastName: 'Doe', email: 'john_dae@person.com' })
console.log(user instanceof User); // --> true

User Controller:

import User from "models/User";
class UserController {
  async index(req, res) {
    const users = await User.query();
    res.json(users);
  }
  
  async store(req, res) {
    const data = req.body;
    console.log(data);
    let user = null
    
    try{
      user = await User.query().insert(data);
    }catch(error){
      console.log(error)
    }
    
    res.send(user);
  }
}
export default new UserController();

Read more about queries in objection.js here.

If you are interested in reading more of our blogs,

Backend Knowledge Sharing #18

Frontend-Design Knowledge Sharing #18

Happy Holidays…