Example #1
0
def search_users_by_options_queryset(request, include_flagged=False):
    """
    return a User queryset that filters by search opetions and some other
    basic filters like blocked usernames, etc.

    include_flagged (bool) default False
        show users be included that were already flagged (like, nope, etc)
        by auth user?
    """
    # 1
    li = User.objects.all()
    # 2 search option: sex
    if request.user.profile.f_sex > 0:
        li = li.filter(profile__sex=request.user.profile.f_sex)
    # 3 search option: age
    dob_earliest, dob_latest = get_dob_range(request.user.profile.f_minage,
                                             request.user.profile.f_maxage)
    li = li.filter(profile__dob__gt=dob_earliest, profile__dob__lt=dob_latest)
    # 4 search option: distance
    if request.user.profile.f_distance:
        lat_min, lng_min, lat_max, lng_max = get_latlng_bounderies(
            request.user.profile.lat, request.user.profile.lng,
            request.user.profile.f_distance)
        li = li.filter(profile__lat__gte=lat_min, profile__lat__lte=lat_max,
                       profile__lng__gte=lng_min, profile__lng__lte=lng_max)
    # x only show SFW profiles?
    if request.user.profile.f_over_18:  # unused
        pass
        # li = li.filter(profile__over_18=True)
    # x only users with a verified email on reddit?
    if request.user.profile.f_has_verified_email:  # unused
        pass
        # li = li.filter(profile__has_verified_email=True)
    # 5 exclude auth user themself.
    li = li.exclude(pk=request.user.pk)
    # 5a. exclude banned users
    li = li.exclude(is_active=False)
    # 5b. exclude users who deleted their account (deleted last_login)
    li = li.exclude(last_login=None)
    # 5c. exclude users with low karma
    li = li.exclude(
        profile__link_karma__lte=settings.USER_MIN_LINK_KARMA,
        profile__comment_karma__lte=settings.USER_MIN_COMMENT_KARMA)
    # 6 are not already flagged by auth user ('like', 'nope', 'block')
    if not include_flagged:
        li = li.exclude(flags_received__sender=request.user)
    # 7 are not blocked by admin via username blocklist,
    li = li.exclude(username__in=get_blocked_usernames_list())
    # 8 have at least one picture URL,
    li = li.exclude(profile___pics='"[]"')

    print('--> li.query', li.query)
    return li
Example #2
0
def search_users_by_options_queryset(params, include_flagged=False):
    """
    Return a User queryset that filters by search options of the user and
    some other basic filters, like globally blocked usernames, etc. this is
    used, for example, for the Sr view user list.

    :params: a dict of search parameters.
    :user: auth user
    :include_flagged: <bool> default False. Whether to include users that were
        already flagged (like, nope, etc) by user.
    """
    # 1
    li = User.objects.all().prefetch_related('profile')

    # 2 search option: sex
    if 'sex' in params and params['sex']:
        li = li.filter(profile__sex=params['sex'])

    # 3 search option: age
    if 'minage' not in params:
        params['minage'] = 18
    if 'maxage' not in params:
        params['maxage'] = 100
    dob_earliest, dob_latest = get_dob_range(params['minage'], params['maxage'])
    li = li.filter(profile__dob__gt=dob_earliest, profile__dob__lt=dob_latest)

    # 4 search option: distance
    # Values too close are inaccurate because of location fuzzying. Also,
    # "distance" must be at least 1, so that the signup flow doesn't intercept
    # it because it has no value set! Leave this to only search distances
    # above 5 km or so, and return "worldwide" for any value below 5 km.
    if 'distance' in params and 'lat' in params and \
                    'lng' in params and params['distance'] > 5:
        lat_min, lng_min, lat_max, lng_max = get_latlng_bounderies(
            params['lat'], params['lng'], params['distance'])
        li = li.filter(profile__lat__gte=lat_min, profile__lat__lte=lat_max,
                       profile__lng__gte=lng_min, profile__lng__lte=lng_max)

    # 5 exclude auth user themself
    if 'user_id' in params:
        li = li.exclude(pk=params['user_id'])

    # 5a. exclude banned users
    li = li.exclude(is_active=False)

    # 5b. exclude users who deleted their account (i.e. last_login==None)
    li = li.exclude(last_login=None)

    # 5c. exclude users with low karma
    li = li.exclude(
        profile__link_karma__lte=settings.USER_MIN_LINK_KARMA,
        profile__comment_karma__lte=settings.USER_MIN_COMMENT_KARMA)

    # 6 are not noped by auth user ('like', 'nope', 'block')
    # if not include_flagged:
    #     li = li.exclude(flags_received__sender=user)

    # 7 are not blocked by admin via username blocklist,
    li = li.exclude(username__in=get_blocked_usernames_list())

    # 8 have at least one picture URL,
    if 'hide_no_pic' in params and params['hide_no_pic']:
        li = li.exclude(profile___pics__in=('', '[]', ))

    # 9 only users with a verified email on reddit?
    if 'has_verified_email' in params and params['has_verified_email']:
        li = li.filter(profile__has_verified_email=True)

    return li
Example #3
0
def search_users(request, usernames_only=True):
    """
    Return a list of usernames that are matches for auth user's selected
    search options and also are subscribes to one or more of the same
    subreddits as auth user. The returned username list is ordered by the
    number of subreddits they share with auth user, with the largest number
    first.

    Search options are used only if auth user selected it, otherwise it will
    be ignored.

    The query is build as raw() SQL because there doesn't seem to be a way to
    build the subreddit subscription counting and ordering in Django.

    Since its not possible to inject the other parts into a raw() query
    (see https://code.djangoproject.com/ticket/17741), the entire query is
    build in SQL.
    """
    is_filter_block_flag_only = True
    query_params = []
    query_string = ''

    # part 1
    query_params += []
    query_string += '''
        SELECT
            au.id, au.username, ap.created, ap.accessed, ap.views_count,
            COUNT(r1.user_id) AS sr_count

        FROM dtr5app_subscribed r1

        INNER JOIN dtr5app_subscribed r2
            ON r1.sr_id = r2.sr_id AND r1.user_id <> r2.user_id

        INNER JOIN auth_user au
            ON r2.user_id = au.id

        INNER JOIN dtr5app_sr sr
            ON r1.sr_id = sr.id

        INNER JOIN dtr5app_profile ap
            ON au.id = ap.user_id

        WHERE au.is_active IS TRUE AND last_login IS NOT NULL '''

    # part 1.1
    # if the user has set a maximum size for subreddits to be considered
    # in search. this can be used to filter all the huge default subs that
    # most redditors belong to.
    #
    # REMOVED!
    #
    # if request.user.profile.f_ignore_sr_max:
    #     query_params += [request.user.profile.f_ignore_sr_max]
    #     query_string += ''' AND sr.subscribers < %s '''

    # part 1.2
    # a list of Sr.display_name values. these subreddits should NOT be
    # considered when producing matches.
    # Subreddit names should appear as case insensitive! The f_ignore_sr_li
    # list of subreddit names is supposed to be "cleaned up" already, with
    # the appropriate lettercase of a subreddit's name.
    #
    # REMOVED!
    #
    # if request.user.profile.f_ignore_sr_li:
    #     query_params += request.user.profile.f_ignore_sr_li
    #     x = ', '.join(['%s'] * len(request.user.profile.f_ignore_sr_li))
    #     query_string += ''' AND sr.id NOT IN (
    #                             SELECT id FROM dtr5app_sr sr2
    #                             WHERE sr2.display_name IN (''' + x + ' )) '

    # part 1.3
    # Search only those subs that auth user marked as their favorites. By
    # default, all subreddits a user is subbed to are their favorites, unless
    # they have manually de-selected some in the search settings.
    query_params += []
    query_string += ''' AND r1.is_favorite '''

    # part 1.9
    # Set r1 to be the auth user, and r2 to be the users we are searching for,
    # then do a sub-query to join them with their profiles, so we can search
    # the profile for auth user's search settings.
    query_params += [request.user.id]
    query_string += ''' AND r1.user_id = %s AND au.id IN (
                            SELECT id FROM auth_user u
                            INNER JOIN dtr5app_profile p
                                ON u.id = p.user_id
                            WHERE 1=1 '''

    # part 2: sex --> TODO: search by gender!
    # li = li.filter(profile__sex=request.user.profile.f_sex)
    if request.user.profile.f_sex > 0:
        query_params += [request.user.profile.f_sex]
        query_string += ''' AND p.sex = %s '''

    # part 3: date of birth
    dob_earliest, dob_latest = get_dob_range(request.user.profile.f_minage,
                                             request.user.profile.f_maxage)
    query_params += [dob_earliest, dob_latest]
    query_string += ''' AND p.dob >= %s AND p.dob <= %s '''

    # part 4: lat/lng
    # li = li.filter(profile__lat__gte=lat_min, profile__lat__lte=lat_max,
    #                profile__lng__gte=lng_min, profile__lng__lte=lng_max)
    #
    # Values too close are inaccurate because of location fuzzying. Also,
    # f_distance must be at least 1, so that the signup flow doesn't intercept
    # it because it has no value set! Leave this to only search distances
    # above 5 km or so, and return "worldwide" for any value below 5 km.
    #
    if request.user.profile.f_distance > 5:
        lat_min, lng_min, lat_max, lng_max = get_latlng_bounderies(
            request.user.profile.lat,
            request.user.profile.lng,
            request.user.profile.f_distance)
        query_params += [lat_max, lng_min, lat_min, lng_max]
        query_string += ''' AND p.lat <= %s AND p.lng >= %s
                            AND p.lat >= %s AND p.lng <= %s '''

    # part 5: exclude auth user themself
    # li = li.exclude(pk=request.user.pk)
    query_params += [request.user.id]
    query_string += ''' AND NOT (u.id = %s) '''

    # part 6: exclude users who already have a like/nope flag from auth user
    # li = li.exclude(flags_received__sender=request.user)
    if is_filter_block_flag_only:
        # exclude only users who have a BLOCK_FLAG ("hide") from auth user
        query_params += [Flag.BLOCK_FLAG, request.user.id]
        query_string += ''' AND NOT (u.id IN (SELECT U1.receiver_id AS Col1
                            FROM dtr5app_flag U1
                            WHERE U1.flag = %s AND U1.sender_id = %s)) '''
    else:
        # Exclude all users flagged by auth user: LIKE_FLAG, BLOCK_FLAG, etc.
        query_params += [request.user.id]
        query_string += ''' AND NOT (u.id IN (SELECT U1.receiver_id AS Col1
                            FROM dtr5app_flag U1 WHERE U1.sender_id = %s)) '''

    # part 7: exclude globally blocked usernames
    # li = li.exclude(username__in=get_blocked_usernames_list())
    # --> TODO: currently empty.
    pass

    # part 8: have at least one picture URL in the JSON string
    # li = li.exclude(profile___pics='[]')
    #
    # TODO: for now, allow no-picture profiles, to make testing easier
    #
    # Note: there was an error causing an empty pic list to be written as [] to
    #       the field.
    if request.user.profile.f_hide_no_pic:
        query_params += []
        query_string += ''' AND p._pics NOT IN ('', '[]') '''

    # part 9: only users with a verified email on reddit
    # li = li.filter(profile__has_verified_email=True)
    if request.user.profile.f_has_verified_email:
        query_params += []
        query_string += ''' AND p.has_verified_email '''

    # finish up: fetch 1000 matches with most subs in common
    query_params += [getattr(settings, 'RESULTS_BUFFER_LEN', 1000)]
    query_string += ''' ) GROUP BY r1.user_id, au.id, ap.user_id
                          ORDER BY sr_count DESC LIMIT %s '''
    users = User.objects.raw(query_string, query_params)

    # re-order the results: re-order the 1000 matches by the value stored in the
    # user's session, so they can see seemingly different kinds of lists.
    order_by = request.session.get('search_results_order', '')

    if order_by == '-accessed':  # most recently accessed first
        users = sorted(users, key=lambda u: u.accessed, reverse=True)
    elif order_by == 'accessed':  # most recently accessed last
        users = sorted(users, key=lambda u: u.accessed, reverse=False)
    elif order_by == 'date_joined':  # oldest redddate account
        users = sorted(users, key=lambda u: u.date_joined, reverse=True)
    elif order_by == '-date_joined':  # most recent redddate acccount
        users = sorted(users, key=lambda u: u.date_joined, reverse=True)
    # Not posstible because of the "created" bug when it fetched a unixtime of
    # "0" from reddit. There are still many accounts that have a "created" date
    # set to "1970-01-01T00:00:00".
    #
    # elif order_by == 'reddit_joined':  # oldest REDDIT account
    #     users = sorted(users, key=lambda u: u.created, reverse=False)
    # elif order_by == '-reddit_joined':  # most recent REDDIT account
    #     users = sorted(users, key=lambda u: u.created, reverse=True)
    elif order_by == '-views_count':  # most views first
        users = sorted(users, key=lambda u: u.views_count, reverse=True)
    elif order_by == 'views_count':  # most views last
        users = sorted(users, key=lambda u: u.views_count, reverse=False)
    else:  # -sr_count (default): most subs in common first
        users = sorted(users, key=lambda u: u.sr_count, reverse=True)

    # default return a list of only usernames
    if usernames_only:
        return [x.username for x in users]
    else:
        return users
Example #4
0
def search_users_by_options_queryset(params, include_flagged=False):
    """
    Return a User queryset that filters by search options of the user and
    some other basic filters, like globally blocked usernames, etc. this is
    used, for example, for the Sr view user list.

    :params: a dict of search parameters.
    :user: auth user
    :include_flagged: <bool> default False. Whether to include users that were
        already flagged (like, nope, etc) by user.
    """
    # 1
    li = User.objects.all().prefetch_related('profile')

    # 2 search option: sex
    if 'sex' in params and params['sex']:
        li = li.filter(profile__sex=params['sex'])

    # 3 search option: age
    if 'minage' not in params:
        params['minage'] = 18
    if 'maxage' not in params:
        params['maxage'] = 100
    dob_earliest, dob_latest = get_dob_range(params['minage'],
                                             params['maxage'])
    li = li.filter(profile__dob__gt=dob_earliest, profile__dob__lt=dob_latest)

    # 4 search option: distance
    # Values too close are inaccurate because of location fuzzying. Also,
    # "distance" must be at least 1, so that the signup flow doesn't intercept
    # it because it has no value set! Leave this to only search distances
    # above 5 km or so, and return "worldwide" for any value below 5 km.
    if 'distance' in params and 'lat' in params and \
                    'lng' in params and params['distance'] > 5:
        lat_min, lng_min, lat_max, lng_max = get_latlng_bounderies(
            params['lat'], params['lng'], params['distance'])
        li = li.filter(profile__lat__gte=lat_min,
                       profile__lat__lte=lat_max,
                       profile__lng__gte=lng_min,
                       profile__lng__lte=lng_max)

    # 5 exclude auth user themself
    if 'user_id' in params:
        li = li.exclude(pk=params['user_id'])

    # 5a. exclude banned users
    li = li.exclude(is_active=False)

    # 5b. exclude users who deleted their account (i.e. last_login==None)
    li = li.exclude(last_login=None)

    # 5c. exclude users with low karma
    li = li.exclude(
        profile__link_karma__lte=settings.USER_MIN_LINK_KARMA,
        profile__comment_karma__lte=settings.USER_MIN_COMMENT_KARMA)

    # 6 are not noped by auth user ('like', 'nope', 'block')
    # if not include_flagged:
    #     li = li.exclude(flags_received__sender=user)

    # 7 are not blocked by admin via username blocklist,
    li = li.exclude(username__in=get_blocked_usernames_list())

    # 8 have at least one picture URL,
    if 'hide_no_pic' in params and params['hide_no_pic']:
        li = li.exclude(profile___pics__in=(
            '',
            '[]',
        ))

    # 9 only users with a verified email on reddit?
    if 'has_verified_email' in params and params['has_verified_email']:
        li = li.filter(profile__has_verified_email=True)

    return li
Example #5
0
def search_users(request, usernames_only=True):
    """
    Return a list of usernames that are matches for auth user's selected
    search options and also are subscribes to one or more of the same
    subreddits as auth user. The returned username list is ordered by the
    number of subreddits they share with auth user, with the largest number
    first.

    Search options are used only if auth user selected it, otherwise it will
    be ignored.

    The query is build as raw() SQL because there doesn't seem to be a way to
    build the subreddit subscription counting and ordering in Django.

    Since its not possible to inject the other parts into a raw() query
    (see https://code.djangoproject.com/ticket/17741), the entire query is
    build in SQL.
    """
    is_filter_block_flag_only = True
    query_params = []
    query_string = ''

    # part 1
    query_params += []
    query_string += '''
        SELECT
            au.id, au.username, ap.created, ap.accessed, ap.views_count,
            COUNT(r1.user_id) AS sr_count

        FROM dtr5app_subscribed r1

        INNER JOIN dtr5app_subscribed r2
            ON r1.sr_id = r2.sr_id AND r1.user_id <> r2.user_id

        INNER JOIN auth_user au
            ON r2.user_id = au.id

        INNER JOIN dtr5app_sr sr
            ON r1.sr_id = sr.id

        INNER JOIN dtr5app_profile ap
            ON au.id = ap.user_id

        WHERE au.is_active IS TRUE AND last_login IS NOT NULL '''

    # part 1.1
    # if the user has set a maximum size for subreddits to be considered
    # in search. this can be used to filter all the huge default subs that
    # most redditors belong to.
    #
    # REMOVED!
    #
    # if request.user.profile.f_ignore_sr_max:
    #     query_params += [request.user.profile.f_ignore_sr_max]
    #     query_string += ''' AND sr.subscribers < %s '''

    # part 1.2
    # a list of Sr.display_name values. these subreddits should NOT be
    # considered when producing matches.
    # Subreddit names should appear as case insensitive! The f_ignore_sr_li
    # list of subreddit names is supposed to be "cleaned up" already, with
    # the appropriate lettercase of a subreddit's name.
    #
    # REMOVED!
    #
    # if request.user.profile.f_ignore_sr_li:
    #     query_params += request.user.profile.f_ignore_sr_li
    #     x = ', '.join(['%s'] * len(request.user.profile.f_ignore_sr_li))
    #     query_string += ''' AND sr.id NOT IN (
    #                             SELECT id FROM dtr5app_sr sr2
    #                             WHERE sr2.display_name IN (''' + x + ' )) '

    # part 1.3
    # Search only those subs that auth user marked as their favorites. By
    # default, all subreddits a user is subbed to are their favorites, unless
    # they have manually de-selected some in the search settings.
    query_params += []
    query_string += ''' AND r1.is_favorite '''

    # part 1.9
    # Set r1 to be the auth user, and r2 to be the users we are searching for,
    # then do a sub-query to join them with their profiles, so we can search
    # the profile for auth user's search settings.
    query_params += [request.user.id]
    query_string += ''' AND r1.user_id = %s AND au.id IN (
                            SELECT id FROM auth_user u
                            INNER JOIN dtr5app_profile p
                                ON u.id = p.user_id
                            WHERE 1=1 '''

    # part 2: sex --> TODO: search by gender!
    # li = li.filter(profile__sex=request.user.profile.f_sex)
    if request.user.profile.f_sex > 0:
        query_params += [request.user.profile.f_sex]
        query_string += ''' AND p.sex = %s '''

    # part 3: date of birth
    dob_earliest, dob_latest = get_dob_range(request.user.profile.f_minage,
                                             request.user.profile.f_maxage)
    query_params += [dob_earliest, dob_latest]
    query_string += ''' AND p.dob >= %s AND p.dob <= %s '''

    # part 4: lat/lng
    # li = li.filter(profile__lat__gte=lat_min, profile__lat__lte=lat_max,
    #                profile__lng__gte=lng_min, profile__lng__lte=lng_max)
    #
    # Values too close are inaccurate because of location fuzzying. Also,
    # f_distance must be at least 1, so that the signup flow doesn't intercept
    # it because it has no value set! Leave this to only search distances
    # above 5 km or so, and return "worldwide" for any value below 5 km.
    #
    if request.user.profile.f_distance > 5:
        lat_min, lng_min, lat_max, lng_max = get_latlng_bounderies(
            request.user.profile.lat, request.user.profile.lng,
            request.user.profile.f_distance)
        query_params += [lat_max, lng_min, lat_min, lng_max]
        query_string += ''' AND p.lat <= %s AND p.lng >= %s
                            AND p.lat >= %s AND p.lng <= %s '''

    # part 5: exclude auth user themself
    # li = li.exclude(pk=request.user.pk)
    query_params += [request.user.id]
    query_string += ''' AND NOT (u.id = %s) '''

    # part 6: exclude users who already have a like/nope flag from auth user
    # li = li.exclude(flags_received__sender=request.user)
    if is_filter_block_flag_only:
        # exclude only users who have a BLOCK_FLAG ("hide") from auth user
        query_params += [Flag.BLOCK_FLAG, request.user.id]
        query_string += ''' AND NOT (u.id IN (SELECT U1.receiver_id AS Col1
                            FROM dtr5app_flag U1
                            WHERE U1.flag = %s AND U1.sender_id = %s)) '''
    else:
        # Exclude all users flagged by auth user: LIKE_FLAG, BLOCK_FLAG, etc.
        query_params += [request.user.id]
        query_string += ''' AND NOT (u.id IN (SELECT U1.receiver_id AS Col1
                            FROM dtr5app_flag U1 WHERE U1.sender_id = %s)) '''

    # part 7: exclude globally blocked usernames
    # li = li.exclude(username__in=get_blocked_usernames_list())
    # --> TODO: currently empty.
    pass

    # part 8: have at least one picture URL in the JSON string
    # li = li.exclude(profile___pics='[]')
    #
    # TODO: for now, allow no-picture profiles, to make testing easier
    #
    # Note: there was an error causing an empty pic list to be written as [] to
    #       the field.
    if request.user.profile.f_hide_no_pic:
        query_params += []
        query_string += ''' AND p._pics NOT IN ('', '[]') '''

    # part 9: only users with a verified email on reddit
    # li = li.filter(profile__has_verified_email=True)
    if request.user.profile.f_has_verified_email:
        query_params += []
        query_string += ''' AND p.has_verified_email '''

    # finish up: fetch 1000 matches with most subs in common
    query_params += [getattr(settings, 'RESULTS_BUFFER_LEN', 1000)]
    query_string += ''' ) GROUP BY r1.user_id, au.id, ap.user_id
                          ORDER BY sr_count DESC LIMIT %s '''
    users = User.objects.raw(query_string, query_params)

    # re-order the results: re-order the 1000 matches by the value stored in the
    # user's session, so they can see seemingly different kinds of lists.
    order_by = request.session.get('search_results_order', '')

    if order_by == '-accessed':  # most recently accessed first
        users = sorted(users, key=lambda u: u.accessed, reverse=True)
    elif order_by == 'accessed':  # most recently accessed last
        users = sorted(users, key=lambda u: u.accessed, reverse=False)
    elif order_by == 'date_joined':  # oldest redddate account
        users = sorted(users, key=lambda u: u.date_joined, reverse=True)
    elif order_by == '-date_joined':  # most recent redddate acccount
        users = sorted(users, key=lambda u: u.date_joined, reverse=True)
    # Not posstible because of the "created" bug when it fetched a unixtime of
    # "0" from reddit. There are still many accounts that have a "created" date
    # set to "1970-01-01T00:00:00".
    #
    # elif order_by == 'reddit_joined':  # oldest REDDIT account
    #     users = sorted(users, key=lambda u: u.created, reverse=False)
    # elif order_by == '-reddit_joined':  # most recent REDDIT account
    #     users = sorted(users, key=lambda u: u.created, reverse=True)
    elif order_by == '-views_count':  # most views first
        users = sorted(users, key=lambda u: u.views_count, reverse=True)
    elif order_by == 'views_count':  # most views last
        users = sorted(users, key=lambda u: u.views_count, reverse=False)
    else:  # -sr_count (default): most subs in common first
        users = sorted(users, key=lambda u: u.sr_count, reverse=True)

    # default return a list of only usernames
    if usernames_only:
        return [x.username for x in users]
    else:
        return users
Example #6
0
def search_users_by_options_queryset(user, include_flagged=False):
    """
    return a User queryset that filters by search options of the user and
    some other basic filters, like globally blocked usernames, etc. this is
    used, for example, for the Sr view user list.

    :user: <User instance> who's search option settings to use. Most likely,
        this is the request.user instance.

    :include_flagged: <bool> default False. Whether to include users that were
        already flagged (like, nope, etc) by user.
    """
    # 1
    li = User.objects.all()

    # 2 search option: sex
    if user.profile.f_sex > 0:
        li = li.filter(profile__sex=user.profile.f_sex)

    # 3 search option: age
    dob_earliest, dob_latest = get_dob_range(user.profile.f_minage,
                                             user.profile.f_maxage)
    li = li.filter(profile__dob__gt=dob_earliest, profile__dob__lt=dob_latest)

    # 4 search option: distance
    if user.profile.f_distance:
        lat_min, lng_min, lat_max, lng_max = get_latlng_bounderies(
            user.profile.lat, user.profile.lng,
            user.profile.f_distance)
        li = li.filter(profile__lat__gte=lat_min, profile__lat__lte=lat_max,
                       profile__lng__gte=lng_min, profile__lng__lte=lng_max)

    # x only show SFW profiles?
    if user.profile.f_over_18:  # unused
        pass
        # li = li.filter(profile__over_18=True)

    # x only users with a verified email on reddit?
    if user.profile.f_has_verified_email:  # unused
        pass
        # li = li.filter(profile__has_verified_email=True)

    # 5 exclude auth user themself.
    li = li.exclude(pk=user.pk)

    # 5a. exclude banned users
    li = li.exclude(is_active=False)

    # 5b. exclude users who deleted their account (deleted last_login)
    li = li.exclude(last_login=None)

    # 5c. exclude users with low karma
    li = li.exclude(
        profile__link_karma__lte=settings.USER_MIN_LINK_KARMA,
        profile__comment_karma__lte=settings.USER_MIN_COMMENT_KARMA)

    # 6 are not already flagged by auth user ('like', 'nope', 'block')
    if not include_flagged:
        li = li.exclude(flags_received__sender=user)

    # 7 are not blocked by admin via username blocklist,
    li = li.exclude(username__in=get_blocked_usernames_list())

    # 8 have at least one picture URL,
    li = li.exclude(profile___pics='"[]"')

    if settings.DEBUG:
        print('--> li.query', li.query)

    return li
Example #7
0
def search_users(request, usernames_only=True):
    """
    Return a list of usernames that are matches for auth user's selected
    search options and also are subscribes to one or more of the same
    subreddits as auth user. The returned username list is ordered by the
    number of subreddits they share with auth user, with the largest number
    first.

    Search options are used only if auth user selected it, otherwise it will
    be ignored.

    The query is build as raw() SQL because there doesn't seem to be a way to
    build the subreddit subscription counting and ordering in Django.

    Since its not possible to inject the other parts into a raw() query
    (see https://code.djangoproject.com/ticket/17741), the entire query is
    build in SQL.
    """
    BUFFER_LEN = getattr(settings, 'RESULTS_BUFFER_LEN', 500)
    # SR_LIMIT = getattr(settings, 'SR_LIMIT', 50)
    # SR_MIN_SUBS = getattr(settings, 'SR_MIN_SUBS', 100)
    # SR_MAX_SUBS = getattr(settings, 'SR_MAX_SUBS', 5000000)

    # f_ignore_sr_li
    # f_ignore_sr_max
    # f_exclude_sr_li

    # fetch users and number of same subscribed subreddits for all users
    # that are subscribed to the same subreddits than auth user; fetch a max
    # of BUFFER_LEN items. This query only touches the dtr5app_subscribed
    # table and does not requore any join with other tables.
    query_params = []
    query_string = ''

    # part 1
    query_params += []
    query_string += '''
        SELECT au.id, au.username, COUNT(r1.user_id) AS sr_count
        FROM dtr5app_subscribed r1

        INNER JOIN dtr5app_subscribed r2
            ON r1.sr_id = r2.sr_id AND r1.user_id <> r2.user_id

        INNER JOIN auth_user au
            ON r2.user_id = au.id

        INNER JOIN dtr5app_sr sr
            ON r1.sr_id = sr.id

        WHERE au.is_active IS TRUE AND last_login IS NOT NULL '''

    # part 1.1
    # if the user has set a maximum size for subreddits to be considered
    # in search. this can be used to filter all the huge default subs that
    # most redditors belong to.
    if request.user.profile.f_ignore_sr_max:
        query_params += [request.user.profile.f_ignore_sr_max]
        query_string += ''' AND sr.subscribers < %s '''

    # part 1.2
    # a list of Sr.display_name values. these subreddits should NOT be
    # considered when producing matches.
    # Subreddit names should appear as case insensitive! The f_ignore_sr_li
    # list of subreddit names is supposed to be "cleaned up" already, with
    # the appropriate lettercase of a subreddit's name.
    if request.user.profile.f_ignore_sr_li:
        query_params += request.user.profile.f_ignore_sr_li
        x = ', '.join(['%s'] * len(request.user.profile.f_ignore_sr_li))
        query_string += ''' AND sr.id NOT IN (
                                SELECT id FROM dtr5app_sr sr2
                                WHERE sr2.display_name IN (''' + x + ' )) '

    # part 1.9
    query_params += [request.user.id]
    query_string += ''' AND r1.user_id = %s AND au.id IN (
                            SELECT id FROM auth_user u
                            INNER JOIN dtr5app_profile p
                                ON u.id = p.user_id
                            WHERE 1=1 '''

    # part 2: sex --> TODO: search by gender!
    # li = li.filter(profile__sex=request.user.profile.f_sex)
    if request.user.profile.f_sex > 0:
        query_params += [request.user.profile.f_sex]
        query_string += ''' AND p.sex = %s '''

    # part 3: date of birth
    dob_earliest, dob_latest = get_dob_range(request.user.profile.f_minage,
                                             request.user.profile.f_maxage)
    query_params += [dob_earliest, dob_latest]
    query_string += ''' AND p.dob >= %s AND p.dob <= %s '''

    # part 4: lat/lng
    # li = li.filter(profile__lat__gte=lat_min, profile__lat__lte=lat_max,
    #                profile__lng__gte=lng_min, profile__lng__lte=lng_max)
    if request.user.profile.f_distance:
        lat_min, lng_min, lat_max, lng_max = get_latlng_bounderies(
            request.user.profile.lat,
            request.user.profile.lng,
            request.user.profile.f_distance)
        query_params += [lat_max, lng_min, lat_min, lng_max]
        query_string += ''' AND p.lat <= %s AND p.lng >= %s
                            AND p.lat >= %s AND p.lng <= %s '''

    # part 5: exclude auth user themself
    # li = li.exclude(pk=request.user.pk)
    query_params += [request.user.id]
    query_string += ''' AND NOT (u.id = %s) '''

    # part 6: exclude users who already have a like/nope flag from auth user
    # li = li.exclude(flags_received__sender=request.user)
    query_params += [request.user.id]
    query_string += ''' AND NOT (u.id IN (SELECT U1.receiver_id AS Col1
                        FROM dtr5app_flag U1 WHERE U1.sender_id = %s)) '''

    # part 7: exclude globally blocked usernames
    # li = li.exclude(username__in=get_blocked_usernames_list())
    # --> TODO: currently empty.
    pass

    # part 8: have at least one picture URL in the JSON string
    # li = li.exclude(profile___pics='[]')
    # TODO: for now, allow no-picture profiles, to make testing easier
    # query_params += []
    # query_string += ''' AND NOT (p._pics = '[]') '''

    # finish up
    query_params += [BUFFER_LEN]
    query_string += ''' ) GROUP BY r1.user_id, au.id
                          ORDER BY sr_count DESC LIMIT %s '''

    # execute the query with the collected params
    users = User.objects.raw(query_string, query_params)

    # print('='*50)
    # print(repr(users))
    # print('='*50)
    # for u in users:
    #   print('share {} subs with {}:{}'.format(u.sr_count, u.id, u.username))
    # print('='*50)

    # default return a list of only usernames
    if usernames_only:
        return [x.username for x in users]
    else:
        return users
Example #8
0
def search_users_by_options_queryset(user, include_flagged=False):
    """
    return a User queryset that filters by search options of the user and
    some other basic filters, like globally blocked usernames, etc. this is
    used, for example, for the Sr view user list.

    :user: <User instance> who's search option settings to use. Most likely,
        this is the request.user instance.

    :include_flagged: <bool> default False. Whether to include users that were
        already flagged (like, nope, etc) by user.
    """
    # 1
    li = User.objects.all().prefetch_related('profile')
    if settings.DEBUG:
        print('search_users_by_options_queryset --> all: ', li.count())

    # 2 search option: sex
    if user.profile.f_sex > 0:
        li = li.filter(profile__sex=user.profile.f_sex)
    if settings.DEBUG:
        print('search_users_by_options_queryset --> f_sex: ', li.count())

    # 3 search option: age
    dob_earliest, dob_latest = get_dob_range(user.profile.f_minage,
                                             user.profile.f_maxage)
    li = li.filter(profile__dob__gt=dob_earliest, profile__dob__lt=dob_latest)
    if settings.DEBUG:
        print('search_users_by_options_queryset --> dob: ', li.count())

    # 4 search option: distance
    #
    # Values too close are inaccurate because of location fuzzying. Also,
    # f_distance must be at least 1, so that the signup flow doesn't intercept
    # it because it has no value set! Leave this to only search distances
    # above 5 km or so, and return "worldwide" for any value below 5 km.
    #
    if user.profile.f_distance > 5:
        lat_min, lng_min, lat_max, lng_max = get_latlng_bounderies(
            user.profile.lat, user.profile.lng,
            user.profile.f_distance)
        li = li.filter(profile__lat__gte=lat_min, profile__lat__lte=lat_max,
                       profile__lng__gte=lng_min, profile__lng__lte=lng_max)
    if settings.DEBUG:
        print('search_users_by_options_queryset --> distance: ', li.count())

    # x only show SFW profiles?
    if user.profile.f_over_18:  # unused
        pass
        # li = li.filter(profile__over_18=True)

    # x only users with a verified email on reddit?
    if user.profile.f_has_verified_email:  # unused
        pass
        # li = li.filter(profile__has_verified_email=True)

    # 5 exclude auth user themself.
    li = li.exclude(pk=user.pk)
    if settings.DEBUG:
        print('search_users_by_options_queryset --> user.pk: ', li.count())

    # 5a. exclude banned users
    li = li.exclude(is_active=False)
    if settings.DEBUG:
        print('search_users_by_options_queryset --> is_active: ', li.count())

    # 5b. exclude users who deleted their account (deleted last_login)
    li = li.exclude(last_login=None)
    if settings.DEBUG:
        print('search_users_by_options_queryset --> last_login: '******'search_users_by_options_queryset --> karma: ', li.count())

    # 6 are not already flagged by auth user ('like', 'nope', 'block')
    if not include_flagged:
        li = li.exclude(flags_received__sender=user)
    if settings.DEBUG:
        print('search_users_by_options_queryset --> flagged: ', li.count())

    # 7 are not blocked by admin via username blocklist,
    li = li.exclude(username__in=get_blocked_usernames_list())
    if settings.DEBUG:
        print('search_users_by_options_queryset --> blocklist: ', li.count())

    # 8 have at least one picture URL,
    if getattr(settings, 'REQUIRE_PROFILE_PIC', False):
        li = li.exclude(profile___pics='')
    if settings.DEBUG:
        print('search_users_by_options_queryset --> pics: ', li.count())

    if settings.DEBUG:
        print('--> li.query', li.query)

    return li