Whenever we want to interact with our database, there needs to be a signal to our backend that we want to make a change, get some data, update an entry, etc. This is where we query our database. A query is a keyword signifying that our backend has been notified we want to perform an action on our data, and the nature of the action is deduced by what query we want to run.
A database query is either an action query or a select query.
- Select Query - To retrieve data from the database.
- Action Query - For insertion, updating, deleting, or other forms of data manipulation.
In this blog, I will be walking you through how to perform some basic queries on your canonic project that has some existing data entries.
We will cover:
- Filtering data
- Searching for an entry or a group of entries
- Sorting query results
Searching your data
Searching for data is the most executed operation on a database because by the virtue of storing your data in a database, it is a given you would want to search for this data to perform some operations in your application.
There are majorly two ways to go about searching your data:
- Running a search query for a unique data entry
- Running a search query for a group of data (we will talk more about this in filtering your data section)
To search for a unique data entry, Let's consider we have a list of products in our database and we want only to fetch a unique data entry here.
Let's take a product inventory project to understand this with real code now!
We have a project which has a few products listed in the products table as you can see below.
On the Left-hand menu inside the graph, you can see there is an endpoints sub-menu that populates the endpoints for this specific table of products.
Let us now click on the product endpoint, because that is the general endpoint where you can get a unique data entry. There will be a similar endpoint for your table pre-created by canonic for you!
- Click the graph node to open up the settings for this endpoint
- Go to the code section and expand it using the bottom left arrow
Here you will see, that the method has the following code:
module.exports = async function getOne({ _id }) {
return Product.findOne({ _id }).populate([]);
}
By default, this method will accept a unique ID for a product in our database and if any entry matches the value for the ID we have passed, we will get that data entry in the response.
Now let us search for Nike Shoes entry in our database by passing its unique ID as an argument to our function.
The best part is you do not actually need to run this code on your frontend to see how this will work, just pass this value in the Input section and press the Test code Button (play icon) in the center to run this code.
As you can see above, we passed in a unique ID mapped to the key _id in an object as the input and we got back our results as expected. Voila!
Filter your data
Filtering data is one of the most essential use-cases of querying a database for any product. There are a lot of use-cases where we might have to reduce the fetched data based on some filters because that's just an efficient thing to do.
To fetch only a subset, we will have to figure out which products we need, and to do that we would need to add constraints. If a product matches the constraint only then do we want is a general understanding.
This is the perfect use for a filter query.
The key difference here is instead of using a .findOne() method in our function, we will leverage .find() method here.
As mentioned above, we will use the same list of products to understand the filter query.
Let's say I only want to search for the Nike Shoes Product because I need that product's data for some operation.
Let us go to the project's graph now!
On the Left-hand menu inside the graph, you can see there is an endpoints sub-menu that populates the endpoints for this specific table of products.
Let us now click on the products endpoint, because that is the general getAll endpoint where you can add your filters to get specific data instead of all entries. There will be a similar endpoint for your table pre-created by canonic for you!
- Click the graph node to open up the settings for this endpoint
- Go to the code section and expand it using the bottom left arrow
You should have a code interface similar to the below screenshot now open!
Here you will see that the method has a code
module.exports = async function getAll(query = {}) {
return Product.find(query).populate([]);
}
As you can see, this method accepts an arg called query and this is where we pass our filters. We can filter based on a key in the table.
For e.g. if I want to filter for a product with the name Nike Shoes I will have to pass a query that helps me achieve this. To get this result, we simply have to pass {"name": "Nike Shoes"} as our query.
Similar to the above example, all you have to do to see if your code runs fine is go to the code tab of this graph node and run it there.
As you can see we get an output for this product which is something like this:
[
{
"name": "Nike Shoes",
"_id": "62750be6d85a0a00096639f1",
"createdAt": "2022-05-06T11:52:06.085Z",
"updatedAt": "2022-05-06T11:52:06.085Z",
}
]
This is one very basic example of how to filter your data based on a field and its value. You can also create complex queries and filter your data as you like! As long as a data entry matches the query, you will get your data.
Sort your query results
Sorting your query results is another major use case that we need for day-to-day operations. One of the classic ones is listing out products. Since we are already talking about a product catalog, the use-case where you need to list products alphabetically as a user might want to see them in alphabetical order, be it sorted from A to Z or Z to A. This is a common pattern seen on e-commerce websites as well and is the perfect place to add a sort query.
For this example, we will be using the getAll API endpoint, similar to what we used in the above section, and making a small modification in the function by chaining a .sort() method with our .find() method.
To give you a little more context let us dive briefly into how the sort method actually works. It simply accepts an object as an argument where you can mention the field which you want to sort and the accepted values for this key will be -1 or 1 . Mapping the key 1 will sort it in ascending order while mapping it with -1 will sort it in descending order.
Let's sort the list of products by their names now!
We have simply chained the sort method and as an argument, we have passed the field we want to sort it by and the order we want to sort it in.
When we run this query we will get the following results:
[
{
"name": "Amul Taaza",
"_id": "62750bddd85a0a00096639ee",
"createdAt": "2022-05-06T11:51:57.116Z",
"updatedAt": "2022-05-06T11:51:57.116Z",
},
{
"name": "Chocolate",
"_id": "62738392c8db87000927fd50",
"createdAt": "2022-05-05T07:58:10.536Z",
"updatedAt": "2022-05-06T11:51:47.080Z",
},
{
"name": "Lays",
"_id": "62750bd7d85a0a00096639eb",
"createdAt": "2022-05-06T11:51:51.375Z",
"updatedAt": "2022-05-06T11:51:51.375Z",
},
{
"name": "Nike Shoes",
"_id": "62750be6d85a0a00096639f1",
"createdAt": "2022-05-06T11:52:06.085Z",
"updatedAt": "2022-05-06T11:52:06.085Z",
}
]
Our data is successfully sorted and that's how we do a sort query!
Thanks for reading! If you find this story helpful, please click the 👏 button and share it to help others find it! Feel free to leave a comment 💬 below.
Top comments (0)