find() method of TypeORM repositories – Wingdings Translator Online

We explored the TypeORM find() method with which you can perform all sorts of simple and complex queries with the ORM without having to get your hands on the SQL.

We are in the Nest Manual, but this article is more related to the ORMTypeORM than with the framework itself. As we have said on other occasions, NestJS is not married to a particular ORM, but it is very common for TypeORM to be used as the first option. In this case you will need to do more complex searches, so it will be good for you to know the find() method from the TypeORM repositories.

The find() method of TypeORM allows us to adjust the search for the elements of an entity. Until now we have explored it little, doing some very elementary searches, but with the configuration options available in the ORM it is possible to get a lot of juice out of it. As you will see, we can do many things that we would do with it, but without having to write the queries by hand.

In this article we want to explore possibilities of working with the find() method to make simple and complex searches, to obtain data of the relationships defined in the entities and other utilities of consideration for the majority of the applications.

TypeORM find method configuration object

All of the examples we’ll see will use the same mechanism to feed the find() method, which is to pass a configuration object.

this.productsRepository.find({ // Configuration options for finding entity objects });

For our examples we will work with the getAll() method on ProductsService, which we already know from previous .

Useful practical examples for find() of TypeORM

The following points in this article are intended to provide a list of different common practices in using the find() method for TypeORM queries. We have selected these exercises because they are simple and frequent in day-to-day work with TypeORM, but we already noticed that find() in the background has many other very relevant utilities as well.

Select only some fields of an entity with select

We can limit the number of fields of the entity elements that we need to search for.

For example, imagine that you only want to receive the names of the products and their stock.

this.productsRepository.find({ select: });

This would return us the products object like this:

Get relations in TypeORM lookup

By default, when we make a query to deliver objects of an entity, the objects of the related entities do not arrive, unless we request them. If you want to have the data of some related entities in a query, it is possible to get it through the “relations” configuration.

Simply in the “relations” property of the options object that we send to find() we will indicate an array with the related entities that we want to be delivered.

this.productsRepository.find({ relations: });

Now the products will have the indicated relations expanded:

{ “id”: 2, “name”: “Chair”, “description”: “Victorian upholstered chair”, “stock”: 44, “sizes”: , “reviews”: },

It is also interesting that we can get also relations of relationssimply by concatenating with the “.” operator.

this.productsRepository.find({ relations: });

In this way we would obtain the products with their sizes and, in addition, for each size we would obtain all the products that have that size.

Perhaps this example is not the best, but imagine that you need all the products that have been invoiced to all the customers. You could request customer invoices and directly deliver the products of each invoice.

relationships:

Get items by exact search

With the find method we can use the “where” option to send it an object of keys and values, so that it finds us elements that exactly match the values.

this.productsRepository.find({ where: { name: ‘Chair’, stock: 5 } });

This would find us all the products whose name is “Chair” and which also have the value of stock equal to 5. Therefore, the operator for this set of conditions is the AND.

It would be interesting if this method allowed searches for similarities or with operators other than equality. We can achieve this with TypeORM but with other more advanced mechanisms that we will see some examples below.

If we wish, it is also possible to search for identical values without using where. It would suffice to write the property to the options object, provided the property exists on the entity of course.

this.productsRepository.find({ id: 8 });

Now let’s see another example combining two fields, where the values will be related again by the AND operator.

this.productsRepository.find({ name: “chair”, description: “a chair…” });

If we want to do a search using the operator ORinstead of AND we can send an array with several property objects that must match exactly as the value of the where property.

sort results

We can define how we want to order the search elements. To do this we simply indicate the “order” property, in which we assign an object with the names of the properties and if it is ascending or descending.

this.productsRepository.find({ order: { stock: ‘DESC’, name: ‘ASC’ } });

In this example we would have all the products, sorted by stock (placing those with the most stock first) and then in alphabetical order according to the “name” field.

Limit the number of items in the results

We can do the typical SQL “limit” with the “take” option, indicating how many elements we want to be returned.

this.productsRepository.find({ take: 5, });

This will give us only 5 elements. Here the typical thing would be to indicate an order so that the 5 that they give you are the ones you really want.

this.productsRepository.find({ take: 5, order: { name: ‘ASC’ } });

Here we would be obtaining the first 5 products, ordered alphabetically.

How to do pagination with TypeORM using skip

Sooner or later you will need to paginate results in your applications. In order to do element paging we need to combine the “take” option with the “skip” option, which allows us to pass a given set of elements.

Imagine that you want the results in pages of 5 by 5 and that you want the third page of results to be delivered to you. So, to get paginated and get the third page you need to discard the elements that it would show you on the first and second pages, that is, 10 elements.

this.productsRepository.find({ take: 5, order: { name: ‘ASC’ }, skip: 10 });

Advanced query options with find()

All the previous options that we have just seen are classified as basic in the documentation of . Now we are going to look at some of the more useful options that are in the “advanced” classification.

From the SQL point of view they are not as advanced as we say, but they are from the ORM point of view because they require new functions that we have not seen and that we will have to import specifically in the applications.

Search for similarities

It is essential to search using the SQL LIKE operator, to find elements that respond to similarities and not exact searches as the dry where option allowed us.

this.productsRepository.find({ name: Like(‘%chair%’), description: Like(‘%chair%’) });

Here we get the products that have “chair” in their product name and description, at the same time (operator AND).

Very important, all these functions that are called “advanced” have to be imported:

import { Like } from “typeorm”;

If what we want is for them to relate to ORthen we need to go to the where option and indicate the conditions in an array.

this.productsRepository.find({ where: });

This will return all items that have “chair” in their name or description.

Find items that have a field greater than a quantity

Now we are going to see how to get the products that have a field greater than something that we give them. For this there is a function called MoreThan. The first thing is to import it:

import { MoreThan } from “typeorm”;

Then we can use it in the query:

this.productsRepository.find({ stock: MoreThan(10), });

There are very similar functions, such as MoreThanOrEqual or LessThan. The use is very similar to what we have just seen.

Find items that are between one quantity and another

Things can get complicated when we need to search for a stock between two quantities. There is a function called Between that comes to the rescue.

We’re going to look for items that have stock between 3 and 8. First we’re going to need to import the Between function.

import { Between } from ‘typeorm’;

Now we do the query like this.

this.productsRepository.find({ where: { stock: Between(3,8) } });

conclusion

There are many advanced functions for making queries, such as IsNull, In, ILike, Any, etc. that add a lot of power to queries with TypeORM. Ideally, you should have the TypeORM documentation handy.

Just in case what we need doesn’t exist, we still have a function called “Raw” that allows us to do even more advanced searches, indicating a bit of SQL ourselves. But be careful in this case because we will have to protect ourselves against possible SQL injections.