Advertisement
Guest User

Habr's 477624 post paste.

a guest
Nov 27th, 2019
359
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. SELECT CAST(u.id AS TEXT)
  2.   FROM "user" u CROSS JOIN filter f
  3.        JOIN "user" our ON our.id = :user_id
  4.  WHERE f.user_id = :user_id
  5. -- Check other user's legitimacy
  6.    AND u.is_fully_registered
  7.    AND NOT u.is_permabanned
  8.    AND NOT EXISTS (SELECT TRUE FROM match_exclusion me WHERE me.user_id=:user_id AND me.excluded_id = u.id)
  9. -- Check simple things
  10.    AND ((u.birthday BETWEEN f.age_high AND f.age_low) OR f.age_high ISNULL OR f.age_low ISNULL)
  11.    AND ((u.weight BETWEEN f.weight_low AND f.weight_high) OR f.weight_low ISNULL OR f.weight_high ISNULL)
  12.    AND ((u.height BETWEEN f.height_low AND f.height_high) OR f.height_low ISNULL OR f.height_high ISNULL)
  13.    AND (f.search_male AND u.is_male OR f.search_female AND NOT u.is_male)
  14. -- Check one-to-many array matching
  15.    AND (f.educations = '{}'   OR u.education = ANY (f.educations))
  16.    AND (f.universities = '{}' OR u.university = ANY (f.universities))
  17.    AND (f.religions = '{}'    OR u.religion = ANY (f.religions))
  18. -- Check many-to-many array matching.
  19.    AND (f.interests = '{}' OR (u.interests @> f.interests))
  20.    AND (f.languages = '{}' OR (u.languages @> f.languages))
  21.  
  22.  ORDER BY st_distance(st_setsrid(st_point(u.longitude,   u.latitude),   4326),
  23.                       st_setsrid(st_point(our.longitude, our.latitude), 4326)) NULLS LAST;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement