Nodejs: In a controller method, how to perform two different model functions?

Question

I need to render a page with the data of an Author and the listing of their News.

In this case, I am considering two tables: authors and news and their respective models.

Database: Mysql;

Relationship: 1 author, n news.

---

Tables:

authors

email name id

news

id autor_id text title

---

Model Autor

Autor.prototype.getAutor = function(id, callback){
    let result = this._connection.query(`select * from autores where id = ?`, id, callback);
    return result;
}

---

Model Noticia

Noticia.prototype.getNoticiasByAutor = function(autor_id, callback){
    let result = this._connection.query(`select * from noticias where autor_id = ?`, autor_id, callback);
    return result;
}

---

In the controller, I need a showAutorNoticies method that should do something as exemplified below, however, this way does not work.

How to get customer data and list of your news in the same controller method?

---

Controller Autor

// Desejado algo parecido com isto:
 
module.exports.showAutorNoticias = function(application, req, res) {
    let id = req.params.id; // id do autor
    let connection = application.config.dbConnection;
    let Autor = new application.app.models.Autor(connection);
    let Noticia = new application.app.models.Noticia(connection);
 
    // 1 - Obter os dados do Autor (Apenas um exemplo da intenção).
    let autorData = Autor.getAutor(id, function(error, result) {
        return result;           
    });
 
    // 2 - Obter as noticias do autor (Apenas um exemplo da intenção).
    let noticias = Noticia.getNoticiasByAutor(autorData[0].id, function(error, result) {
        return result;
    });
 
    // 3 - Armazenar os dados do autor e suas noticias em um objeto;
    let autorNoticias = {
        id: autorData[0].id.toString(),
        name: autorData[0].nome,
        noticias: {
            noticias: noticias
        }
    }
   
    // 4 - Passar esse objeto para a view.
    res.render('admin/autor-noticias', { autorNoticias: autorNoticias });
}

Answer 1

One way to solve it is by using a INNER JOIN.

Here is an example of what a query would look like to solve this problem:

module.exports.showAutorNoticias = function(application, req, res) {
let id = req.params.id; // id do autor
let connection = application.config.dbConnection;
const sql = 'SELECT n.* FROM noticias n INNER JOIN autores a ON n.autor_id = a.id WHERE a.id = ?'

connection.query(sql, [id], (err, autorData, fields) => {
    if (err) {
        //renderizar página avisando que houve um erro.
    }
    console.log(autorData)
    if (autorData.length === 0) {
        //renderizar página sem artigos.
         res.render('admin/autor-noticias', { autorNoticias: [] });
    }
     // aqui estou apenas supondo mas talvez seja necessário adaptar o código
     // 3 - Armazenar os dados do autor e suas noticias em um objeto;
    let autorNoticias = {
        id: autorData[0].id.toString(),
        name: autorData[0].nome,
        noticias: {
            noticias: noticias
        }
    }
    // 4 - Passar esse objeto para a view.
    res.render('admin/autor-noticias', { autorNoticias: autorNoticias });
    })
}

In this code I implemented the flow that exists in the official lib documentation mysql.

I used a console.log to show the data returned by the database, since I do not know the internal structure of its tables, but with that information can assemble the estrutura de dados who wishes to return to the user.

In my code I check the size of the array autorData.length, I do this because further down in the code you make direct access to the zero index of the array.

It is worth a warning word regarding the use of its parameter in the Where clause should not be used directly without being sanitized because there is a risk that sql Injection, and that if the user passes a string there will be a 500 error in their query.

It will be necessary to treat when the user passes one id of a autor nonexistent in the database (e.g.: -1 or) that in the case of the Inner Join will return 0 lines or null in sql.