CODEWITHSUNDEEP
mysqlsequelize.js
Jon
Jon

Reputation: 21

Query across two tables in sequelize

I am quite new to sequelize and mySQL and feel like I have tried everything in order to pass a search term ('query') to both the books table (searching against titles) and the authors table (searching against first_name or last_name). In the event of matching any of those values substrings it is to return the whole book and author information as a JSON object. When I just have the query focused on book title, it returns everything just fine. The problem comes in when I try to pass in Author columns. I have tried aliasing, nesting where clauses, everything I can think of to do and nothing I come across on here or online seems to help me figure it out.

search: (req, res) => {
    const { query } = req.query;
    Book.findAll({
      include: [Author],
      where: {
        [Op.or]: [
            { title: { [Op.substring]: query } },
        ]},
    })
      .then((Book) => res.json(Book))
      .catch((err) => {
        console.log(err);
        res.status(500).json(err);
      });
  },

Here is the working code. In the where clause, I want to do { first_name: { [Op.substring]: query } }, for example but it isn't accessing the Author table. In the include statement I have tried aliasing and calling it in the where clause, but that throws a aliasing error saying I have already declared an alias (Author) but when I try to use that as { 'Author.first_name' { [Op.substring]: query } }, it returns that there is no Book.Author.first_name.

I am probably missing something simple, so anyone that might be able to help, let me know what I am doing wrong here!

Upvotes: 1

Views: 375

Answers (1)

Jon
Jon

Reputation: 21

Solved and it was super easy. I was missing the syntax for accessing the separate table which is '$Author.first_name$'.

Upvotes: 1

Related Questions