40-Nodejs Course 2023: Database CRUD: Count, First, Latest and last methods
Tags: node, typescript, mongodb, fastify
Published: 2022-11-05 14:46:10

---
title: 40-Nodejs Course 2023: Database CRUD: Count, First, Latest and last methods
published: true
description: 
series: Nodejs Course 2023
tags: nodejs, typescript, mongodb, fastify
cover_image: https://dev-to-uploads.s3.amazonaws.com/uploads/articles/dkh7pf3419grgqjl8lbv.png
# Use a ratio of 100:42 for best results.
---

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.

```ts
// 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:

```ts
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.

```ts
// 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.

```ts
// 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.

```ts
// 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

```ts
// 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.

```ts
// 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:

```ts
// 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:

```ts
// 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.

## 🚀 Project Repository

You can find the latest updates of this project on [Github](https://github.com/hassanzohdy/nodejs-2023)

## 😍 Join our community

Join our community on [Discord](https://discord.gg/pb2vmdfhGf) to get help and support (Node Js 2023 Channel).

## 🎞️ Video Course (Arabic Voice)

If you want to learn this course in video format, you can find it on [Youtube](https://www.youtube.com/playlist?list=PLGO8ntvxgiZMJc7RN2lIq9WmMOlWZGzmz), the course is in Arabic language.

## 💰 Bonus Content 💰

You may have a look at these articles, it will definitely boost your knowledge and productivity.

General Topics

- [Event Driven Architecture: A Practical Guide in Javascript](https://dev.to/hassanzohdy/event-driven-architecture-the-best-paradigm-that-i-love-to-work-with-in-javascript-and-node-js-1gnk)
- [Best Practices For Case Styles: Camel, Pascal, Snake, and Kebab Case In Node And Javascript](https://dev.to/hassanzohdy/best-practices-for-case-styles-camel-pascal-snake-and-kebab-case-in-node-and-javascript-55oi)
- [After 6 years of practicing MongoDB, Here are my thoughts on MongoDB vs MySQL
](https://dev.to/hassanzohdy/after-6-years-of-practicing-mongodb-here-are-my-thoughts-on-mongodb-vs-mysql-574b)

Packages & Libraries

- [Collections: Your ultimate Javascript Arrays Manager](https://dev.to/hassanzohdy/collections-your-ultimate-javascript-array-handler-3o15)
- [Supportive Is: an elegant utility to check types of values in JavaScript](https://dev.to/hassanzohdy/supportive-is-an-elegant-utility-to-check-types-of-values-in-javascript-1b3e)
- [Localization: An agnostic i18n package to manage localization in your project](https://dev.to/hassanzohdy/mongez-localization-the-simplest-way-to-translate-your-website-regardless-your-favorite-framework-4gi3)

 React Js Packages

- [useFetcher: easiest way to fetch data in React Js](https://dev.to/hassanzohdy/usefetcher-the-easiest-way-to-fetch-data-in-react-45o9)

Courses (Articles)

- [React Js: Let"s Create File Manager With React Js and Node Js](https://dev.to/hassanzohdy/lets-create-a-file-manager-from-scratch-with-react-and-typescript-chapter-i-a-good-way-to-expand-your-experience-5g4k)