Queries

Running Database Queries

Retrieving All Rows From a Collection

You may use the class extended Mongoloquent to begin a query. The class method returns a fluent query builder instance for the given collection, allowing you to chain more constraints onto the query and then finally retrieve the results of the query using the get method:

import { Mongoloquent } from "mongoloquent";

class User extends Mongoloquent {
	static collection = "users";
	static softDelete = true;
	static timestamp = true;
}

const users = await User.get();

Also, you can using the all method

const users = await User.all();

Retrieving a Single Document From a Collection

If you just need to retrieve a single row from a database collection, you may use the Mongoloquent's first method. This method will return a single object:

const user = await User.where("name", "Udin").first();

// you can access the query result in the data property
console.log(user.data);

To retrieve a single row by its _id key value, use the find method:

const user = await User.find("65ab7e3d05d58a1ad246ee87");

// you can access the query result in the data property
console.log(user.data);

Retrieving a List of Column Values

If you would like to retrieve an array containing the values of a single key, you may use the pluck method. In this example, we'll retrieve a array of user titles:

const titles = await User.pluck("title");

Aggregates

The Mongoloquent also provides a variety of methods for retrieving aggregate values like count, max, min, avg, and sum. You may call any of these methods after constructing your query:

import User from "./yourPath/User";
import Order from "./yourPath/Order";

const users = await User.count();

const price = await Order.max("price");

Of course, you may combine these methods with other clauses to fine-tune how your aggregate value is calculated:

const price = await Order.where("finalized", 1).avg("price");

Select Statements

Specifying a Select Clause

You may not always want to select all columns from a database collection. Using the select method, you can specify a custom "select" clause for the query:

import User from "./yourPath/User";

const users = await User.select("name").select("email").get();

Also, you can passing a array of keys into select method

const users = await User.select(["name", "emai"]).get();

Specifying a Exclude Clause

You may want to exclude specific keys from a database collection. Using the exclude method, you can specify a custom "exclude" clause for the query:

const users = await User.exclude("name").exclude("email").get();

Also, you can passing a array of keys into exclude method

const users = await User.exclude(["name", "emai"]).get();

Basic Where Clauses

Where Clauses

You may use the query where method to add "where" clauses to the query. The most basic call to the where method requires three arguments. The first argument is the name of the key. The second argument is an operator, which can be any of the database's supported operators. The third argument is the value to compare against the column's value.

For example, the following query retrieves users where the value of the votes key is equal to 100 and the value of the age key is greater than 35:

import User from "./yourPath/User";

const users = await User.where("votes", "=", 100).where("age", ">=", 35).get();

For convenience, if you want to verify that a column is = to a given value, you may pass the value as the second argument to the where method. Mongoloquent will assume you would like to use the = operator:

const users = await User.where("votes", 100).get();

As previously mentioned, you may use any operator that is supported by your database system:

users = await User.where("votes", ">=", 100).get();

users = await User.where("votes", "<=", 100).get();

users = await User.where("name", "like", "T").get();

Or Where Clauses

When chaining together calls to the query where method, the "where" clauses will be joined together using the and operator. However, you may use the orWhere method to join a clause to the query using the or operator. The orWhere method accepts the same arguments as the where method:

users = await User.where("votes", ">", 100).orWhere("name", "Jhon").get();

Additional Where Clauses

whereBetween / orWhereBetween

The whereBetween method verifies that a key's value is between two values:

const users = await User.whereBetween("votes", [1, 100]).get();

whereIn / whereNotIn / orWhereIn / orWhereNotIn

The whereIn method verifies that a given key's value is contained within the given array:

const users = await User.whereIn("_id", [
	"01ab7e3d05d58a1ad246ee87",
	"02ab7e3d05d58a1ad246ee87",
	"03ab7e3d05d58a1ad246ee87",
]).get();

The whereNotIn method verifies that the given key's value is not contained in the given array:

const users = await User.whereNotIn("_id", [
	"01ab7e3d05d58a1ad246ee87",
	"02ab7e3d05d58a1ad246ee87",
	"03ab7e3d05d58a1ad246ee87",
]).get();

Ordering, Grouping, Limit and Offset

Ordering

The orderBy Method

The orderBy method allows you to sort the results of the query by a given key. The first argument accepted by the orderBy method should be the key you wish to sort by, while the second argument determines the direction of the sort and may be either asc or desc:

const users = await User.orderBy("name", "desc").get();

To sort by multiple keys, you may simply invoke orderBy as many times as necessary:

const users = await User.orderBy("name", "desc").orderBy("email", "asc").get();

Grouping

The groupBy Method

As you might expect, the groupBy method may be used to group the query results.

users = await User.groupBy("accountId").get();

Limit and Offset

The skip and take Methods

You may use the skip and take methods to limit the number of results returned from the query or to skip a given number of results in the query:

const users = await User.skip(10).take(5).get();

Alternatively, you may use the limit and offset methods. These methods are functionally equivalent to the take and skip methods, respectively:

const users = await User.offset(10).limit(5).get();

Insert Statements

The query also provides an insert method that may be used to insert records into the database collection. The insert method accepts an object of key names and values:

await User.insert({
	email: "udin@mail.com",
	votes: 0,
});

You may insert several records at once by passing an array of object. Each array represents a record that should be inserted into the collection:

await User.insertMany([
	{
		email: "udin@mail.com",
		votes: 0,
	},
	{
		email: "kosasih@mail.com",
		votes: 0,
	},
]);

Update Statements

In addition to inserting records into the database, the query can also update existing records using the update method. The update method, like the insert method, accepts an object of key and value pairs indicating the keys to be updated. You may constrain the update query using where clauses:

await User.where("_id", "03ab7e3d05d58a1ad246ee87").update({
	votes: 1,
});

You may update several records by invoked updateMany

await User.where("votes", 0).updateMany({
	votes: 1,
});

Delete Statements

The query delete method may be used to delete records from the collection. You may constrain delete statements by adding "where" clauses before calling the delete method:

await User.delete();

await User.where("votes", ">", 100).delete();

Also, you can delete multiple documents that match queries by invoking the deleteMany method.

await User.where("votes", ">", 100).deleteMany();

Deleting an Existing Data by its Primary Key

if you know the primary key of the model, you may delete the model without explicitly retrieving it by calling the destroy method. In addition to accepting the single primary key, the destroy method will accept multiple primary keys or an array of primary keys:

import Flight from "./yourPath/Flight";

await Flight.destroy("10ab7e3d05d58a1ad246ee87");

await Flight.destroy(["20ab7e3d05d58a1ad246ee87", "30ab7e3d05d58a1ad246ee87"]);

Deleting Data Using Queries

Of course, you may build an Mongoloquent query to delete all models matching your query's criteria. In this example, we will delete all flights that are marked as inactive.

const deleted = await Flight.where("active", false).deleteMany();

---

Support us

Mongoloquent is an MIT-licensed open source project. It can grow thanks to the support by these awesome people. If you'd like to join them, please read more here.

Sponsors

_

Partners