Untitled

mail@pastecode.io avatar
unknown
plain_text
a year ago
3.9 kB
4
Indexable
Never
@all_sidebar_computers_blueprint.route('/sidebar/getall', methods=['GET'])
def get_sidebar_options():
    # Get selected values from query parameters
    selected_values = request.args.get('selectedValues', '{}')
    selected_values = json.loads(selected_values)
    selected_column_mappings = {
        'brands': 'brand_name',
        'processors': 'cpu',
        'rams': 'ram',
        'screenSizes': 'screen',
        'priceInterval': 'price'
        # Add other mappings as needed
    }

    base_query = """
        SELECT *
        FROM from_different_sources fds
        WHERE (fds.product_name, fds.brand_name, fds.saved_time) IN (
            SELECT
                product_name,
                brand_name,
                MAX(saved_time) AS latest_saved_time
            FROM from_different_sources
            GROUP BY product_name, brand_name
        )
    """
    params = {}

    brands = selected_values.get('brands')
    processors = selected_values.get('processors')
    rams = selected_values.get('rams')
    screen_sizes = selected_values.get('screenSizes')
    price_interval = selected_values.get('priceInterval')

    if brands:
        base_query += f" AND {selected_column_mappings['brands']} = ANY(:brands)"
        params['brands'] = brands
    if processors:
        base_query += f" AND {selected_column_mappings['processors']} = ANY(:processors)"
        params['processors'] = processors
    if rams:
        advanced_ram_conditions = []
        for ram in rams:
            # Remove the "+" sign and convert to lowercase before querying
            ram = ram.replace('+', '').lower()
            ram_condition = f"ram ILIKE '%{ram}%'"
            advanced_ram_conditions.append(ram_condition)
        ram_condition = " OR ".join(advanced_ram_conditions)
        base_query += f" AND ({ram_condition})"
    if screen_sizes:
        advanced_screen_conditions = []
        for screen_size in screen_sizes:
            # Remove the "+" sign and convert to lowercase before querying
            screen_size = screen_size.replace('+', '').lower()
            screen_condition = f"screen ILIKE '%{screen_size}%'"
            advanced_screen_conditions.append(screen_condition)
        screen_condition = " OR ".join(advanced_screen_conditions)
        base_query += f" AND ({screen_condition})"
    if price_interval:
        base_query += " AND price BETWEEN :min_price AND :max_price"
        params['min_price'] = price_interval[0]
        params['max_price'] = price_interval[1]

    # Get the user's selected sorting option and handle default case
    selected_sort_option = request.args.get('sort', 'ascendive_price')

    # Determine which query to use based on the sorting option
    if selected_sort_option == 'ascendive_price':
        order_by_column = "price ASC"
    elif selected_sort_option == 'descending_price':
        order_by_column = "price DESC"
    elif selected_sort_option == 'descendive_review_rating':
        order_by_column = "review_rating ASC"
    elif selected_sort_option == 'ascendive_review_count':
        order_by_column = "review_count DESC"
    elif selected_sort_option == 'descendive_review_count':
        order_by_column = "review_count ASC"
    elif selected_sort_option == 'ascendive_review_rating':
        order_by_column = "review_rating DESC"
    else:
        return jsonify({"error": "Invalid sorting option"})

    page = request.args.get('page', 1, type=int)
    items_per_page = request.args.get('itemsPerPage', 20, type=int)  # Get items per page from query parameters
    base_query += f" ORDER BY {order_by_column} LIMIT :limit OFFSET :offset"
    params['limit'] = items_per_page
    params['offset'] = (page - 1) * items_per_page

    query_results = db.session.execute(text(base_query), params).fetchall()