Tutorial - Property Search & Review

After completing the previous chapter (Building a Housing Rental), you should have set up a Tacnode instance and implemented some core features of the homestay platform. This includes publishing, modifying, and deleting listings from the landlord's perspective, as well as searching for and commenting on listings from the tenant's view. In this article, we will explore how to locate a target listing using basic information and reviews from the Airbnb test data set, and we will assess the ordered listings.

Scenarios

Once a user selects a destination, the next step is to choose accommodations. Let's explore a few scenarios demonstrating how the homestay platform allows customers to carry out various tasks, from selecting rooms to booking and reviewing orders.

Begin by identifying a property that fits your requirements using criteria such as:
- Keywords related to the listing, including the location and hotel name
- The dates you wish to book
Narrow down the property list by applying specific filters, such as:
- Ensuring the listing's rating exceeds a predetermined level
- Setting a price range for the property
- Specifying the number of beds available
After compiling a list of suitable properties, choose a few that interest you and review prior guest feedback to aid in your property selection process.
Once your stay is complete, you will review the property and share your accommodation experience.

Table Design

Listings

The properties listings table includes details about each property. Every property has a distinct ID and associated attributes like name, room type, and price.

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 )
);

Reviews

This table contains property reviews, with each review linked to a listing_id.

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

Calendar

This table holds the available dates for a property. Each entry is linked to a listing_id and a date, showing if the property can be booked on that specific date.

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

Data Import

To start, confirm that all three tables are set up, and then utilize the copy method to transfer data. For example, we will use sample data from Hong Kong (the number of rows in sample data downloaded at different times may differ slightly). After downloading, be sure to extract it to a local directory. For additional instructions on importing data, see the document Data Import and Export.

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
(3 rows)
tacnode=> \COPY calendar FROM ~/data/calendar.csv WITH CSV HEADER;
COPY 2458275
tacnode=> \COPY reviews FROM ~/data/reviews.csv WITH CSV HEADER;
COPY 95386
tacnode=> \COPY listings FROM ~/data/listings.csv WITH CSV HEADER;
COPY 6735

To enable a full-text search of the name field, we revised the table structure and added a new column with a tsvector type.

tacnode=> ALTER TABLE listings ADD COLUMN name_tsvector TSVECTOR;
ALTER TABLE
tacnode=> UPDATE listings set name_tsvector=to_tsvector(name);
UPDATE 6735

Find Target Properties

Typically, the initial stage of booking accommodation involves selecting a property based on specific keywords or your check-in time. Below are examples illustrating these two situations. It’s important to note that while you can search using both keywords and check-in times simultaneously, this combination will not be discussed here, nor will it be in the following chapters.

Find target properties using hotel keywords

Tacnode enables full-text search functionality via tsvector. For information on using tsvector, see Full Text Search.

For example, if a user intends to search for Condo

tacnode=> select name,name_tsvector from listings where name_tsvector @@ to_tsquery('Condo');
                             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
 Condo in Sheung Wan · 1 bedroom · 1 bed · 1 bath             | '1':6,9,12 'bath':13 'bed':10 'bedroom':7 'condo':1 'sheung':3 'wan':4 '·':5,8,11
 Condo in Sheung Wan · ★4.63 · 1 bedroom · 1 bed · 1 bath     | '1':8,11,14 'bath':15 'bed':12 'bedroom':9 'condo':1 'sheung':3 'wan':4 '·':5,7,10,13 '★4.63':6
(4 rows)

For example, if you wish to find a Condo in the vicinity of Sai Wan

tacnode=> select name,name_tsvector from listings where name_tsvector @@ to_tsquery('Condo&Sai&Wan');
                           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
(1 row)
 
-- You can also use plainto_tsquery to directly pass in the original text input. Note that this function can only express the AND relationship after word segmentation.
tacnode=> select name,name_tsvector from listings where name_tsvector @@ plainto_tsquery('Condo Sai Wan');
                           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
(1 row)

Find target properties by check-in date

Initially, utilizing a homestay platform involves filtering houses according to the specified check-in time. Verifying the house’s availability is essential by checking the calendar table.

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 a
WHERE a.available_count = 3;

Filter Target Properties

Once we identify a list of properties that align with your time and availability requirements, we must apply additional filtering criteria to refine our options. For instance, we might want to filter based on specific interest conditions. Here are a few examples:

Score exceeds a certain threshold
Filter by the number of bedrooms
Set a price range

Search all properties rated above 4

SELECT
        B.listing_id,
        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 A
        WHERE
                A.available_count = 3
        ) AS B
        JOIN listings AS C ON B.listing_id = C.ID
WHERE
        C.review_scores_rating >= 4.0;

Search for properties with twin beds

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 A
        WHERE
                A.available_count = 3
        ) AS B
        JOIN listings AS C ON B.listing_id = C.ID
WHERE
        C.beds = 2;

Search for properties costing between 800 and 1000

SELECT
	B.listing_id,
	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 A
	WHERE
		A.available_count = 3
	) AS B
	JOIN listings AS C ON B.listing_id = C.ID
WHERE
	C.price >= '$800'
	AND C.price <= '$1000';

View Property Reviews

Once users filter the listings by basic information, they arrive at a selection of target options. Before placing an order, they typically click on a specific listing to read previous guest reviews. They particularly focus on past negative reviews to steer clear of potential issues.

SELECT id, date, reviewer_id, reviewer_name, comments, listing_id
FROM reviews
WHERE (listing_id = 17891::text);

Submit Property Reviews

Post Reviews

Post your review of a property.

INSERT INTO reviews (listing_id, id, date, reviewer_id, reviewer_name, comments)
VALUES (17891, 37352, '2010-04-23', 76132, 'Tamara',
        $$The apartment on Holly wood Rd was exactly as described. It was a comfortable and very convenient base for our two week holiday. Our host was very forthcoming in answering queries and giving us some local tips. I would definitely recommend Candace'S place TO friends AND would have NO hesitation IN booking it again WHEN planning another trip TO HK.$$);

Edit Reviews

Revise and update existing property reviews

UPDATE reviews
SET comments=$$The apartment on Holly wood Rd was exactly as described. It was a comfortable and very convenient base for our two week holiday. Our host was very forthcoming in answering queries and giving us some local tips. I would definitely recommend Candace'S place TO friends AND would have NO hesitation IN booking it again WHEN planning another trip TO HK.$$ WHERE id=17891;

Delete Reviews

Remove the published property reviews

DELETE
FROM reviews
WHERE id = 17891;

Tutorial - Property Search & Review

On this page