sailsjs waterline search into two tables

Question

sailsjs waterline combined search for name like '%sam%' from one table and product name like '%nike%' from another table and left join for getting data in terms of user. i know there is no left join in mongo but how to achieve this. i am using mongodb.

var reqData =  req.param('q');
        console.log(reqData);
        // Lookup for records that match the specified criteria
         reqData = '%'+reqData+'%';

        console.log(reqData);
        User.find({ first_name: { 'like': reqData }}).exec(function user(err, user){
                    if (err) return res.json(err);
                    console.log(user);
                    res.json(true);
        }); 
Product.find({ name: { 'like': reqData }}).exec(function user(err, user){
                        if (err) return res.json(err);
                        console.log(user);
                        res.json(true);
            });

score 1 · Answer 1 · answered Nov 23 '15 at 14:32

.find() queries from waterline are async (in fact, all nodeJS/sailsJS tasks are async) which means that you have no guarantees of User.find() returning before Product.find().

You need to do nested queries or use promises using bluebird (default library in sails.js)

score 1 · Answer 2 · answered Nov 24 '15 at 05:45

You can simply achieve this by enabling async in your sails and use it to combine (join) your data.

Enabling it will be in /config/globals.js :

async : true

And to use the code will be :

async.parallel({
    user : function(callback){
       //process user object here
       User.find({ first_name: { 'like': reqData }}).exec(function user(err, user){
           //add return to exit the function
           if (err) return callback(err, null);
           console.log(user);
           return callback(null, user);
       });
    },
    product : function(callback){
        //process product object here
       Product.find({ name: { 'like': reqData }}).exec(function user(err, product){
           if (err) return callback(err, null);
           console.log(product);
           return callback(null, product);
       });             
    }
},
function(err, results){
    // the results array will equal { user : [..], product : [..] } even though
    // the second function had a shorter timeout.
    // you can access the user and product objects by results.user & results.product and do the combine process here
});

The above snippet is based on your code example given from question that has been amended to suit the async parallel method.

SELECT u.name,u.age,p.name,p.brand FROM user u LEFT JOIN product p ON u.id = p.id where u.name like "%xyz%" and p.name like "%xyz%"; i want to do this type of query in waterline but due to mongo restriction i am not able to get the accurate result.\ — Ankur, Nov 24 '15 at 13:10

sailsjs waterline search into two tables

2 Answers2