explain query

[bwgjoseph]

Hi,

Is there a similar query to explain in ottoman? mongoose-reference

[AV25242]

@bwgjoseph no there isnt, however if you go to the Couchbase Console (UI) you can see the Advise and Plan tab's that will give probably what you are looking for.

[bwgjoseph]

It would be great to expose an API for this, prefer not to have to go into the UI for the explanation

[AV25242]

Understood, the limitations are that its not exposed via underlying SDK as well and I am not sure if its even exposed by the query service that SDK uses. So it drills down multiple layers :) and hence cannot be supported at the moment.

[bwgjoseph]

Sure.

Would like to know if there is an existing way to convert an ottoman API query to an n1ql query?

Model.find({ name: 'hello' }, { select: 'id, name' }).toN1ql();

// output, may not be accurate but just an example
SELECT id, name FROM bucket.scope.collection WHERE name equal 'hello'

Otherwise, it would be difficult to use the explain in the UI if I'm using ottoman as I won't be writing n1q1 query most of the time

The current Query Builder won't be suitable (unless I'm wrong) since I won't be writing my query using Query Builder in the first place

[AV25242]

Understood, currently the only way to see the underlying N1QL is to build it something like this and then pass the params in the find https://v2.ottomanjs.com/guides/query-builder.html#build-a-query-by-using-parameters

I will keep this in the backlog to be considered post GA

[bwgjoseph]

Can you expand a little bit more of the and then pass the params in the find?

I can build using the query builder and pass it to the .find function in ottoman?

[AV25242]

@bwgjoseph on this we will most likely be exposing the query thats build underneath in the debug mode.

[bwgjoseph]

This issue actually has 2 part. One being in the original post, and another from https://github.com/bwgjoseph/mongoose-vs-ottoman/issues/75#issuecomment-835448937 which I will break out into a separate issue.

As per your reply we will most likely be exposing the query thats build underneath in the debug mode, that would be for which part? I assume is for the latter?

[AV25242]

so there is no means to explain the query via ottoman (reading your original ask) as such that is because this feature is not exposed directly via SDK's (most likely because its not exposed via server as an API may be !). what we would be doing is in the debug mode give you the ability to see the underlying query thats being formed. You can use this query to run in the console and seek the advise and build indexes accordingly. This will be your effort during development time to identify how the query is formed and get advise from the query index advisor to build the appropriate query indexes.

[bwgjoseph]

Alright, I think that's good enough for now, given that the API isn't being exposed.

I think it's a good feature to have those (in future), it would help a lot during development.

[AV25242]

With Debug set to true, you should be able to see the N1QL query with any Ottoman methods that are N1QL based example find.

Available with Beta 2.0

[bwgjoseph]

Hmm, is populate a JOIN query in n1q1?

I tested this query await commentModel.find({}, { populate: 'post' }); and the output was

SELECT `text`,`post`,`id` FROM `testBucket`.`samp`.`samp1` WHERE _type="comment"

Running $and query gives me the following (which seem normal)

SELECT `callsign`,`name`,`operational`,`destination`,`scheduledAt`,`capacity`,`model`,`size`,`type`,`email`,`info`,`expiresAt`,`location`,`extension`,`id` FROM `testBucket` WHERE (callsign="Hawk" AND name="Emirates Airlines") AND _type="Airplane"

And I think it only applies to find, even if I use on findById, it also doesn't generates the query with debug on.

So is this supposed to be for all methods?

[AV25242]

I will get back to you on the populate

On the findById, its a KV operation there is no N1QL generated. So is this supposed to be for all method ? Yes all methods backed by N1QL

[bwgjoseph]

So what are the methods backed by N1QL? How would I know?

[AV25242]

@bwgjoseph
find, findOne, findOneAndUpdate, count, createMany, updateMany and removeMany internally use N1QL queries

On populate doesn't use N1QL query or JOINS like you were asking

• Populate method will populate a document based on KV operation, so is not a part of the N1QL query.

• Populate happens once you have the result of the NIQL then we loop through the items and execute the populate for each document.

Hope this helps !!!

[bwgjoseph]

Great, I think that might be a great addition to the docs.

I'll keep this open for a while longer before closing this.