Changes between Version 4 and Version 5 of AdvancedTopics

Timestamp:

06/11/26 10:37:05 (6 days ago)

Author:

231035

Comment:

--

AdvancedTopics

@@ -148 +148 @@
 }}}
 This returns the top 10 recommended properties for user 1.
+{{{
+CREATE OR REPLACE FUNCTION recommend_properties_for_user(
+    p_user_id BIGINT,
+    p_limit INT DEFAULT 50
+)
+RETURNS TABLE (
+    property_id BIGINT,
+    title varchar,
+    description text,
+    base_price NUMERIC,
+    max_guests INTEGER,
+    city varchar,
+    country_name varchar,
+    distance DOUBLE PRECISION,
+    recommendation_type text
+)
+LANGUAGE plpgsql
+AS $$
+BEGIN
+    IF EXISTS (
+        SELECT 1
+        FROM user_recommendation_profiles urp
+        WHERE urp.user_id = p_user_id
+          AND urp.preference_embedding IS NOT NULL
+    ) THEN
+
+        RETURN QUERY
+        SELECT
+            p.property_id,
+            p.title,
+            p.description,
+            p.base_price,
+            p.max_guests,
+            a.city,
+            c.country_name,
+            p.embedding <=> urp.preference_embedding AS distance,
+            'PERSONALIZED'::text AS recommendation_type
+        FROM properties p
+        JOIN addresses a
+            ON a.address_id = p.address_id
+        JOIN countries c
+            ON c.country_id = a.country_id
+        JOIN user_recommendation_profiles urp
+            ON urp.user_id = p_user_id
+        WHERE p.embedding IS NOT NULL
+          AND urp.preference_embedding IS NOT NULL
+          AND p.status = 'ACTIVE'
+          AND p.property_id NOT IN (
+              SELECT DISTINCT r2.property_id
+              FROM bookings b2
+              JOIN guests g2
+                  ON g2.guest_id = b2.guest_id
+              JOIN rooms r2
+                  ON r2.room_id = b2.room_id
+              WHERE g2.user_id = p_user_id
+          )
+        ORDER BY distance ASC
+        LIMIT p_limit;
+
+    ELSE
+
+        -- korisnikot nema prev bookings
+        RETURN QUERY
+        SELECT
+            p.property_id,
+            p.title,
+            p.description,
+            p.base_price,
+            p.max_guests,
+            a.city,
+            c.country_name,
+            NULL::DOUBLE PRECISION AS distance,
+            'FALLBACK_POPULAR'::text AS recommendation_type
+        FROM properties p
+        JOIN addresses a
+            ON a.address_id = p.address_id
+        JOIN countries c
+            ON c.country_id = a.country_id
+        LEFT JOIN reviews rv
+            ON rv.property_id = p.property_id
+        LEFT JOIN rooms r
+            ON r.property_id = p.property_id
+        LEFT JOIN bookings b
+            ON b.room_id = r.room_id
+           AND b.booking_status IN ('CONFIRMED', 'COMPLETED')
+        WHERE p.status = 'ACTIVE'
+        GROUP BY
+            p.property_id,
+            p.title,
+            p.description,
+            p.base_price,
+            p.max_guests,
+            a.city,
+            c.country_name,
+            p.created_at
+        ORDER BY
+            COUNT(b.booking_id) DESC,
+            AVG(rv.rating) DESC NULLS LAST,
+            p.created_at DESC
+        LIMIT p_limit;
+
+    END IF;
+END;
+$$;
+}}}
 === Index
 To make vector search faster, we add an HNSW index: