def get_recommendations(product_id):
"""
Get the recommended items from the database given a product ID
"""
product_id = product_id.replace("'", "''")
results = database.execute_query(
"select recommendations from simplerecs where product_id = %s",
(product_id, ))
recs = [
convert_to_model.toProduct(
database.execute_query(
"select * from products where product_id = %s", (e, ))[0])
for e in results[0][0]
]
return recs
def prioritize_discount(recs, limit):
"""Give priority to recommendations with (higher) discounts"""
if len(recs) > 4:
# get all product_prices data from the DB
price_data = database.execute_query(f"select * from product_prices",
"")
# select all the product prices from the cart where the discount is not none(we exclude None so we can use sorted() later)
product_prices = [
product_price for product_price in price_data
if product_price[0] in recs if product_price[1] is not None
]
# randomize the placement of these prices
product_prices = (random.sample(product_prices, len(product_prices)))
# sort the prices on discount
sorted_product_prices = list(
reversed(sorted(product_prices, key=lambda x: x[1])))
# add prices where discount is None
sorted_product_prices = sorted_product_prices + [
product_price for product_price in price_data
if product_price[0] in recs if product_price[1] is None
]
# select product_ids
recs = [x[0] for x in sorted_product_prices]
# return limit product_ids
return recs[:limit]
else:
return recs
def fill_table_property_matching(cursor, connection):
"""create and fill a table based on the property_matching() function"""
cursor.execute("select count(*) from property_matching_recs")
hasEntries = True if cursor.fetchone()[0] > 0 else False
if not hasEntries:
cursor.execute("select product_id from products")
data = cursor.fetchall()
price_data = database.execute_query(
f"""select pc.product_id , doelgroep , eenheid, factor, gebruik, geschiktvoor, geursoort, huidconditie,huidtype , huidtypegezicht , inhoud , klacht , kleur , leeftijd , serie, soort,soorthaarverzorging , soortmondverzorging , sterkte , type, typehaarkleuring , typetandenborstel , variant,waterproof , pc.category, sub_category, sub_sub_category, brand, gender
from product_properties pp
inner join product_categories pc on pc.product_id = pp.product_id
inner join products on products.product_id = pp.product_id """,
"")
# replacing ' with '' so LIKE in the sql statement doesn't crash
id_list = [id[0].replace("'", "''") for id in data]
for count, id in enumerate(id_list):
data = (property_matching(id, 4, price_data))
recs = data[0]
weight = data[1]
cursor.execute(
f"insert into property_matching_recs (product_id,recommendations,weighted_match_rate) values(%s,%s,%s)",
(id, recs, weight))
if count % 500 == 0 or count == len(id_list):
connection.commit()
print(
f"Recommendations (Property matching): {count}/{len(id_list)}"
)
def property_matching(product_id, limit, price_data):
"""Look at all relevant product properties to decide the best matching recommendation, returns a list with a list of recs and an average match rate"""
# column numbers have been divided in different weight classes, product properties have different weight in deciding what to recommend
w2 = [3, 4, 7, 8, 9, 12, 13, 15, 16, 17, 18, 19, 20, 22, 24, 25, 27]
w5 = [5, 11, 21, 23, 26, 28]
try:
product_id_properties = database.execute_query(
f"""select pc.product_id , doelgroep , eenheid, factor, gebruik, geschiktvoor, geursoort, huidconditie,huidtype , huidtypegezicht , inhoud , klacht , kleur , leeftijd , serie, soort,soorthaarverzorging , soortmondverzorging , sterkte , type, typehaarkleuring , typetandenborstel , variant,waterproof , pc.category, sub_category, sub_sub_category, brand, gender
from product_properties pp
inner join product_categories pc on pc.product_id = pp.product_id
inner join products on products.product_id = pp.product_id
where pc.product_id like '{product_id}'""",
"")[0]
except IndexError:
return [None, 0]
if (product_id_properties.count(None) == 27):
return [None, 0]
match_list = []
for data in price_data:
match_count = 0
viable_match_len = 0
for i in range(len(data)):
# make sure we don't count None <-> None as a property match
if not (data[i] is None or product_id_properties[i] is None):
if data[i] != "}":
viable_match_len += 1
if data[i] == product_id_properties[i]:
if i in w2:
match_count += 2
elif i in w5:
match_count += 5
else:
match_count += 1
match_list.append([data[0], match_count])
# sort on match_count
match_list = (list(reversed(sorted(match_list, key=lambda x: x[1]))))
# change match_counts to a %-weighted-match scale
scale_factor = float(100 / match_list[0][1])
for index, x in enumerate(match_list):
match_list[index][1] = round(x[1] * scale_factor, 2)
# get the product_ids, exclude the best match(itself)
recommendations = [match_list[1:][x][0] for x in range(limit)]
avg_match = (round(
sum([x[1] for x in match_list[1:limit + 1]]) / limit, 2))
return [recommendations, avg_match]
def recommend(cart, limit=4):
"""
Generate recommendations for items in a shopping cart, based on the similarities between the cart and other orders
"""
# Get all of the products in all of the orders
res = database.execute_query(
"select products from orders where cardinality(products) > 1", ())
products = [set(e[0]) for e in res]
# Sort the list of orders by how similar they are to the cart, and by how much the cart makes up of that order,
# while simultaneously changing all orders to contain only products not found in the shopping cart
cart = set(cart)
products = sorted([
e - cart for e in sorted(products, key=lambda e: len(cart & e))[::-1]
if len(e - cart) > 0 and len(e & cart) >= 1
],
key=len)
result = []
# Get all legitimate IDs, to check whether the IDs in the orders are legit
ids = [
e[0]
for e in database.execute_query("select product_id from products", "")
]
# Fill the result with items from the changed products list, until the result contains a maximum number of items
# equal to the given limit
while len(result) < limit:
if len(products) == 0:
break
order = products.pop(0)
for p in order:
if p not in result and p in ids:
result.append(p)
if len(result) >= limit:
break
return result
def changeprofileid(self):
""" This function checks whether the provided session ID actually exists
and stores it in the session if it does. """
try:
newprofileid = request.form.get('profile_id')
available_profiles = database.execute_query("select visitor_id from visitor_recs where visitor_id = %s",
(newprofileid,))
profidexists = available_profiles[0][0] == newprofileid
if profidexists:
session['profile_id'] = newprofileid
return '{"success":true}'
return '{"success":false}'
except:
return '{"success":false}'
def productpage(self, cat1=None, cat2=None, cat3=None, cat4=None, page=1):
""" This function renders the product page template with the products it
can retrieve from the database, based on the URL path provided (which
corresponds to product categories). """
limit = session['items_per_page'] if session['items_per_page'] != 0 else \
database.execute_query("select count(product_id) from products", '')[0][0]
rec_limit = 4
catlist = [cat1, cat2, cat3, cat4]
nononescats = [e for e in catlist if e is not None]
skipindex = session['items_per_page'] * (page - 1)
""" Get all products (this need to be based on profile if has profile id) """
profile_id = session['profile_id'] if session['profile_id'] is not None else '5a393d68ed295900010384ca'
retrieved_ids = page_home.get_recommendations(profile_id, nononescats, limit)
id_batch = retrieved_ids[skipindex:(skipindex + limit)]
""" Convert the recommended ids to product objects """
prodList = [convert_to_model.toProduct(e) for e in id_batch]
""" Get 'anderen kochten ook' recommendations """
recs = page_home.get_anderen_kochten_ook(id_batch, rec_limit, profile_id)
recs = [convert_to_model.toProduct(e) if type(e) != product.Product else e for e in recs]
""" Set the url path to match the categries and page we are in """
if len(nononescats) > 0:
pagepath = "/producten/" + ("/".join(nononescats)) + "/"
else:
pagepath = "/producten/"
return self.renderpackettemplate('products.html', {'products': prodList,
'productcount': len(retrieved_ids),
'pstart': skipindex + 1,
'pend': skipindex + session['items_per_page'] if session[
'items_per_page'] > 0 else len(
retrieved_ids),
'prevpage': pagepath + str(page - 1) if (
page > 1) else False,
'nextpage': pagepath + str(page + 1) if (session[
'items_per_page'] * page < len(
retrieved_ids)) else False,
'r_products': recs[:rec_limit],
'r_type': list(self.recommendationtypes.keys())[0],
'r_string': list(self.recommendationtypes.values())[0],
'header_text': 'Voor u aanbevolen' if len(
nononescats) == 0 else ''
})
def product_detail_alg_selection(product):
"code that decides what algorithm to use in the product_details based on the accuracy of the recommendations"
recs_data = database.execute_query(
f"select recommendations, weighted_match_rate from property_matching_recs where product_id = '{product.product_id}'",
"")
threshold = 50
# If 50% (threshold value in percentages) of the properties of a product match with others
# we can you those products. This indicated how much a product has in common with others.
if recs_data[0][1] > threshold:
print('Algorithm: property_matching')
recs = (recs_data[0][0])
r_products = convert_to_model.convert_to_product_list(
"select * from products where product_id in %s", (tuple(recs), ))
# if this product does have much in common we call the simple algorithm to extend te list to its limit (usually 4)
else:
print('Algorithm: simple')
r_products = simple.get_recommendations(product.product_id)
return r_products
def cart_alg_selection(limit, shopping_cart, profile_id):
"code that decides what algorithm to use in the shopping cart based on the accuracy of the recommendations, returns *limit* recommendations"
# get all the ids from the cart
ids_in_cart = [x[0] for x in shopping_cart]
# if there is only 1 element in the list Psycopg2 will crash. Hence the statement add a blank string.
# (does not affect the results)
if len(ids_in_cart) == 1:
ids_in_cart.append('')
if len(ids_in_cart) > 0:
recs_data = database.execute_query(
f"select * from order_based_recs where product_id in {tuple(ids_in_cart)}",
"")
recs_data = list(reversed(sorted(recs_data,
key=lambda x: x[2])))[:limit]
recs_data_simple = database.execute_query(
f"select * from simplerecs where product_id in {tuple(ids_in_cart)}",
"")
recs_data_behaviour = behaviour.recommend(ids_in_cart, limit)
# print(f'{ids_in_cart}, {recs_data_behaviour}')
sample_size_limit = 10
if recs_data[0][2] >= sample_size_limit:
print('Algorithm: Bought_together')
recs = list(
set([
rec for data in recs_data if (data[2] > sample_size_limit)
for rec in data[1]
]))
# recs = prioritze_discount.prioritize_discount(recs, limit)
random.shuffle(recs)
recs = recs[:4]
else:
if len(recs_data_behaviour) == limit:
print('Algorithm: Behaviour')
recs = recs_data_behaviour
else:
print('Algorithm: Simple')
recs = list(
set([
z for x in recs_data_simple
for z in random.sample(x[1], k=len(x[1]))
]))[:limit]
print(recs)
if len(recs) < 4:
recs = list(
set([
z for x in recs_data_simple
for z in random.sample(x[1], k=len(x[1]))
]))[:limit]
print(recs)
r_prods = convert_to_model.convert_to_product_list(
"select * from products where product_id in %s", (tuple(recs), ))
else:
try:
if profile_id is None:
raise NameError
r_prods = page_home.get_profile_recommendations(profile_id,
limit=limit)
except NameError:
r_prods = [
convert_to_model.toProduct(e)
for e in database.get_based_on_categories([], limit)
]
return r_prods
def convert_to_product_list(query, data):
r_prods = [toProduct(e) for e in (database.execute_query(query, data))]
return r_prods
def get_recs(visitor_id, limit, only_ids=False):
""" get recommendations based on visitor id """
# get related products to a profile """
profile_columns = list(
database.execute_query(
"select previously_recommended, viewed_before, similars from visitor_recs where visitor_id = %s",
(visitor_id, ))[0])
try:
# flatten the lists (profile_columns can conatins list in lists etc.)
all_product_ids = tuple(itertools.chain.from_iterable(profile_columns))
except TypeError:
return []
# create dict with empty dicts for every category type and keep count
category_counter = {'main': {}, 'sub': {}, 'sub_sub': {}}
all_product_ids = list(all_product_ids)
if (len(all_product_ids) == 1):
all_product_ids.append('')
if (len(all_product_ids) > 0):
cat_results = database.execute_query(
f"select category, sub_category, sub_sub_category from product_categories where product_id in {tuple(all_product_ids)}",
"")
else:
cat_results = []
layers = ['sub_sub', 'sub', 'main']
products = []
r_layers = layers.copy()
r_layers.reverse()
for row in cat_results:
for i, layer in enumerate(r_layers):
if row[i] in category_counter[layer]:
category_counter[layer][row[i]] += 1
else:
category_counter[layer][row[i]] = 1
# loop trough category types starting from deepest (starting wiht sub_sub)
for layer in layers:
try:
# Calculate recommendations from the category type and add them to a list
category_counter = {
k: v
for k, v in reversed(
sorted(category_counter[layer].items(),
key=lambda item: item[1]))
}
most_popular_category = list(category_counter.keys())[0]
popular_category_product_ids = database.execute_query(
"select product_id from product_categories where sub_sub_category = %s",
(most_popular_category, ))
popular_category_product_ids = prioritze_discount.prioritize_discount(
list(
itertools.chain.from_iterable(
popular_category_product_ids)), limit)
select_criteria = '*' if only_ids is False else 'product_id'
products_results = database.execute_query(
f"select {select_criteria} from products where product_id in %s",
(tuple(popular_category_product_ids), ))
return products_results
except psycopg2.errors.SyntaxError:
break
return products