How to use an operator in where clause with an optional include?

Question

I have the following Model:

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

Some authors have no books.

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

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 mysql query:

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, if an author has no books, then the result is empty. Even if there is an author that matches the search criteria.

I tried to set the include to required: false, which gives a left outer join. In that case, I get some not matching results. The where clause is omitted.

How do I have to change my sequelize query, or what would be the proper mysql query?

Answer 1

The MySql query should probably be something like

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')

Note how here filtering condition is in WHERE rather than part of JOIN ... ON clause

Basing on the example at Top level where with eagerly loaded models you squizzle query should probably be something like

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