def index(request):
base_sql = '''
{select}
FROM
`merchandise` JOIN `store` JOIN `address` JOIN `book` JOIN `book_category_detail` JOIN `book_category`
JOIN `merchandise_image` JOIN `image` JOIN `merchandise_portfolio` JOIN `merchandise_condition`
ON `merchandise`.`id_user` = `store`.`id_user`
AND `merchandise`.`id_address` = `address`.`id`
AND `merchandise`.`id` = `book`.`id`
AND `book`.`id` = `book_category_detail`.`id_book`
AND `book_category_detail`.`id_category` = `book_category`.`id`
AND `merchandise_image`.`id_merchandise` = `merchandise`.`id`
AND `merchandise_image`.`id_image` = `image`.`id`
AND `merchandise`.`id_portfolio` = `merchandise_portfolio`.`id`
AND `merchandise`.`id_condition` = `merchandise_condition`.`id`
{where}
{group}
{order};
'''
products_select_clause = '''
SELECT
`merchandise`.*, `image`.`url`, `book`.`name`, `book`.`publisher`, `book`.`publication_date`,
`book`.`width`, `book`.`height`, `book`.`length`, `book`.`pages_num`, `address`.`city`,
(SELECT `tb_cat`.`name`
FROM `book_category` `tb_cat` JOIN `book_category_detail` `tb_det`
ON `tb_cat`.`id` = `tb_det`.`id_category`
WHERE `tb_det`.`id_book` = `book`.`id`
ORDER BY `tb_cat`.`id` DESC
LIMIT 1) AS `category`,
(`merchandise`.`total_star`/`merchandise`.`times_rated`) AS `rate_point`
'''
sqlutils = SQLUtils()
sqlutils.add_where(Merchandise.check_book_raw_query())
sqlutils.add_where(Merchandise.check_selling_raw_query())
sqlutils.add_where(Store.check_opening_raw_query())
sqlutils.add_order(SORT_SQL.get('hotest', None))
hotest_merchandises = Merchandise.objects.raw(
base_sql.format(select=products_select_clause,
where=sqlutils.get_where_clause(),
group=' GROUP BY `merchandise`.`id` ',
order=sqlutils.get_order_clause()),
sqlutils.get_params())[:10]
sqlutils = SQLUtils()
sqlutils.add_where(Merchandise.check_book_raw_query())
sqlutils.add_where(Merchandise.check_selling_raw_query())
sqlutils.add_where(Store.check_opening_raw_query())
sqlutils.add_order(SORT_SQL.get('newest', None))
newest_merchandises = Merchandise.objects.raw(
base_sql.format(select=products_select_clause,
where=sqlutils.get_where_clause(),
group=' GROUP BY `merchandise`.`id` ',
order=sqlutils.get_order_clause()),
sqlutils.get_params())[:10]
return render(
request, 'home/index.html', {
'hotest_merchandises': hotest_merchandises,
'newest_merchandises': newest_merchandises
})
def get_merchandise_sqlutils(self):
sqlutils = SQLUtils()
if self.kwargs.get('is_book'):
sqlutils.add_where(Merchandise.check_book_raw_query())
if self.kwargs.get('merchandise_status'):
sqlutils.add_where(
Merchandise.STATUS_RAW_QUERY.get(
self.kwargs.get('merchandise_status')))
if self.kwargs.get('is_opening_store'):
sqlutils.add_where(Store.check_opening_raw_query())
### Handle url queries
if self.kwargs.get('owner'):
sqlutils.add_where('`merchandise`.`id_user` = %s',
self.kwargs.get('owner'))
if self.kwargs.get('category'):
sqlutils.add_where('`book_category`.`url_name` = %s',
self.kwargs.get('category'))
if self.kwargs.get('author'):
sqlutils.add_where('`book`.`author` = %s',
self.kwargs.get('author'))
if self.kwargs.get('location'):
sqlutils.add_where('`address`.`city` = %s',
self.kwargs.get('location'))
if self.kwargs.get('condition'):
sqlutils.add_where('`merchandise_condition`.`code` = %s',
self.kwargs.get('condition'))
if self.kwargs.get('low_price'):
sqlutils.add_where('`merchandise`.`price` >= %s',
self.kwargs.get('low_price'))
if self.kwargs.get('high_price'):
sqlutils.add_where('`merchandise`.`price` <= %s',
self.kwargs.get('high_price'))
if self.kwargs.get('search_product'):
sqlutils.add_where('`book`.`name` LIKE %s',
'%' + self.kwargs.get('search_product') + '%')
sqlutils.add_order(
SORT_SQL.get(self.kwargs.get('sort'), SORT_SQL.get('newest')))
return sqlutils
def get_products(request):
base_sql = '''
{select}
FROM
`merchandise` JOIN `store` JOIN `address` JOIN `book` JOIN `book_category_detail` JOIN `book_category`
JOIN `merchandise_image` JOIN `image` JOIN `merchandise_portfolio` JOIN `merchandise_condition`
ON `merchandise`.`id_user` = `store`.`id_user`
AND `merchandise`.`id_address` = `address`.`id`
AND `merchandise`.`id` = `book`.`id`
AND `book`.`id` = `book_category_detail`.`id_book`
AND `book_category_detail`.`id_category` = `book_category`.`id`
AND `merchandise_image`.`id_merchandise` = `merchandise`.`id`
AND `merchandise_image`.`id_image` = `image`.`id`
AND `merchandise`.`id_portfolio` = `merchandise_portfolio`.`id`
AND `merchandise`.`id_condition` = `merchandise_condition`.`id`
{where}
{group}
{order};
'''
sqlutils = SQLUtils()
sqlutils.add_where(Merchandise.check_selling_raw_query())
sqlutils.add_where(Store.check_opening_raw_query())
sqlutils.add_order(
SORT_SQL.get(request.GET.get('sort'), SORT_SQL.get('newest')))
### Handle url queries
if request.GET.get('location'):
sqlutils.add_where('`address`.`city` = %s',
request.GET.get('location'))
if request.GET.get('condition'):
sqlutils.add_where('`merchandise_condition`.`code` = %s',
request.GET.get('condition'))
if request.GET.get('low_price'):
sqlutils.add_where('`merchandise`.`price` >= %s',
request.GET.get('low_price'))
if request.GET.get('high_price'):
sqlutils.add_where('`merchandise`.`price` <= %s',
request.GET.get('high_price'))
if request.GET.get('q'):
sqlutils.add_where(
'( `book`.`name` LIKE %s OR `book`.`author` LIKE %s )', [
'%' + request.GET.get('q') + '%',
'%' + request.GET.get('q') + '%'
])
## Get cities
cities_select_clause = 'SELECT `address`.`city` as `id`, COUNT(DISTINCT `merchandise`.`id`) AS `num_products`'
cities = Merchandise.objects.raw(
base_sql.format(select=cities_select_clause,
where=sqlutils.get_where_clause(),
group=' GROUP BY `address`.`city` ',
order=' ORDER BY `num_products` DESC '),
sqlutils.get_params())
## Get conditions
conditions_select_clause = 'SELECT `merchandise_condition`.*, COUNT(DISTINCT `merchandise`.`id`) AS `num_products`'
conditions = Merchandise.objects.raw(
base_sql.format(select=conditions_select_clause,
where=sqlutils.get_where_clause(),
group=' GROUP BY `merchandise_condition`.`id` ',
order=' ORDER BY `num_products` DESC '),
sqlutils.get_params())
## Get books and paging
products_select_clause = '''
SELECT
`merchandise`.*, `image`.`url`, `book`.`name`, `book`.`publisher`, `book`.`publication_date`,
`book`.`width`, `book`.`height`, `book`.`length`, `book`.`pages_num`, `address`.`city`,
(SELECT `tb_cat`.`name`
FROM `book_category` `tb_cat` JOIN `book_category_detail` `tb_det`
ON `tb_cat`.`id` = `tb_det`.`id_category`
WHERE `tb_det`.`id_book` = `book`.`id`
ORDER BY `tb_cat`.`id` DESC
LIMIT 1) AS `category`,
(`merchandise`.`total_star`/`merchandise`.`times_rated`) AS `rate_point`
'''
merchandises = Merchandise.objects.raw(
base_sql.format(select=products_select_clause,
where=sqlutils.get_where_clause(),
group=' GROUP BY `merchandise`.`id` ',
order=sqlutils.get_order_clause()),
sqlutils.get_params())
print(merchandises)
paginator = Paginator(merchandises, 9)
page_number = request.GET.get('page')
pager = paginator.get_page(page_number)
page_navigator = []
for i in range(max(1, pager.number - 2), pager.number):
page_navigator.append(i)
page_navigator.append(pager.number)
for i in range(pager.number + 1,
min(pager.number + 2, pager.paginator.num_pages) + 1):
page_navigator.append(i)
return render(
request, 'search/products.html', {
'pager': pager,
'page_navigator': page_navigator,
'cities': cities,
'conditions': conditions
})
