1

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?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
user1354934
  • 8,139
  • 15
  • 50
  • 80
  • The query is malformed. The predicate `where "categories"."category_id" in ($)` silently converts the outer join into an inner join. Please modify the query by either 1) using an inner join, or 2) by accepting nulls in `"categories"."category_id"`. – The Impaler Feb 11 '22 at 23:20
  • Thanks, sorry but can you please explain what you mean by modify the query? Are you saying to move the where one down (before the postgis function), and use and so it is on the join? – user1354934 Feb 11 '22 at 23:24
  • `I am trying to find spaces within a radius, and that meet some categories input.` is not the same as `get all the categories, as long there is some overlap`. Please clarify what you want *exactly*. Start by disclosing your Postgres version. – Erwin Brandstetter Feb 11 '22 at 23:35
  • Ah I see, thank you! I have updated – user1354934 Feb 11 '22 at 23:36

1 Answers1

1

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?

Related:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Thank you! The cast from geometry to geography is because I had not understood postgis, I should have used geography column since I am going to be doing queries to find within meters of a point, or within bounding box. The geometry allow me to query in degrees. Do you think it is better that I change the column? I don't have expression index, only a column gist index. – user1354934 Feb 11 '22 at 23:49
  • 1
    If you can, switch to `geography`. If you can't, at least add one ore more expression indexes to cover your main queries. I added related links. The plain index you have is not going to help the query at hand. – Erwin Brandstetter Feb 11 '22 at 23:52