Loopback-next: How to execute raw MongoDB queries

[sureshkodur]

Description / Steps to reproduce / Feature proposal

How to use raw queries like aggregation on mongodb. Is there any solution ?

Current Behavior

Expected Behavior

I want to use raw queries or other models. For example, Customer model, and Products model. I need to write aggregate query like

db.LoanRequestMappings.aggregate([
    {
        $lookup:{
            .....   
        }
    },
    {   $unwind:"$data" },
    {
        $match:{
            ...
        }
    },
    {
        $lookup:{
            ... 
        }
    },
    {   $unwind:"$LoanRepayment" },
    { $project : { _id: 0} },
    { $out : "tempData" }
])

on product model while getting customer model.

Please help.

_See Reporting Issues for more tips on writing good issues_

[bajtos]

You can execute raw queries using the recently introduced Repository method execute, see https://github.com/strongloop/loopback-next/pull/2681. This works great for SQL connectors.

Based on the code we have in loopback-datasource-juggler and loopback-connector-mongodb, I suspect the execute method will not work for MongoDB, because the connector requires more than just a command string and args array :(

We need to find a way how to improve LB4 API, MongoDB connector API or both, to make it easy to execute raw MongoDB commands.

Until that happens, you can call MongoDB connector's execute method directly.

const repo = // obtain the repository instance, e.g. via @inject()
const result = await new Promise((resolve, reject) => {
  repo.dataSource.connector.execute('LoanRequestMappings', 'aggregate', [
    {
        $lookup:{
            .....   
        }
    },
    {   $unwind:"$data" },
    {
        $match:{
            ...
        }
    },
    {
        $lookup:{
            ... 
        }
    },
    {   $unwind:"$LoanRepayment" },
    { $project : { _id: 0} },
    { $out : "tempData" }
  ],
  (err, data) => {
    if (err) reject(err);
    else resolve(data);
  });
});

[dougal83]

What error is the red underline indicating? Are the parameters correct?

On Tue, 30 Apr 2019, 06:53 sureshkodur, [e-mail] wrote:

Hi @bajtos https://github.com/bajtos , I tried the above code as below,
[image: Screen Shot 2019-04-30 at 11 21 52 AM]
https://user-images.githubusercontent.com/31954801/56942836-60ee0e80-6b3a-11e9-82de-63d5dea0103e.png

Its not working

—
You are receiving this because you are subscribed to this thread.
Reply to this email directly, view it on GitHub
https://github.com/strongloop/loopback-next/issues/2807#issuecomment-487828484,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AAU36CM3NZNOPGC3BZIBKCTPS7NGZANCNFSM4HJBROAA
.

[sureshkodur]

@bajtos said dataSource.connector.execute will work. But it giving undefined connector. See the second image. I tried #2021 . But it is also not working. it says 500 TypeError: Cannot read property 'settings' of undefined at MongoDB.collectionName . Can you help me

[dougal83]

@sureshkodur Sorry I didn't see the images via email earlier. Maybe screen dumps are less than optimal..

It looks like you're falling foul of typescripts type checking. I think you'll need to provide a repo in order to get help with this.

[dougal83]

@sureshkodur Sorry I can't help you based on the code you've shown. Please bear in mind that I'm just learning LB4 too. Without the ability to run your code I'm at a loss. Hopefully someone else can come along and help.

[bajtos]

@sureshkodur is this issue still relevant? Please create a small application we can use to reproduce the problem, see https://loopback.io/doc/en/contrib/Reporting-issues.html#loopback-4x-bugs

[bajtos]

I opened a new issue to describe the more generic feature we are looking for - the ability to execute raw MongoDB queries. See https://github.com/strongloop/loopback-next/issues/3342

It would be great if you can up-vote the issue by adding a :+1: reaction (not a comment please, see https://github.blog/2016-03-10-add-reactions-to-pull-requests-issues-and-comments/), that way we will know how many people are asking for this particular feature.

[VigneshMeem]

You can execute raw queries using the recently introduced Repository method execute, see #2681. This works great for SQL connectors.

Based on the code we have in loopback-datasource-juggler and loopback-connector-mongodb, I suspect the execute method will not work for MongoDB, because the connector requires more than just a command string and args array :(

We need to find a way how to improve LB4 API, MongoDB connector API or both, to make it easy to execute raw MongoDB commands.

Until that happens, you can call MongoDB connector's execute method directly.

const repo = // obtain the repository instance, e.g. via @inject()
const result = await new Promise((resolve, reject) => {
  repo.dataSource.connector.execute('LoanRequestMappings', 'aggregate', [
    {
        $lookup:{
            .....   
        }
    },
    {   $unwind:"$data" },
    {
        $match:{
            ...
        }
    },
    {
        $lookup:{
            ... 
        }
    },
    {   $unwind:"$LoanRepayment" },
    { $project : { _id: 0} },
    { $out : "tempData" }
  ],
  (err, data) => {
    if (err) reject(err);
    else resolve(data);
  });
});

To resolve as array :
> else resolve(data.toArray());