CODEWITHSUNDEEP
javascriptpostgresql
user12561026
user12561026

Reputation:

How to get rows from javascript array response?

I have skills in one table, user_skills in other table and getting skills against id from skills table in for loop.I have stored query results in javascript array. I want a array with objects in it. I'm getting array in array structure. As you can see in image that i am getting multiple arrays in array.It should be objects in single array.

response

var userExist = await ctx.app.pool.query("SELECT * FROM USER_SKILLS WHERE user_id = $1",
            [`${user_id}`]);

        var strArray = userExist.rows[0].skill_array.split(",");

        var i;
        var skillsArray = [];
        for (i = 0; i < strArray.length; i++) {

          var findSkill = await ctx.app.pool.query("SELECT skill_name FROM ALL_SKILLS WHERE id = $1",
                [`${strArray[i]}`]);

            skillsArray.push(findSkill.rows);

        }
        console.log('skillsArray', skillsArray);

        ctx.body = {
            status: 200,
            error: false,
            message: "Skills found",
            data: skillsArray
        };

Upvotes: 0

Views: 3741

Answers (2)

404
404

Reputation: 8552

Assuming skill_array is in fact an array in postgres, you can combine both those queries into one:

const query = "SELECT skill_name
               FROM ALL_SKILLS
               WHERE id = ANY((
                   SELECT skill_array
                   FROM USER_SKILLS
                   WHERE user_id = $1
               ))";
const res = await ctx.app.pool.query(query, [`${user_id}`]);

That will be more performant than doing multiple queries.

Then the reason why you're getting an array with arrays is because skillsArray.push(findSkill.rows) puts the whole rows property, which is an array, into your skillsArray array.

It's not entirely clear to me exactly what is the format of the result you want, but I'll assume it's the actual skill names in an array, something like:

{
    message: "Skills found",
    data: [
        "PHP",
        "Node js"
    ]
}

In which case you could restructure your code to be something like this:

const query = "SELECT STRING_AGG(skill_name, ',') AS skill_names
               FROM ALL_SKILLS
               WHERE id = ANY((
                   SELECT skill_array
                   FROM USER_SKILLS
                   WHERE user_id = $1
               ))";
const res = await ctx.app.pool.query(query, [`${user_id}`]);
const skillNames = res.rows[0].skill_names.split(',');
ctx.body = {
    status: 200,
    error: false,
    message: "Skills found",
    data: skillNames
};

I've added a STRING_AGG because I like to get postgres results in a single row if possible rather than have pg read multiple rows sequentially, I believe it will be faster. I'm not using ARRAY_AGG because I don't know how the pg module treats arrays, whether it converts them to string or a js array. So I return one field with the skills concatenated with a comma, e.g. "PHP,Node js", then just need to split that one field by the comma to get the desired array.

Upvotes: 1

ROOT
ROOT

Reputation: 11622

I believe this is what you want

var userExist = await ctx.app.pool.query("SELECT * FROM USER_SKILLS WHERE user_id = $1",
    [`${user_id}`]);

var strArray = userExist.rows[0].skill_array.split(",");

var i;
var skillsArray = [];
for (i = 0; i < strArray.length; i++) {
  var findSkill = await ctx.app.pool.query("SELECT skill_name FROM ALL_SKILLS WHERE id = $1",
        [`${strArray[i]}`]);

    skillsArray.push(findSkill.rows[0]);

}
console.log('skillsArray', skillsArray);

ctx.body = {
    status: 200,
    error: false,
    message: "Skills found",
    data: skillsArray
};

But I as i mentioned in my comment, I suggest that you have your database tables/schema in relationships M:M, in your case, where you can just get the data you need in a single query, and it is considered bad practice to do a query in for(...) loop like this. you should use ORM like Sequelize or any other ORM that you prefer and works well with KOA, I hope this help you in achieving this:

Upvotes: 0

Related Questions