Changes between Version 3 and Version 4 of AdvancedTopics

Timestamp:

06/11/26 10:34:44 (10 days ago)

Author:

231035

Comment:

--

AdvancedTopics

@@ -20 +20 @@
 }}}
 
-This column stores the semantic meaning of each property.We created a database view:{{{vw_property_embedding_text}}}
-This view prepares one text description for every property.This text is then converted into an embedding.
+This column stores the semantic meaning of each property. Additionaly we created a new table for this phase.
+{{{
+CREATE TABLE user_recommendation_profiles (
+    user_id BIGINT PRIMARY KEY
+        REFERENCES users(user_id)
+        ON DELETE CASCADE
+        ON UPDATE CASCADE,
+
+    preference_embedding vector(384),
+    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
+);
+}}}
+We created a database view:{{{vw_property_embedding_text}}}
+This view prepares one text description for every property. This text is then converted into an embedding.
+The view:
+{{{
+
+CREATE OR REPLACE VIEW vw_property_embedding_text AS
+SELECT
+    p.property_id,
+
+    CONCAT(
+        'Title: ', p.title, '. ',
+        'Description: ', COALESCE(p.description, ''), '. ',
+        'Listing type: ', lt.type_name, '. ',
+        'City: ', a.city, '. ',
+        'Country: ', c.country_name, '. ',
+        'Base price: ', p.base_price, '. ',
+        'Max guests: ', p.max_guests, '. ',
+        'Property amenities: ', COALESCE(string_agg(DISTINCT pa_am.amenity_name, ', '), ''), '. ',
+        'Room types: ', COALESCE(string_agg(DISTINCT rt.type_name, ', '), ''), '. ',
+        'Room amenities: ', COALESCE(string_agg(DISTINCT ra_am.amenity_name, ', '), ''), '. ',
+        'Average rating: ', COALESCE(ROUND(AVG(rv.rating)::numeric, 2), 0), '.'
+    ) AS embedding_text
+
+FROM properties p
+JOIN listing_types lt
+    ON lt.listing_type_id = p.listing_type_id
+JOIN addresses a
+    ON a.address_id = p.address_id
+JOIN countries c
+    ON c.country_id = a.country_id
+
+LEFT JOIN property_amenities pa
+    ON pa.property_id = p.property_id
+LEFT JOIN amenities pa_am
+    ON pa_am.amenity_id = pa.amenity_id
+LEFT JOIN rooms r
+    ON r.property_id = p.property_id
+LEFT JOIN room_types rt
+    ON rt.room_type_id = r.room_type_id
+LEFT JOIN room_amenities ra
+    ON ra.room_id = r.room_id
+LEFT JOIN amenities ra_am
+    ON ra_am.amenity_id = ra.amenity_id
+
+LEFT JOIN reviews rv
+    ON rv.property_id = p.property_id
+
+GROUP BY
+    p.property_id,
+    p.title,
+    p.description,
+    p.base_price,
+    p.max_guests,
+    lt.type_name,
+    a.city,
+    c.country_name;
+
+}}}
 [[br]]
 [[br]]