Search record in two tables with nodejs and mysql

Asked

Viewed 93 times

0

I’m using the NodeJS and Mysql and I need to show in my post listing form. These I search in the table "posts", but in the category item, the category id appears. How do I search for the category name in the table "categories" in the same query? Follow the code:

router.get('/postagens', (req, res) => {
    Postagem.findAll({order:[['id', 'desc']]}).then((postagens) => {
        res.render("admin/postagens", {postagens: postagens})
    }).catch((err) => {
        req.flash("error_msg", "Houve um erro ao listar as postagens")
        res.redirect("/admin")
    })
  });

In Models: postagem.js

const db = require('./db')

const Post = db.sequelize.define('postagens', {
    titulo: {
        type: db.Sequelize.STRING
    },
    slug: {
        type: db.Sequelize.STRING
    },
    descricao: {
        type: db.Sequelize.TEXT
    },
    conteudo: {
        type: db.Sequelize.TEXT
    },

    categoria: {
        type: db.Sequelize.STRING,
   allowNull: false,
  references: {        
      models: 'categorias',
         key: 'id',
    },
  },
})

module.exports = Post


Em Admin.js

//Postagens 
//Listando as postagens
router.get('/postagens', (req, res) => {
     Postagem.findAll({order:[['id', 'desc']]}).then((postagens) => {


         res.render("admin/postagens", {postagens: postagens})
     }).catch((err) => {
         req.flash("error_msg", "Houve um erro ao listar as postagens")
         res.redirect("/admin")
     })
})


postagens.handlebars

<h2>Lista de Postagens</h2>
<hr>
<a href="/admin/postagens/add"><button class="btn btn-success">Nova postagem</button></a>


{{#each postagens}}
  <div class="card mt-4">
      <div class="card-body">
          <h4>{{titulo}}</h4>
          <p>Descrição: {{descricao}}</p>
          <small>Data de criação: {{#formatDate dataValues.createdAt}}{{/formatDate}}</small></p>
          <small>Categoria: {{categoria}}</small><br>
          <a href="/admin/postagens/edit/{{dataValues.id}}"><button class="btn btn-success mt-3">Editar postagem</button></a>
          <br>
          <a href="/admin/postagens/deletar/{{dataValues.id}}"><button class="btn btn-danger mt-4">Deletar postagem</button></a>
      </div>
  </div>
{{else}}
  <p><h4 class="mt-3">Nenhuma postagem registrada!</h4></p>
{{/each}}

My question is very simple: What is the mysql statement that replaces this in the nodejs?

Postagem.findAll().populate("categorias").sort({data:"desc"}).then((postagens) => {
  • Add the object code to the question Postagem

  • use the populate of the Mongoose https://mongoosejs.com/docs/api/query.html#query_Query-populate

  • Thank you Teuuz1994, but I am developing with mysql. I need the instruction in Mysql. Thank you.

No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.