Merge Failure When Using Raw Where Query With Scope

[klondikemarlen]

Issue Creation Checklist

• I understand that my issue will be automatically closed if I don't fill in the requested information
• I have read the contribution guidelines

Bug Description

When I try to chain "withScope" on a model, when using a raw SQL where clause it doesn't merge the successive scopes with "and". This kind of thing used to work in Sequelize 6 via whereMergeStrategy, but seems broken in Sequelize 7.0.0-alpha.40

Reproducible Example

import { Op, WhereOptions, sql, ModelStatic } from "@sequelize/core"
import { User } from "@/models"

const terms = ["aaa", "bbb", "ccc"]

let userScope: ModelStatic<User> = User
for (const term of terms) {
  const termPattern = `%${term}%`
  const where = sql`LOWER(first_name) like ${termPattern} OR LOWER(last_name) like ${termPattern} OR LOWER(display_name) like ${termPattern}`
  userScope = userScope.withScope({ where })
}

const userResults3 = await userScope.findAll({
  logging: console.log,
})

which produces the SQL

SELECT
  [id],
  ...
  [deleted_at] AS [deletedAt]
FROM
  [users] AS [User]
WHERE
  [User].[deleted_at] IS NULL
  AND (
    LOWER(first_name) like N'%ccc%'
    OR LOWER(last_name) like N'%ccc%'
    OR LOWER(display_name) like N'%ccc%'
  );

What do you expect to happen?

I would expect it to do this

import { Op, WhereOptions, sql, ModelStatic } from "@sequelize/core"
import { User } from "@/models"

const terms = ["aaa", "bbb", "ccc"]

const userWhere: {
  [Op.and]?: WhereOptions<User>[]
} = {}
const userWhereConditions: WhereOptions<User>[] = terms.map((term: string) => {
  const termPattern = `%${term}%`
  return sql`LOWER(first_name) like ${termPattern} OR LOWER(last_name) like ${termPattern} OR LOWER(display_name) like ${termPattern}`
})

userWhere[Op.and] = userWhereConditions
const userResults2 = await User.findAll({
  where: userWhere,
  logging: console.log
})

which produces the SQL

SELECT
  [id],
  ...
  [deleted_at] AS [deletedAt]
FROM
  [users] AS [User]
WHERE
  [User].[deleted_at] IS NULL
  AND (
    (
      LOWER(first_name) like N'%aaa%'
      OR LOWER(last_name) like N'%aaa%'
      OR LOWER(display_name) like N'%aaa%'
    )
    AND (
      LOWER(first_name) like N'%bbb%'
      OR LOWER(last_name) like N'%bbb%'
      OR LOWER(display_name) like N'%bbb%'
    )
    AND (
      LOWER(first_name) like N'%ccc%'
      OR LOWER(last_name) like N'%ccc%'
      OR LOWER(display_name) like N'%ccc%'
    )
  );

What is actually happening?

As far as I can tell its just replacing the query instead of merging it.

Environment

• Sequelize version: 7.0.0-alpha.40
• Node.js version: v20.10.0
• If TypeScript related: TypeScript version: 5.4.5
• Database & Version: docker image mcr.microsoft.com/mssql/server:2022-latest
• Connector library & Version: "@sequelize/mssql": "^7.0.0-alpha.40",

Would you be willing to resolve this issue by submitting a Pull Request?

• Yes, I have the time and I know how to start.
• Yes, I have the time but I will need guidance.
• No, I don't have the time, but my company or I are supporting Sequelize through donations on OpenCollective.
• No, I don't have the time, and I understand that I will need to wait until someone from the community or maintainers is interested in resolving my issue.

---

Indicate your interest in the resolution of this issue by adding the 👍 reaction. Comments such as "+1" will be removed.