[CW-2391] Filter on labels not working as intended

[saivasudatta]

Describe the bug

Filter based on labels does not filter out correct conversations.

To Reproduce

1. Add labels in equals that you want in the conversations.
2. Add the labels in not equals that you want to filter out.
3. Click Apply

Expected behavior

Using the above filter the conversations that have child_customer , z_kids_first_audio_received labels and do not have z_kids_second_audio_received label should be present, but we get conversations that has the z_kids_second_audio_received label (4th conversation in the below image).

Environment

Linux VM

Cloud Provider

DigitalOcean

Platform

None

Operating system

No response

Browser and version

No response

Docker (if applicable)

No response

Additional context

Going through the code it seems like the issue lies at the left_outer_join (https://github.com/chatwoot/chatwoot/blob/develop/app/services/conversations/filter_service.rb).

Let us know the correct way to use the filters if we are not using it correctly.

_{From SyncLinear.com | CW-2391}

[pranavrajs]

@saivasudatta Thanks for reporting this and raising the PR. Let me go through the PR this week.

[saivasudatta]

The issue was w.r.t. the query that was being generated,

The original query was:
SELECT "conversations".* FROM "conversations" LEFT OUTER JOIN "taggings" ON "taggings"."taggable_type" = $1 AND "taggings"."context" = $2 AND "taggings"."taggable_id" = "conversations"."id" LEFT OUTER JOIN "tags" ON "tags"."id" = "taggings"."tag_id" WHERE "conversations"."account_id" = $3 AND ( tags.name IN ('child_customer','z_kids_first_audio_received') and tags.name NOT IN ('z_kids_second_audio_received') ) ORDER BY "conversations"."last_activity_at" DESC LIMIT $4 OFFSET $5 [["taggable_type", "Conversation"], ["context", "labels"], ["account_id", 1], ["LIMIT", 25], ["OFFSET", 0]]

the fix we did creates the following query:
SELECT "conversations".* FROM "conversations" WHERE c.id IN ( SELECT conversation_id FROM tagggings ts LEFT JOIN tags t ON t.id = ts.tag_id WHERE t.name IN ('child_customer', 'z_kids_first_audio_received')) AND c.id NOT IN ( SELECT conversation_id FROM tagggings ts LEFT JOIN tags t ON t.id = ts.tag_id WHERE t.name IN ('z_kids_second_audio_received')) ORDER BY "conversations"."last_activity_at" DESC

Previously the query builder would just add the labels to the IN operator and to the NOT IN operator, which did not filter the conversations properly.

In the change we did, for equals we are creating a nested query to get all the conversations which have the labels we need.
For not equals also we create a nested query to get all the conversations which have the labels we don't need.
Then we filter out the conversations where the conversation we want are in IN operator and the conversations which we want to exclude are in NOT IN operator.

[saivasudatta]

@pranavrajs created a PR for your reference
#7774