How to use FULLTEXT search with nested include in sequelize?

Question

I'm using sequelize to work with a MariaDB database and I need to do a full text search on two columns: order.comment and order.orderProducts.product.name.

I have added FULLTEXT indexes on the order.name, product.name and user.name columns.

Here's my code:

if (search) {
  where = Sequelize.literal(
    `MATCH(order.comment) AGAINST('*${search}*' IN BOOLEAN MODE) OR
    MATCH(orderProducts.product.name) AGAINST('*${search}*' IN BOOLEAN MODE) OR
    MATCH(user.name) AGAINST('*${search}*' IN BOOLEAN MODE)`,
  );
}

const orders = await this.orderModel.findAndCountAll({
  subQuery: false,
  limit,
  offset,
  distinct: true,
  order: [['id', 'DESC']],
  where,
  include: [
    {
      model: User,
    },
    {
      model: OrderProduct,
      include: [
        {
          model: Product,
        },
      ],
    },
  ],
});

When I search for something I get an error: Unknown column 'orderProducts.product.name' in 'where clause'

Without MATCH(orderProducts.product.name) AGAINST('*${search}*' IN BOOLEAN MODE) everything works.

I don't understand why this is happening and how can I fix it. I tried different options but without success.

My models:

@Table({
  tableName: 'orders',
  indexes: [{ type: 'FULLTEXT', fields: ['comment'] }],
})
export class Order extends Model<Order> {
  @Column({
    type: DataType.INTEGER,
    primaryKey: true,
    autoIncrement: true,
  })
  id: number;

  @Column({ type: DataType.TEXT, allowNull: false })
  comment: string;

  @HasMany(() => OrderProduct, { foreignKey: 'orderId' })
  orderProducts: OrderProduct[];
}

@Table({
  tableName: 'products',
  indexes: [{ type: 'FULLTEXT', fields: ['name'] }],
})
export class Product extends Model<Product> {
  @Column({
    type: DataType.INTEGER,
    primaryKey: true,
    autoIncrement: true,
  })
  id: number;

  @Column({ type: DataType.STRING, unique: true, allowNull: false })
  name: string;

  @HasMany(() => OrderProduct, { foreignKey: 'productId' })
  orderProducts: OrderProduct[];
}

@Table({ tableName: 'order_products' })
export class OrderProduct extends Model<OrderProduct> {
  @Column({
    type: DataType.INTEGER,
    primaryKey: true,
    autoIncrement: true,
  })
  id: number;

  @Column({ type: DataType.INTEGER, allowNull: false })
  price: number;

  @ForeignKey(() => Product)
  @Column({ type: DataType.INTEGER })
  productId: number;

  @ForeignKey(() => Order)
  @Column({ type: DataType.INTEGER })
  orderId: number;

  @BelongsTo(() => Product, { foreignKey: 'productId' })
  product: Product;

  @BelongsTo(() => Order, { foreignKey: 'orderId' })
  order: Order;
}

P.S. I've tried these variations:

/* ----1---- */
if (search) {
  where = Sequelize.literal(
    `MATCH(product.name) AGAINST('*${search}*' IN BOOLEAN MODE)`,
  );
}
/* ----2---- */
if (search) {
  where = Sequelize.literal(
    `MATCH(Product.name) AGAINST('*${search}*' IN BOOLEAN MODE)`,
  );
}
/* ----3---- */
if (search) {
  where = Sequelize.literal(
    `MATCH(OrderProducts.Product.name) AGAINST('*${search}*' IN BOOLEAN MODE)`,
  );
}

The error is the same.

SQL query on error:

sql: SELECT count(DISTINCT(`Order`.`id`)) AS `count` FROM `orders` AS `Order` LEFT OUTER JOIN `users` AS `user` ON `Order`.`userId` = `user`.`id` LEFT OUTER JOIN `order_products` AS `orderProducts` ON `Order`.`id` = `orderProducts`.`orderId` LEFT OUTER JOIN `products` AS `orderProducts->product` ON `orderProducts`.`productId` = `orderProducts->product`.`id`  WHERE MATCH(orderProducts.product.name) AGAINST('*hello*' IN BOOLEAN MODE); - parameters:[]

Answer 1

You can see Sequelize is joining products like this:

LEFT OUTER JOIN `products` AS `orderProducts->product`

giving it an alias of orderProducts->product, which contains special characters so needs to be enclosed in backticks when used. So your literal sql should use the column:

`orderProducts->product`.name

Escape the backticks to include it in your template literal:

`MATCH(\`orderProducts->product\`.name) AGAINST('*${search}*' IN BOOLEAN MODE)`,