Tutorial - Property Search & Review System

Introduction

Building on the foundation established in our Housing Rental Platform tutorial, we now focus on creating sophisticated search and review functionality. This tutorial demonstrates how to implement the features that transform a basic property database into a comprehensive rental marketplace where users can discover, evaluate, and book accommodations.

We'll work with real Airbnb data from Hong Kong to create practical, production-ready features that handle the complexity of modern rental platforms.

User Journey & Core Scenarios

Let's explore the typical user journey on a rental platform, from initial search to post-stay review:

1. Property Discovery

Users start by searching for properties using various criteria:

Keyword Search: Location names, property types, or specific features
Date-based Availability: Checking calendar availability for specific dates
Location Proximity: Finding properties near points of interest

Once users have initial results, they refine their search with filters:

Quality Filters: Minimum rating requirements
Capacity Needs: Number of beds, bathrooms, or guest capacity
Budget Constraints: Price range filtering
Amenity Requirements: Specific features like WiFi, parking, or pools

3. Property Evaluation

Before booking, users research properties by:

Reading Guest Reviews: Learning from previous experiences
Analyzing Ratings: Understanding overall satisfaction levels
Checking Recent Feedback: Ensuring current quality standards

4. Post-Stay Engagement

After their stay, users contribute to the platform by:

Writing Reviews: Sharing their experience with future guests
Rating Properties: Providing numerical feedback on various aspects
Updating Reviews: Modifying feedback if circumstances change

Database Schema Design

Our platform requires three core tables to support the complete user journey:

Property Listings Table

The listings table serves as our central repository for property information:

CREATE TABLE listings (
    ID TEXT PRIMARY KEY,
    listing_url TEXT,
    scrape_id TEXT,
    last_scraped DATE,
    SOURCE TEXT,
    NAME TEXT,
    description TEXT,
    neighborhood_overview TEXT,
    picture_url TEXT,
    host_id BIGINT,
    host_url TEXT,
    host_name TEXT,
    host_since DATE,
    host_location TEXT,
    host_about TEXT,
    host_response_time TEXT,
    host_response_rate TEXT,
    host_acceptance_rate TEXT,
    host_is_superhost BOOLEAN,
    host_thumbnail_url TEXT,
    host_picture_url TEXT,
    host_neighbourhood TEXT,
    host_listings_count INTEGER,
    host_total_listings_count INTEGER,
    host_verifications TEXT,
    host_has_profile_pic BOOLEAN,
    host_identity_verified BOOLEAN,
    neighbourhood TEXT,
    neighbourhood_cleansed TEXT,
    neighbourhood_group_cleansed TEXT,
    latitude NUMERIC(9, 6),
    longitude NUMERIC(9, 6),
    property_type TEXT,
    room_type TEXT,
    accommodates SMALLINT,
    bathrooms TEXT,
    bathrooms_text TEXT,
    bedrooms TEXT,
    beds SMALLINT,
    amenities JSONB,
    price MONEY,
    minimum_nights INTEGER,
    maximum_nights INTEGER,
    minimum_minimum_nights INTEGER,
    maximum_minimum_nights INTEGER,
    minimum_maximum_nights INTEGER,
    maximum_maximum_nights INTEGER,
    minimum_nights_avg_ntm NUMERIC(10, 2),
    maximum_nights_avg_ntm NUMERIC(10, 2),
    calendar_updated TEXT,
    has_availability BOOLEAN,
    availability_30 SMALLINT,
    availability_60 SMALLINT,
    availability_90 SMALLINT,
    availability_365 SMALLINT,
    calendar_last_scraped DATE,
    number_of_reviews INTEGER,
    number_of_reviews_ltm INTEGER,
    number_of_reviews_l30d INTEGER,
    first_review DATE,
    last_review DATE,
    review_scores_rating NUMERIC(10, 2),
    review_scores_accuracy NUMERIC(10, 2),
    review_scores_cleanliness NUMERIC(10, 2),
    review_scores_checkin NUMERIC(10, 2),
    review_scores_communication NUMERIC(10, 2),
    review_scores_location NUMERIC(10, 2),
    review_scores_value NUMERIC(10, 2),
    license TEXT,
    instant_bookable BOOLEAN,
    calculated_host_listings_count TEXT,
    calculated_host_listings_count_entire_homes SMALLINT,
    calculated_host_listings_count_private_rooms SMALLINT,
    calculated_host_listings_count_shared_rooms SMALLINT,
    reviews_per_month NUMERIC(10, 2)
);

Guest Reviews Table

The reviews table captures user feedback and experiences:

CREATE TABLE reviews (
    listing_id TEXT,
    id BIGINT PRIMARY KEY,
    date DATE,
    reviewer_id BIGINT,
    reviewer_name TEXT,
    comments TEXT
);

Availability Calendar Table

The calendar table tracks property availability and pricing by date:

CREATE TABLE calendar (
    listing_id TEXT,
    date DATE,
    available BOOLEAN,
    price MONEY,
    adjusted_price MONEY,
    minimum_nights SMALLINT,
    maximum_nights SMALLINT
);

Data Import & Setup

Let's set up our database with sample data. First, verify your tables are created:

tacnode=> \d
                       List of relations
 Schema |   Name   |  Type  |         Owner
----------+----------+--------+------------------------
 public   | calendar | table | test@tacnode.io
 public   | listings | table | test@tacnode.io
 public   | reviews  | table | test@tacnode.io

Now import the sample data using the COPY command:

-- Import calendar data (availability and pricing)
tacnode=> \COPY calendar FROM ~/data/calendar.csv WITH CSV HEADER;
COPY 2458275
 
-- Import user reviews
tacnode=> \COPY reviews FROM ~/data/reviews.csv WITH CSV HEADER;
COPY 95386
 
-- Import property listings
tacnode=> \COPY listings FROM ~/data/listings.csv WITH CSV HEADER;
COPY 6735

Enable Full-Text Search

To support keyword-based property searches, we'll add a full-text search column:

-- Add tsvector column for full-text search
tacnode=> ALTER TABLE listings ADD COLUMN name_tsvector TSVECTOR;
ALTER TABLE
 
-- Populate the search vector with property names
tacnode=> UPDATE listings SET name_tsvector = to_tsvector(name);
UPDATE 6735

Feature Implementation

Now let's implement the core search and review features:

Feature 1: Smart Property Discovery

Keyword-Based Search

Tacnode's full-text search capabilities enable users to find properties using natural language queries:

Example: Finding Condos

SELECT name, name_tsvector 
FROM listings 
WHERE name_tsvector @@ to_tsquery('Condo');

Result:

                             name                             |                                             name_tsvector
--------------------------------------------------------------+-------------------------------------------------------------------------------------------------------
 Condo in Sai Wan · ★4.13 · 2 bedrooms · 2 beds · 2 baths     | '2':8,11,14 'bath':15 'bed':12 'bedroom':9 'condo':1 'sai':3 'wan':4 '·':5,7,10,13 '★4.13':6
 Condo in Kennedy Town · ★4.73 · 2 bedrooms · 2 beds · 1 bath | '1':14 '2':8,11 'bath':15 'bed':12 'bedroom':9 'condo':1 'kennedi':3 'town':4 '·':5,7,10,13 '★4.73':6

Example: Location-Specific Search

-- Find condos specifically in Sai Wan area
SELECT name, name_tsvector 
FROM listings 
WHERE name_tsvector @@ to_tsquery('Condo&Sai&Wan');

Result:

                           name                           |                                        name_tsvector
----------------------------------------------------------+----------------------------------------------------------------------------------------------
 Condo in Sai Wan · ★4.13 · 2 bedrooms · 2 beds · 2 baths | '2':8,11,14 'bath':15 'bed':12 'bedroom':9 'condo':1 'sai':3 'wan':4 '·':5,7,10,13 '★4.13':6

For simpler queries, you can use plainto_tsquery() which automatically handles AND relationships:

SELECT name, name_tsvector 
FROM listings 
WHERE name_tsvector @@ plainto_tsquery('Condo Sai Wan');

Date-Based Availability Search

Users need to find properties available for their specific travel dates:

-- Find properties available for a 3-day stay (Oct 11-13, 2023)
SELECT listing_id
FROM (
    SELECT listing_id, COUNT(1) AS available_count
    FROM calendar
    WHERE date >= '2023-10-11'
      AND date <= '2023-10-13'
      AND available = 't'
    GROUP BY listing_id
) AS availability_check
WHERE availability_check.available_count = 3;

This query ensures properties are available for the entire requested period, not just individual days.

Feature 2: Advanced Filtering System

Once users have initial search results, they need sophisticated filtering options:

Quality-Based Filtering

Find High-Rated Properties (4+ stars)

SELECT
    B.listing_id,
    C.review_scores_rating
FROM (
    -- Get available properties for date range
    SELECT listing_id
    FROM (
        SELECT listing_id, COUNT(1) AS available_count 
        FROM calendar 
        WHERE date >= '2023-10-11' 
          AND date <= '2023-10-13' 
          AND available = 't' 
        GROUP BY listing_id
    ) AS availability_filter
    WHERE availability_filter.available_count = 3
) AS B
JOIN listings AS C ON B.listing_id = C.ID
WHERE C.review_scores_rating >= 4.0;

Accommodation Needs Filtering

Find Properties with Specific Bed Count

SELECT
    B.listing_id,
    C.name,
    C.review_scores_rating
FROM (
    SELECT listing_id
    FROM (
        SELECT listing_id, COUNT(1) AS available_count 
        FROM calendar 
        WHERE date >= '2023-10-11' 
          AND date <= '2023-10-13' 
          AND available = 't' 
        GROUP BY listing_id
    ) AS availability_filter
    WHERE availability_filter.available_count = 3
) AS B
JOIN listings AS C ON B.listing_id = C.ID
WHERE C.beds = 2;

Budget-Based Filtering

Find Properties Within Price Range ($800-$1000)

SELECT
    B.listing_id,
    C.review_scores_rating,
    C.price
FROM (
    SELECT listing_id
    FROM (
        SELECT listing_id, COUNT(1) AS available_count 
        FROM calendar 
        WHERE date >= '2023-10-11' 
          AND date <= '2023-10-13' 
          AND available = 't' 
        GROUP BY listing_id
    ) AS availability_filter
    WHERE availability_filter.available_count = 3
) AS B
JOIN listings AS C ON B.listing_id = C.ID
WHERE C.price >= '$800'
  AND C.price <= '$1000';

Feature 3: Review & Rating System

Viewing Property Reviews

Before booking, users want to read previous guest experiences:

SELECT id, date, reviewer_id, reviewer_name, comments, listing_id
FROM reviews
WHERE listing_id = '17891'
ORDER BY date DESC;

Managing Guest Reviews

Submitting New Reviews

After a stay, guests can share their experience:

INSERT INTO reviews (listing_id, id, date, reviewer_id, reviewer_name, comments)
VALUES (
    '17891', 
    37352, 
    '2010-04-23', 
    76132, 
    'Tamara',
    'The apartment on Hollywood Rd was exactly as described. It was comfortable and very convenient for our two week holiday. Our host was forthcoming in answering queries and giving local tips. I would definitely recommend Candace''s place to friends and would have no hesitation booking it again when planning another trip to HK.'
);

Updating Existing Reviews

Users can modify their reviews to add information or correct details:

UPDATE reviews
SET comments = 'The apartment on Hollywood Rd exceeded our expectations. It was comfortable and perfectly located for our two week holiday. Our host was incredibly helpful in answering queries and providing excellent local recommendations. I would definitely recommend Candace''s place to friends and would eagerly book it again for future trips to HK.'
WHERE id = 37352;

Removing Reviews

If needed, users can remove their reviews:

DELETE FROM reviews
WHERE id = 37352;

Advanced Query Patterns

Finding Properties with Poor Reviews

Sometimes users want to see negative feedback to make informed decisions:

-- Find reviews mentioning specific concerns
SELECT r.listing_id, r.comments, l.name
FROM reviews r
JOIN listings l ON r.listing_id = l.id
WHERE r.comments ILIKE '%noisy%' 
   OR r.comments ILIKE '%dirty%' 
   OR r.comments ILIKE '%problem%'
ORDER BY r.date DESC
LIMIT 10;

Comprehensive Multi-Criteria Search

Combining all filters for sophisticated property discovery:

SELECT 
    l.id,
    l.name,
    l.price,
    l.review_scores_rating,
    l.beds,
    l.neighbourhood
FROM listings l
JOIN calendar c ON l.id = c.listing_id
WHERE 
    -- Availability filter
    c.available = TRUE
    AND c.date BETWEEN '2023-10-11' AND '2023-10-13'
    
    -- Quality filter
    AND l.review_scores_rating >= 4.0
    
    -- Price filter
    AND l.price BETWEEN '$500' AND '$1500'
    
    -- Accommodation filter
    AND l.beds >= 2
    
    -- Amenity filter
    AND l.amenities @> '["Wifi", "Air conditioning"]'
    
GROUP BY l.id, l.name, l.price, l.review_scores_rating, l.beds, l.neighbourhood
HAVING COUNT(c.date) = 3  -- Ensure all 3 nights are available
ORDER BY l.review_scores_rating DESC, l.price ASC;

Performance Optimization Tips

1. Index Full-Text Search

CREATE INDEX idx_listings_name_tsvector ON listings USING GIN(name_tsvector);

2. Index Common Filter Columns

CREATE INDEX idx_listings_price ON listings(price);
CREATE INDEX idx_listings_rating ON listings(review_scores_rating);
CREATE INDEX idx_calendar_availability ON calendar(listing_id, date, available);

3. Index JSONB Amenities

CREATE INDEX idx_listings_amenities ON listings USING GIN(amenities);

What's Next?

In this tutorial, we built a comprehensive search and review system that enables:

Smart Property Discovery through keyword and date-based searches
Advanced Filtering by quality, capacity, price, and amenities
User-Generated Content through reviews and ratings
Dynamic Query Capabilities for complex multi-criteria searches

Our next tutorial, Information Enrichment & Omni Search, will enhance our platform further by:

Leveraging JSONB for flexible property attributes
Implementing dynamic amenity filtering
Creating advanced analytics capabilities
Building omni-directional search features

These enhancements will transform our platform into a truly dynamic, data-driven rental marketplace.