-
So we've been taking a look so far at the Model Query Builder. The Model Query Builder runs directly off of our models. We can use the actual column names
-
that we've defined on the models within our queries itself to make things a little bit more developer friendly. And you'll also notice that all of the responses
-
have been of type our model itself. So whenever you're working with the Model Query Builder, it's always going to return back a type of your model itself. So it's always going to be the same structure
-
as your models defined. And that's regardless of whether or not you're specifying any select statements or anything of the sort. So for example, if we specify that we just want back
-
the ID for our topics here and we give this a save, we're still getting back a type of our topic array. And whenever we actually send out a request
-
to this endpoint to get back our results, you'll see that it's not just an array of the underlying IDs. Instead, it's an object with the ID inside of it, still keeping true that it's returning back
-
some reference to our model itself. Now, if we did truly just want this to be an array of IDs, we could wrap our await statement here in parentheses,
-
map over the results, do topic, topic.id to just return back the ID and send this out.
-
And now we just get back an array of the underlying IDs, not wrapped in any sort of object. Now, in addition to the Model Query Builder, which is what we've been taking a look at here so far,
-
we also have access to the Database Query Builder. And this is more of a raw ConnectJS Query Builder. Let's take a look at const db equals await.
-
We can import the Database Query Builder via IOC Adonis Lucid Database. We can specify the table that we want to run this query against by doing dot from. This will be our topics table.
-
And you'll notice that we're using the underlying table name from the database itself. None of the names that we have defined inside our model will work whenever we're working
-
with the Database Query Builder. The key difference here between these two is that the Model Query Builder runs through our models and we can use our model names, relationships, et cetera,
-
with the Model Query Builder. And it will always return back a reference to our model, regardless of what we're selecting or joining in. The Database Query Builder
-
runs directly through our database. It doesn't touch any of our models. It doesn't run any hooks. It just goes directly to the database. Because of that, we don't have any actual types
-
running through this database. So if we take a look at the type of a response, currently it's just an array of any. So if we go ahead and add DB into our JSON response here,
-
I'm gonna get rid of the select on our Model Query Builder here temporarily. Now our two queries here match in terms of what we would expect. We're just getting back all of our topics from our Topic Model Query Builder.
-
And we're just getting back all of our topics from our Database Query Builder. So let's give this a save, give this a run. Here is our Topics Query Builder result. You'll see that we get back exactly
-
what we've been working with here so far. For our Database Query Builder, we get back the exact same result. And that's because we're looking at the serialized response of our topics
-
and the serialized result of our database, which at the end of the day will match. Now let's take a second to take a look at the code implementation here.
-
So it's console.log, our topics and our DB. And then let's open up our terminal here. I'm gonna open this up all the way and let's send the request out.
-
Okay, same serialized results that we got before. But if we jump back into our console, our database results match that, that we're getting with our serialized results
-
for the Database Query Builder. But our Model Query Builder you'll see is turning back actual instances of our model itself. So through the Model Query Builder,
-
we can still access all the information in terms of our model itself, including the underlying data that we would then get back. So the key difference between the Model Query Builder
-
that we're taking a look at the results here for and the Database Query Builder is what it's referencing and going through. The Model Query Builder will always go through the model.
-
The Database Query Builder just goes directly to the database and will return back whatever the database returns. Let's go ahead and collapse this down here for a second and let's add these selects back.
-
For our Database Query Builder, we can do the exact same thing, select. You'll also notice that the where statements and everything that we've worked with in the prior two lessons is applicable here.
-
We'll take a look at the main difference here between that next. Okay, let's give this a save. I'm gonna scroll all the way down on our terminal here, jump back into Insomnia, send this off again.
-
Again, the underlying serialized results match here. But if we take a look at the console, we're still getting back a reference of our topic.
-
It looks the exact same as the results before. The only difference is going to be the underlying attributes that we have populated. In terms of our database results, again,
-
it matches exactly what we have for the serialized results. I'm gonna go ahead and collapse our terminal away, get rid of our console log. And now one last thing to take a look at with the Database Query Builder
-
is the difference between our column references. So previously, whenever we sorted our Model Query Builder, we did an order by, and the only one that we have that's camel-cased is our createdAt and updatedAt.
-
So we'll go ahead and do that here, and we'll do it in descending order. So for the order by here, we're using the column name as it's defined in our model, which is camel-cased createdAt. For our Database Query Builder,
-
whenever we do an order by here, we're going to want to reference the column name as it's defined in our database, which is created_at.
-
And again, we can do descending here as well. Give this a save, jump back into Insomnia, send this off. And again, you'll see the underlying serialized results match.
-
The main difference again is the types of the response. For the Model Query Builder, it's going to be a type topic array. And for the Database Query Builder, it's going to be an any array. How we reference the table name,
-
the Model Query Builder will use the table name as it's defined in the model. And the Database Query Builder, we need to actually specify the table name that we want to use as it's defined in the database. And the same goes for the columns.
-
We need to use the column as it's defined in the model for the Model Query Builder, and the column as it's defined in the database for the Database Query Builder. One last notation is that the Model Query Builder
-
is forgiving, so you can use the column names as they're defined in the database as well. So let's switch that to created_at, send this off, and we get back the exact same thing.
-
It will work just fine because at the end of the day, it will double check against the database itself if it cannot find a model reference.
