[Bug]: Using the category filter on the partners index is not showing partners that should be there

[katjam]

Description

Demo-ing filters for category tags on https://climatejustice.placecal.org/partners

Steps to reproduce

1. View dropdown options - all ctegories are presented
2. Select one - no result, no empty text

What you expected to happen

Rakesh says he has added categories, but the few partners I checked have none. I'll need to confirm with him, but this bug should be fixed either way. See #1909

There are a lot of open issues related to filtering. This may be part of a bigger problem we want to tackle. https://github.com/geeksforsocialchange/PlaceCal/milestone/25

[kimadactyl]

That's weird - it works on the hulme site

https://hulme.placecal.org/partners

[katjam]

It may be because there are none in use, so it shows all? Though Rakesh did say he was using them... so I'll bring it up next check in.

[kimadactyl]

The filter logic I can't parse at all, it looks like it's been way way way overcomplicated to me. I'm gonna have to rewrite it.

The categories that are showing up are correct, it's just not showing the matching partners.

I don't know why we need a query anything like this complex, and what it returns doens't seem to match the function names. It should be a simple case of filtering by a html data attribute.

https://github.com/geeksforsocialchange/PlaceCal/blob/f4635a4617f5512e7fb6bc3a87cdd704a8293aa3/app/controllers/concerns/partner_filters.rb#L42-L53

[kimadactyl]

OK - I found the underlying issue here and it's partly my fault!

The problem is that Rails doesn't seem to like me chaining two wheres on the same property.

In this example, 8 is the ID for "Activism" and 28 for "Climate Justice". There is deffo a valid partner that has both. However, the implicit chaining Rails is doing is something like this:

Partner.joins(:tags).where("tags.id in (?)", 8).where("tags.id in (?)", 28)
  Partner Load (14.8ms)  SELECT "partners".* FROM "partners" INNER JOIN "partner_tags" ON "partner_tags"."partner_id" = "partners"."id" INNER JOIN "tags" ON "tags"."id" = "partner_tags"."tag_id" WHERE (tags.id in (8)) AND (tags.id in (28)) /* loading for pp */ LIMIT $1  [["LIMIT", 11]]
=> []

@katjam - do you know if that SQL should be working? I'm a little out my depth here.

It's kinda my fault as there was a for_site_with_tags method I deleted, but I couldn't work out why it existed. Now I think I know but it's not a pretty solution to this!

Note this is the same query if it makes it easier to parse:

Partner.joins(:partnerships).where("tags.id in (?)", 28).joins(:categories).where("tags.id in (?)", 8)
  Partner Load (16.8ms)  SELECT "partners".* FROM "partners" INNER JOIN "partner_tags" ON "partner_tags"."partner_id" = "partners"."id" INNER JOIN "tags" ON "tags"."id" = "partner_tags"."tag_id" AND "tags"."type" = $1 INNER JOIN "partner_tags" "partner_tags_partners_join" ON "partner_tags_partners_join"."partner_id" = "partners"."id" INNER JOIN "tags" "categories_partners" ON "categories_partners"."id" = "partner_tags_partners_