How to use an operator that has a clause with optional inclusion?

Question

I have the following model:

AuthorModel.hasMany(BookModel);
BookModel.belongsTo(AuthorModel);

Some authors do not have books.

I want to select an author whose name or the title of one of his books matches the search bar.

I can achieve this with the following statement, but only for authors with books in their BookModel

       Author.findOne({
         include: [{
            model: Book,
            where: {
              [Op.or]: [
                  {'$author.name$': 'search string'},
                  { title: 'search string'}
                 ]
               },
             }]
           })

This gives me more or less the following query mysql:

SELECT 
    `author`.`name`,
    `book`.`title`
FROM `author` INNER JOIN `book` 
     ON `author`.`id` = `book`.`authorId`
     AND ( `author`.`name` = 'search string' OR `book`.`title` = 'search string');

The problem here is that if the author has no books, then the result is empty. Even if there is an author who meets the search criteria.

I tried to install include in required: false, which gives left outer join. In this case, I get some inappropriate results. The where clause is omitted.

How do I change my query sequelizeor what will be the correct mysqlquery?

Answer 1

MySql -

SELECT 
    `author`.`name`,
    `book`.`title`
FROM `author` LEFT JOIN `book` 
     ON `author`.`id` = `book`.`authorId`
WHERE ( `author`.`name` = 'search string' OR `book`.`title` = 'search string')

, WHERE, JOIN ... ON

, , squizzle -

Author.findOne({
    where: {
          [Op.or]: [
              {'$author.name$': 'search string'},
              { '$Book.title$': 'search string'}
             ]
           },
    include: [{
        model: Book,           
        required: false
       }]})