Sequelize search "Unknown column 'contact.name' in where clause

Viewed 33

I have a service that is in charge of bringing the tickets with the last message of the users. For this, the Contact, Queue, WhatsApp models were added to the include. The problem is that when adding the Tags model, closely related to "Contact", the service stopped working and response with:

"Unknown column 'contact.name' in where clause

The only thing I added was the relationship with Tags, since it is new. Help me understand? It's like it no longer recognizes the column

interface Request {
   searchParam?: string;
   pageNumber?: string;
   status?: string;
   date?: string;
   showAll?: string;
   userId: string;
   withUnreadMessages?: string;
   queueIds: number[];
}

interface Response {
   tickets: Ticket[];
   count: number;
   hasMore: boolean;
}

 const ListTicketsService = async ({
   searchParam = "",
   pageNumber = "1",
   queueIds,
   status,
   date,
   showAll,
   userId,
   withUnreadMessages
}: Request): Promise<Response> => {
    let whereCondition: Filterable["where"] = {
    [Op.or]: [{ userId }, { status: "pending" }],
     queueId: { [Op.or]: [queueIds, null] }
  };
  let includeCondition: Includeable[];

  includeCondition = [
    {
      model: Contact,
      as: "contact",
      attributes: ["id", "name", "number", "profilePicUrl"],
      include: [{
         model: Tags,
         as: "tags",
         attributes: ["name"],
       }]
     },
     {
       model: Queue,
       as: "queue",
       attributes: ["id", "name", "color"]
     },
     {
       model: Whatsapp,
       as: "whatsapp",
       attributes: ["name"]
    },
  ];

   if (showAll === "true") {
     whereCondition = { queueId: { [Op.or]: [queueIds, null] } };
   }

   if (status) {
     whereCondition = {
       ...whereCondition,
       status
     };
   }

  if (searchParam) {
    const sanitizedSearchParam = searchParam.toLocaleLowerCase().trim();

    includeCondition = [
      ...includeCondition,
      {
        model: Message,
        as: "messages",
        attributes: ["id", "body"],
        where: {
          body: where(
            fn("LOWER", col("body")),
            "LIKE",
            `%${sanitizedSearchParam}%`
          )
        },
        required: false,
        duplicating: false
      }
    ];
           
   whereCondition = {
      ...whereCondition,
      [Op.or]: [
         {
          "$contact.name$": where(
             fn("LOWER", col("contact.name")),
             "LIKE",
             `%${sanitizedSearchParam}%`
         )
       },
         { "$contact.number$": { [Op.like]: `%${sanitizedSearchParam}%` } },
        {
        "$message.body$": where(
            fn("LOWER", col("body")),
            "LIKE",
            `%${sanitizedSearchParam}%`
          )
        }
      ]
    };
  }

  if (date) {
    whereCondition = {
      createdAt: {
        [Op.between]: [+startOfDay(parseISO(date)), +endOfDay(parseISO(date))]
      }
    };
  }

  if (withUnreadMessages === "true") {
    const user = await ShowUserService(userId);
    const userQueueIds = user.queues.map(queue => queue.id);

    whereCondition = {
      [Op.or]: [{ userId }, { status: "pending" }],
      queueId: { [Op.or]: [userQueueIds, null] },
      unreadMessages: { [Op.gt]: 0 }
    };
  }

  const limit = 40;
  const offset = limit * (+pageNumber - 1);

  const { count, rows: tickets } = await Ticket.findAndCountAll({
    where: whereCondition,
    include: includeCondition,
    distinct: true,
    limit,
    offset,
    order: [["updatedAt", "DESC"]], logging: console.log
  });

  const hasMore = count > offset + tickets.length;
  return {
    tickets,
    count,
    hasMore
  };
};

export default ListTicketsService;

Another thing is, i don't know how but this is giving me only unique record. The problem is, 1 contact may have n Tags. So, its possible do this query?

Regards

0 Answers
Related