40-Nodejs Course 2023: Database CRUD: Count, First, Latest and last methods

In our previous article we saw how to use the query builder in the crud model, now let's use it again to add more methods to our crud model.

Count Method

Now, Let's create a new method in our crud model to count records, as it can be used directly.

// src/core/database/model/curd-model.ts
import queryBuilder from "../query-builder/query-builder";

export default abstract class CrudModel extends BaseModel {
  // ...
  /**
   * Count the number of documents based on the given filter
   */
  public static async count<T>(
    this: ChildModel<T>,
    filter: Filter = {},
  ): Promise<number> {
    return queryBuilder.count(this.collectionName, filter);
  }

We can now use it like this:

const count = await User.count(); // count all users
// count users with age 33
const countAges = await User.count({ age: 33 });

First method

We have a method to find a document by id (find method), or find it by certain column (findBy method), we also have a method called list to filter documents by multiple columns, but we don't have a method to get the first document based on the given filter.

So let's create a new method called first to get the first document based on the given filter.

// src/core/database/model/curd-model.ts
import queryBuilder from "../query-builder/query-builder";

export default abstract class CrudModel extends BaseModel {
  // ...
  /**
   * Get the first document based on the given filter
   */
  public static async first<T>(
    this: ChildModel<T>,
    filter: Filter = {},
  ): Promise<T | null> {
    const result = await queryBuilder.first(this.collectionName, filter);

    return result ? this.self(result as ModelDocument) : null;
  }

Last method

Works exactly like the first method, but it gets the last document based on the given filter.

This one is not created in the query builder, so we will create it first there.

// src/core/database/query-builder/query-builder.ts

import { Document, Filter } from "../model/types";
import { FindOptions, WithId } from "mongodb";

export class QueryBuilder {
    // ..
  /**
   * Find last matched document for the given filter
   */
  public async last(
    collectionName: string,
    filter: Filter,
    filterOptions?: FindOptions,
  ) {
    const query = this.query(collectionName);

    const documents = await query
      .find(filter, filterOptions)
      .sort({
        _id: "desc",
      })
      .limit(1)
      .toArray();

    return documents.length === 1 ? documents[0] : null;
  }
}

We used the _id as a sort option to order the document using _id in a descending order, so the last document will be the first document in the array.

Now let's use it in our crud model.

// src/core/database/model/curd-model.ts
import queryBuilder from "../query-builder/query-builder";

export default abstract class CrudModel extends BaseModel {
  // ...
  /**
   * Get the last document based on the given filter
   */
  public static async last<T>(
    this: ChildModel<T>,
    filter: Filter = {},
  ): Promise<T | null> {
    const result = await queryBuilder.last(this.collectionName, filter);

    return result ? this.self(result as ModelDocument) : null;
  }

Latest method

Works exactly like list but will reorder the documents based on the _id column.

Let's create it first in the query builder

// src/core/database/query-builder/query-builder.ts

import { Document, Filter } from "../model/types";
import { FindOptions, WithId } from "mongodb";

export class QueryBuilder {
    // ..
  /**
   * Find all matched documents for the given filter
   */
  public async latest(
    collectionName: string,
    filter: Filter,
    filterOptions?: FindOptions,
  ) {
    const query = this.query(collectionName);

    const documents = await query
      .find(filter, filterOptions)
      .sort({
        _id: "desc",
      })
      .toArray();

    return documents;
  }
}

Now let's use it in our crud model.

// src/core/database/model/curd-model.ts

import queryBuilder from "../query-builder/query-builder";

export default abstract class CrudModel extends BaseModel {
  // ...
  /**
   * Get all documents based on the given filter
   */
  public static async latest<T>(
    this: ChildModel<T>,
    filter: Filter = {},
  ): Promise<T[]> {
    const results = await queryBuilder.latest(this.collectionName, filter);

    return results.map((result) => this.self(result as ModelDocument));
  }

Now our final query builder file will look like:

// src/core/database/query-builder/query-builder.ts
import { FindCursor, FindOptions, WithId } from "mongodb";
import connection, { Connection } from "../connection";
import { Document, Filter } from "../model/types";

export class QueryBuilder {
  /**
   * Connection
   */
  protected connection: Connection = connection;

  /**
   * Make a create operation
   */
  public async create(
    collectionName: string,
    data: Document,
  ): Promise<WithId<Document>> {
    const query = this.query(collectionName);

    const result = await query.insertOne(data);

    return {
      ...data,
      _id: result.insertedId,
    };
  }

  /**
   * Update document for the given filter
   */
  public async update(
    collectionName: string,
    filter: Filter,
    data: Document,
  ): Promise<WithId<Document> | null> {
    const query = this.query(collectionName);

    const result = await query.findOneAndUpdate(
      filter,
      {
        $set: data,
      },
      {
        returnDocument: "after",
      },
    );

    if (result.ok) {
      return result.value;
    }

    return null;
  }

  /**
   * Replace the entire document for the given filter
   */
  public async replace(collectionName: string, filter: Filter, data: Document) {
    const query = this.query(collectionName);

    const result = await query.findOneAndReplace(filter, data, {
      returnDocument: "after",
    });

    return result.ok ? result.value : null;
  }

  /**
   * Update Or create document for the given filter
   */
  public async upsert(
    collectionName: string,
    filter: Filter,
    data: Document,
  ): Promise<WithId<Document> | null> {
    const query = this.query(collectionName);

    const result = await query.findOneAndUpdate(
      filter,
      {
        $set: data,
      },
      {
        returnDocument: "after",
        upsert: true,
      },
    );

    if (result.ok) {
      return result.value;
    }

    return null;
  }

  /**
   * Delete Single document
   */
  public async deleteOne(
    collectionName: string,
    filter: Filter,
  ): Promise<number> {
    const query = this.query(collectionName);

    const result = await query.deleteOne(filter);

    return result.deletedCount;
  }

  /**
   * Delete multiple documents
   */
  public async delete(collectionName: string, filter: Filter): Promise<number> {
    const query = this.query(collectionName);

    const result = await query.deleteMany(filter);

    return result.deletedCount;
  }

  /**
   * Find first matched document for the given filter
   */
  public async first(
    collectionName: string,
    filter: Filter,
    filterOptions?: FindOptions,
  ) {
    const query = this.query(collectionName);

    return await query.findOne(filter, filterOptions);
  }

  /**
   * Find last matched document for the given filter
   */
  public async last(
    collectionName: string,
    filter: Filter,
    filterOptions?: FindOptions,
  ) {
    const query = this.query(collectionName);

    const documents = await query
      .find(filter, filterOptions)
      .sort({
        _id: "desc",
      })
      .limit(1)
      .toArray();

    return documents.length === 1 ? documents[0] : null;
  }

  /**
   * Find list of documents based on the given filter
   */
  public async list(
    collectionName: string,
    filter: Filter,
    filterQuery?: (cursor: FindCursor) => void,
    filterOptions?: FindOptions,
  ) {
    const query = this.query(collectionName).find(filter, filterOptions);

    if (filterQuery) {
      filterQuery(query);
    }

    return await query.toArray();
  }

  /**
   * Find all matched documents for the given filter
   */
  public async latest(
    collectionName: string,
    filter: Filter,
    filterOptions?: FindOptions,
  ) {
    const query = this.query(collectionName);

    const documents = await query
      .find(filter, filterOptions)
      .sort({
        _id: "desc",
      })
      .toArray();

    return documents;
  }

  /**
   * Count documents based on the given filter
   */
  public async count(collectionName: string, filter: Filter): Promise<number> {
    const query = this.query(collectionName);

    return await query.countDocuments(filter);
  }

  /**
   * Get query of the given collection name
   */
  public query(collectionName: string) {
    return this.connection.database.collection(collectionName);
  }
}

const queryBuilder = new QueryBuilder();

export default queryBuilder;

And our final crud model will look like this:

// src/core/database/model/curd-model.ts
import { ObjectId } from "mongodb";
import queryBuilder from "../query-builder/query-builder";
import BaseModel from "./bae-model";
import {
  ChildModel,
  Document,
  Filter,
  ModelDocument,
  PaginationListing,
  PrimaryIdType,
} from "./types";

export default abstract class CrudModel extends BaseModel {
  /**
   * Create a new record in the database for the current model (child class of this one)
   * and return a new instance of it with the created data and the new generated id
   */
  public static async create<T>(
    this: ChildModel<T>,
    data: Document,
  ): Promise<T> {
    const modelData = { ...data };

    // generate a new id
    modelData.id = await this.generateNextId();

    // perform the insertion
    // please note that the create method returns directly the document with `_id` attached to it.
    const result = await queryBuilder.create(this.collectionName, modelData);

    return this.self(result as ModelDocument);
  }

  /**
   * Update model by the given id
   */
  public static async update<T>(
    this: ChildModel<T>,
    id: PrimaryIdType,
    data: Document,
  ): Promise<T | null> {
    // perform the update
    const result = await queryBuilder.update(
      this.collectionName,
      { [this.primaryIdColumn]: id },
      data,
    );

    return result ? this.self(result as ModelDocument) : null;
  }

  /**
   * Replace the entire document for the given document id with the given new data
   */
  public static async replace<T>(
    this: ChildModel<T>,
    id: PrimaryIdType,
    data: Document,
  ): Promise<T | null> {
    // perform the replace
    const result = await queryBuilder.replace(
      this.collectionName,
      { [this.primaryIdColumn]: id },
      data,
    );

    return result ? this.self(result as ModelDocument) : null;
  }

  /**
   * Update the document if it exists, otherwise create a new one
   */
  public static async upsert<T>(
    this: ChildModel<T>,
    id: PrimaryIdType,
    data: Document,
  ): Promise<T> {
    // perform the upsert
    const result = await queryBuilder.upsert(
      this.collectionName,
      { [this.primaryIdColumn]: id },
      data,
    );

    return this.self(result as ModelDocument);
  }

  /**
   * Find document by id
   */
  public static async find<T>(this: ChildModel<T>, id: PrimaryIdType) {
    return this.findBy(this.primaryIdColumn, id);
  }

  /**
   * Find document by the given column and value
   */
  public static async findBy<T>(
    this: ChildModel<T>,
    column: string,
    value: any,
  ): Promise<T | null> {
    const result = await queryBuilder.first(this.collectionName, {
      [column]: value,
    });

    return result ? this.self(result as ModelDocument) : null;
  }

  /**
   * Get the first document based on the given filter
   */
  public static async first<T>(
    this: ChildModel<T>,
    filter: Filter = {},
  ): Promise<T | null> {
    const result = await queryBuilder.first(this.collectionName, filter);

    return result ? this.self(result as ModelDocument) : null;
  }

  /**
   * Get the last document based on the given filter
   */
  public static async last<T>(
    this: ChildModel<T>,
    filter: Filter = {},
  ): Promise<T | null> {
    const result = await queryBuilder.last(this.collectionName, filter);

    return result ? this.self(result as ModelDocument) : null;
  }

  /**
   * List multiple documents based on the given filter
   */
  public static async list<T>(
    this: ChildModel<T>,
    filter: Filter = {},
  ): Promise<T[]> {
    const result = await queryBuilder.list(this.collectionName, filter);

    return result.map(item => this.self(item as ModelDocument));
  }

  /**
   * Get all documents based on the given filter
   */
  public static async latest<T>(
    this: ChildModel<T>,
    filter: Filter = {},
  ): Promise<T[]> {
    const results = await queryBuilder.latest(this.collectionName, filter);

    return results.map(result => this.self(result as ModelDocument));
  }

  /**
   * Paginate records based on the given filter
   */
  public static async paginate<T>(
    this: ChildModel<T>,
    filter: Filter = {},
    page = 1,
    limit = this.perPage,
  ): Promise<PaginationListing<T>> {
    const documents = await queryBuilder.list(
      this.collectionName,
      filter,
      query => {
        query.skip((page - 1) * limit).limit(limit);
      },
    );

    const totalDocumentsOfFilter = await queryBuilder.count(
      this.collectionName,
      filter,
    );

    const result: PaginationListing<T> = {
      documents: documents.map(document =>
        this.self(document as ModelDocument),
      ),
      paginationInfo: {
        limit,
        page,
        result: documents.length,
        total: totalDocumentsOfFilter,
        pages: Math.ceil(totalDocumentsOfFilter / limit),
      },
    };

    return result;
  }

  /**
   * Count the number of documents based on the given filter
   */
  public static async count<T>(
    this: ChildModel<T>,
    filter: Filter = {},
  ): Promise<number> {
    return queryBuilder.count(this.collectionName, filter);
  }

  /**
   * Delete single document if the given filter is an ObjectId of mongodb
   * Otherwise, delete multiple documents based on the given filter object
   */
  public static async delete<T>(
    this: ChildModel<T>,
    filter: PrimaryIdType | Filter,
  ): Promise<number> {
    if (
      filter instanceof ObjectId ||
      typeof filter === "string" ||
      typeof filter === "number"
    ) {
      return queryBuilder.deleteOne(this.collectionName, {
        [this.primaryIdColumn]: filter,
      });
    }

    return await queryBuilder.delete(this.collectionName, filter);
  }
}

🎨 Conclusion

We created roughly 4 new methods, count, first, last and latest methods which we can use it directly from any model or even from the query builder directly.