query multiple tables with knex.js

Question

I want to render with Expres.js and knex.js two tables using for that only one get function in order to use the data from both tables in one HTML template. It works when I query only one table (schools or students) but I don't know how to do with two tables. Any suggestion?

app.get('/schools', function(req, res) {

    knex.select()
    .from('schools', 'students')
    .then(function(schools, students) {
        res.render('schools', {
            schools: schools,
            students: students
        });
    }).catch(function(error) {
        console.log(error);
    });
});

Solution 1

your approach indeed works, however i suggest you this idiom in order to avoid the promise hell (see the link for even better examples.)

router.get("/schools",function(req,res){
  var schools
  knex("schools").select().then(function(ret){
    schools=ret
    return knex("students").select()
  }).then(function(students){
    res.render("schools",{
      students: students,
      schools: schools
    })
  })
})

Solution 2

You need to use join to use multiple tables on the basis of some reference key Here is example of join two tables at with reference key

table 1:users And table2: accounts

And reference key is user's primary key

.then(function() {
  return knex('users')
    .join('accounts', 'users.id', 'accounts.user_id')
    .select('users.user_name as user', 'accounts.account_name as account');
})

Hope this can give you better idea.

For more reference see the Docs

Solution 3

I found the solution, to the problem and it is working, just adding a new query to the .then of the previous one and passing it as an argument so I can render both tables to the same .html and use them independently.

    knex.select()
    .from('schools')
    .then(function(schools){
        knex.select()
        .from('students')
        .then(function(students) {
            res.render('schools', {
                students: students,
                schools: schools
            });
        });
    }).catch(function(error) {
        console.log(error);
    });

Comments

[karliatto]

Thank you for your answer adbulbarik, but using join I will create a one table with the data together, but what I wanted was to render two tables. May be I didn't explain properly. Luckily I found the way that works and I will post it.

[karliatto]

Thanks! it works in the same way as the one I posted but the structure is better.

[igasparetto]

This is bad as you are revisiting the database several times. Use Join instead.