How can I get all the related rows when using IN to filter?-CodePudding

This is what my query looks like:

select "spaces"."id" as "id",
  "spaces"."user_id" as "user_id",
  "spaces"."type" as "type",
  "spaces"."latitude" as "latitude",
  "spaces"."longitude" as "longitude",
  "categories"."category_id" as "categories:category_id"
  from "spaces"
  left join "spaces_categories" as "categories" on "categories"."space_id" = "spaces"."id"
  where "categories"."category_id" in ($)
  and ST_DWithin(spaces.geometry::geography, ST_SetSRID(ST_Point($,$), 4326)::geography, $)

I am using pg 14.1

I am trying to find all spaces within a radius that meet the requested category Ids, along with all the categories for those spaces.

A space can have many category IDs, so if I search for category ids (1,2), and there happens to be 2 space results (and assume each space has 4 categories, 1,2,3,4), I would be expecting 8 rows. However, my query only returns 4 rows, for categories 1,2.

How can I update my query so I can get all the categories, as long there is some overlap?

Is it due to my IN clause?

CodePudding user response：

And EXISTS subquery should do the trick:

SELECT s.id
     , s.user_id
     , s.type
     , s.latitude
     , s.longitude
     , c.category_id AS "categories:category_id"
FROM   spaces                 s
LEFT   JOIN spaces_categories c ON c.space_id = s.id
WHERE  st_dwithin(s.geometry::geography, st_setsrid(st_point($,$), 4326)::geography, $)
AND    EXISTS (
   SELECT FROM spaces_categories x
   WHERE  x.space_id = s.id
   AND    x.category_id IN ($)
   );

There are many other ways, but this should be fastest and clearest.

The cast from geometry to geography seems worrisome for performance, though. Do you have an expression index covering that?