def _front_page( paging_size=settings.PAGING_SIZE, page=0, add_filter={}, add_q=[], as_of=None, days_back=50, ): # TODO: weighting https://medium.com/hacking-and-gonzo/how-hacker-news-ranking-algorithm-works-1d9b0cf2c08d # (P-1) / (T+2)^G if as_of is None: now = timezone.now() else: now = as_of if connection.vendor == "postgresql": now_value = Value(now, output_field=fields.DateTimeField()) submission_age_float = ExpressionWrapper( (now_value - F("created_at")), output_field=fields.DurationField()) submission_age_hours = ExpressionWrapper( Extract(F("tf"), "epoch") / 60 / 60 + 2.1, output_field=fields.FloatField()) real_p = ExpressionWrapper(F("points") - 1, output_field=fields.FloatField()) formula = ExpressionWrapper(F("p") / (Power(F("tfh"), F("g")) + 0.001), output_field=fields.FloatField()) return (Story.objects.select_related("user").filter( duplicate_of__isnull=True).filter(points__gte=1).filter( created_at__gte=now - datetime.timedelta(days=days_back)).filter( created_at__lte=now).filter(**add_filter).annotate( tf=submission_age_float). annotate(tfh=submission_age_hours).annotate(p=real_p).annotate( g=Value(1.8, output_field=fields.FloatField())).annotate( formula=formula).order_by("-formula")[( page * paging_size):(page + 1) * (paging_size)]) elif connection.vendor == "sqlite": now_value = Value(now, output_field=fields.DateTimeField()) submission_age_float = ExpressionWrapper( (now_value - F("created_at")), output_field=fields.FloatField()) submission_age_hours = ExpressionWrapper( F("tf") / 60 / 60 / 1000000 + 2.1, output_field=fields.FloatField()) real_p = ExpressionWrapper(F("points") - 1, output_field=fields.FloatField()) formula = ExpressionWrapper(F("p") / (Power(F("tfh"), F("g")) + 0.001), output_field=fields.FloatField()) return (Story.objects.select_related("user").filter( duplicate_of__isnull=True).filter(points__gte=1).filter( created_at__gte=now - datetime.timedelta(days=days_back)).filter( created_at__lte=now).filter(**add_filter).annotate( tf=submission_age_float). annotate(tfh=submission_age_hours).annotate(p=real_p).annotate( g=Value(1.8, output_field=fields.FloatField())).annotate( formula=formula).order_by("-formula")[( page * paging_size):(page + 1) * (paging_size)]) else: raise NotImplementedError( "No frontpage magic for database engine %s implemented" % (connection.vendor))
def get_haversine_distance_annotation(current_lat, current_long): """ This uses the ‘haversine’ formula to calculate the great-circle distance between two points – that is, the shortest distance over the earth’s surface – giving an ‘as-the-crow-flies’ distance between the points (ignoring any hills they fly over, of course!). Haversine formula: a = sin²(Δφ/2) + cos φ1 ⋅ cos φ2 ⋅ sin²(Δλ/2) c = 2 ⋅ atan2( √a, √(1−a) ) d = R ⋅ c where φ is latitude, λ is longitude, R is earth’s radius (mean radius = 6,371km); note that angles need to be in radians to pass to trig functions! """ R = 6371.0088 dlat = Radians(F('latitude')) - Radians(current_lat) dlong = Radians(F('longitude')) - Radians(current_long) a = ( Power(Sin(dlat/2), 2) + Cos(Radians(current_lat)) * Cos(Radians(F('latitude'))) * Power(Sin(dlong/2), 2) ) c = 2 * ATan2(Sqrt(a), Sqrt(1-a)) d = R * c return d
def get(self, request): start_time = time.time() _lat = float(request.query_params.get('latitude', 35.1874726)) _lon = float(request.query_params.get('longitude', 126.900115)) inputcate = request.query_params.get('category', '한식') catelist = list(inputcate.split(',')) for ind, k in enumerate(catelist): if catelist[ind] == '음식점': catelist.pop(ind) catelist.extend( ['중식', '술집', '한식', '카페', '분식', '세계음식', '양식', '뷔페', '일식']) zoom = int(request.query_params.get('zoomlevel', 14)) searchrange = (15.9 * (2**(19 - zoom)) * 2) / 1000 dlat = Radians(F('latitude') - _lat) dlong = Radians(F('longitude') - _lon) a = (Power(Sin(dlat / 2), 2) + Cos(Radians(_lat)) * Cos(Radians(F('latitude'))) * Power(Sin(dlong / 2), 2)) c = 2 * ATan2(Sqrt(a), Sqrt(1 - a)) d = 6371 * c te = Location.objects.select_related('category').annotate( distance=d).filter(distance__lt=searchrange).filter( category__name__in=catelist).order_by('rank') if (te.count() > 50): te = te[:50] serializer = LocationSerializer(te, many=True) return Response(serializer.data)
def get(self, request): _flag = request.query_params.get('flag', 'up') _cnt = int(request.query_params.get('cnt', 1)) _order = int(request.query_params.get('order', 2)) _taste = int(request.query_params.get('ta', 3)) _activity = int(request.query_params.get('ac', 3)) _tour = int(request.query_params.get('to', 3)) print(_order) imk = [_taste, _tour, _activity] ima = F('taste') * imk[0] + F('tour') * imk[1] + F('activity') * imk[2] imb = (Sqrt( Power(F('taste'), 2) + Power(F('tour'), 2) + Power(F('activity'), 2)) * ((imk[0]**2 + imk[1]**2 + imk[2]**2)**0.5)) imc = ima / imb if _flag == 'up': imresult = CourseTotal.objects.annotate( cosdistance=ExpressionWrapper(imc, output_field=FloatField()) ).annotate( count=Count('coursedetail')).order_by('-cosdistance').filter( count=_order)[:_cnt] else: imresult = CourseTotal.objects.annotate( cosdistance=ExpressionWrapper(imc, output_field=FloatField()) ).annotate( count=Count('coursedetail')).order_by('cosdistance').filter( count=_order)[:_cnt] print(imresult) seriailizer = CourseSerializer(imresult, many=True) return Response(seriailizer.data)
def annotate_distance(self, point, units=None): lat_1 = point['latitude'] lon_1 = point['longitude'] lat_2 = F('latitude') lon_2 = F('longitude') phi_1 = Radians(lat_1) phi_2 = Radians(lat_2) delta_phi = Radians(lat_2 - lat_1) delta_lambda = Radians(lon_2 - lon_1) a = Power(Sin(delta_phi / 2), 2) +\ Cos(phi_1) * Cos(phi_2) *\ Power(Sin(delta_lambda / 2), 2) c = 2 * ASin(Sqrt(a)) units = DEFAULT_UNITS if units is None else units r = EARTH_RADIUS / METERS_IN[units] d = c * r return self.exclude(latitude=None).exclude(longitude=None).annotate( distance=d)
def calculate_distance1(point1, point2): """ Giving two points using Haversine distance Calculate the distance with the api view class that is used for suggestions endpoint. and filter the view using Django's Database functions. Note: Used only with view to avoid CombinedExpression errors which is generated when using with serailizer Writing it out with Django's Database functions. In contrast to raw SQL, this also gives the advantage of being able to easily append/prepend other ORM filters. """ # Assign the vlues to these called variables to reflect the function's usage current_lat = point1[0] current_long = point1[1] searched_lat = point2[0] searched_long = point2[1] dlat = Radians(current_lat - searched_lat) dlong = Radians(current_long - searched_long) a = (Power(Sin(dlat / 2), 2) + Cos(Radians(searched_lat)) * Cos(Radians(current_lat)) * Power(Sin(dlong / 2), 2)) c = 2 * ATan2(Sqrt(a), Sqrt(1 - a)) d = 6371 * c return d
def is_available(self, ignore_cart=None, ignore_orderpos=None, ignore_voucher_id=None, sales_channel='web', ignore_distancing=False, distance_ignore_cart_id=None): from .orders import Order if self.blocked and sales_channel not in self.event.settings.seating_allow_blocked_seats_for_channel: return False opqs = self.orderposition_set.filter( order__status__in=[Order.STATUS_PENDING, Order.STATUS_PAID], canceled=False) cpqs = self.cartposition_set.filter(expires__gte=now()) vqs = self.vouchers.filter( Q(Q(valid_until__isnull=True) | Q(valid_until__gte=now())) & Q(redeemed__lt=F('max_usages'))) if ignore_cart and ignore_cart is not True: cpqs = cpqs.exclude(pk=ignore_cart.pk) if ignore_orderpos: opqs = opqs.exclude(pk=ignore_orderpos.pk) if ignore_voucher_id: vqs = vqs.exclude(pk=ignore_voucher_id) if opqs.exists() or (ignore_cart is not True and cpqs.exists()) or vqs.exists(): return False if self.event.settings.seating_minimal_distance > 0 and not ignore_distancing: ev = (self.subevent or self.event) qs_annotated = Seat.annotated( ev.seats, self.event_id, self.subevent, ignore_voucher_id=ignore_voucher_id, minimal_distance=0, ignore_order_id=ignore_orderpos.order_id if ignore_orderpos else None, ignore_cart_id=( distance_ignore_cart_id or (ignore_cart.cart_id if ignore_cart else None))) qs_closeby_taken = qs_annotated.annotate(distance=( Power(F('x') - Value(self.x), Value(2), output_field=models.FloatField()) + Power(F('y') - Value(self.y), Value(2), output_field=models.FloatField()) )).exclude(pk=self.pk).filter( Q(has_order=True) | Q(has_cart=True) | Q(has_voucher=True), distance__lt=self.event.settings.seating_minimal_distance**2) if self.event.settings.seating_distance_within_row: qs_closeby_taken = qs_closeby_taken.filter( row_name=self.row_name) if qs_closeby_taken.exists(): return False return True
def test_null(self): IntegerModel.objects.create(big=100) obj = IntegerModel.objects.annotate( null_power_small=Power('small', 'normal'), null_power_normal=Power('normal', 'big'), ).first() self.assertIsNone(obj.null_power_small) self.assertIsNone(obj.null_power_normal)
def annotated(cls, qs, event_id, subevent, ignore_voucher_id=None, minimal_distance=0, ignore_order_id=None, ignore_cart_id=None, distance_only_within_row=False): from . import CartPosition, Order, OrderPosition, Voucher vqs = Voucher.objects.filter( event_id=event_id, subevent=subevent, seat_id=OuterRef('pk'), redeemed__lt=F('max_usages'), ).filter( Q(valid_until__isnull=True) | Q(valid_until__gte=now()) ) if ignore_voucher_id: vqs = vqs.exclude(pk=ignore_voucher_id) opqs = OrderPosition.objects.filter( order__event_id=event_id, subevent=subevent, seat_id=OuterRef('pk'), order__status__in=[Order.STATUS_PENDING, Order.STATUS_PAID] ) if ignore_order_id: opqs = opqs.exclude(order_id=ignore_order_id) cqs = CartPosition.objects.filter( event_id=event_id, subevent=subevent, seat_id=OuterRef('pk'), expires__gte=now() ) if ignore_cart_id: cqs = cqs.exclude(cart_id=ignore_cart_id) qs_annotated = qs.annotate( has_order=Exists( opqs ), has_cart=Exists( cqs ), has_voucher=Exists( vqs ) ) if minimal_distance > 0: # TODO: Is there a more performant implementation on PostgreSQL using # https://www.postgresql.org/docs/8.2/functions-geometry.html ? sq_closeby = qs_annotated.annotate( distance=( Power(F('x') - OuterRef('x'), Value(2), output_field=models.FloatField()) + Power(F('y') - OuterRef('y'), Value(2), output_field=models.FloatField()) ) ).filter( Q(has_order=True) | Q(has_cart=True) | Q(has_voucher=True), distance__lt=minimal_distance ** 2 ) if distance_only_within_row: sq_closeby = sq_closeby.filter(row_name=OuterRef('row_name')) qs_annotated = qs_annotated.annotate(has_closeby_taken=Exists(sq_closeby)) return qs_annotated
def test_null(self): IntegerModel.objects.create(big=100) obj = IntegerModel.objects.annotate( null_power_small=Power("small", "normal"), null_power_normal=Power("normal", "big"), null_power_big=Power("big", "normal"), ).first() self.assertIsNone(obj.null_power_small) self.assertIsNone(obj.null_power_normal) self.assertIsNone(obj.null_power_big)
def get_queryset(self): dlat = Radians(F('branch__location__lati') - self.request.user.lati) dlong = Radians(F('branch__location__lngt') - self.request.user.lngt) a = (Power(Sin(dlat / 2), 2) + Cos(Radians(self.request.user.lati)) * Cos(Radians(F('branch__location__lati'))) * Power(Sin(dlong / 2), 2)) c = 2 * ATan2(Sqrt(a), Sqrt(1 - a)) d = 6371 * c queryset = Product.objects.annotate( distance=d).order_by('distance').filter(distance__lt=50)[:50] return queryset
def nearest_host_within_x_km(self, current_lat, current_long, x_km): """ Greatest circle distance formula """ dlat = Radians(F("latitude") - current_lat, output_field=models.DecimalField()) dlong = Radians(F("longitude") - current_long, output_field=models.DecimalField()) a = Power(Sin(dlat / 2), 2) + Cos(Radians(current_lat, output_field=models.DecimalField())) * Cos( Radians(F("latitude")) ) * Power(Sin(dlong / 2), 2) c = 2 * ATan2(Sqrt(a), Sqrt(1 - a)) d = 6371 * c return self.annotate(distance=d).order_by("distance").filter(distance__lt=x_km)
def test_integer(self): IntegerModel.objects.create(small=-1, normal=20, big=3) obj = IntegerModel.objects.annotate( small_power=Power('small', 'normal'), normal_power=Power('normal', 'big'), big_power=Power('big', 'small'), ).first() self.assertIsInstance(obj.small_power, float) self.assertIsInstance(obj.normal_power, float) self.assertIsInstance(obj.big_power, float) self.assertAlmostEqual(obj.small_power, obj.small**obj.normal) self.assertAlmostEqual(obj.normal_power, obj.normal**obj.big) self.assertAlmostEqual(obj.big_power, obj.big**obj.small)
def _dummyinput(self): print('dummydata를 input합니다.') User.objects.all().delete() models.CourseTotal.objects.all().delete() models.CourseDetail.objects.all().delete() _lat = 35.1874726 _lon = 126.900115 searchrange = (15.9 * (2**(19 - 11.5)) * 2) / 1000 dlat = Radians(F('latitude') - _lat) dlong = Radians(F('longitude') - _lon) a = (Power(Sin(dlat / 2), 2) + Cos(Radians(_lat)) * Cos(Radians(F('latitude'))) * Power(Sin(dlong / 2), 2)) c = 2 * ATan2(Sqrt(a), Sqrt(1 - a)) d = 6371 * c te = models.Location.objects.select_related('category').annotate( distance=d).filter(distance__lt=searchrange).order_by('rank') getlist = list(te.values_list('id', flat=True)) for i in range(10): print(f'{i+1}회 시작') login_email = f'test{i+1}@test.com' age = random.choice(range(20, 50)) random_plate = [] for j in range(10): random_plate.extend([j + 1] * random.randint(1, 5)) testuser = User.objects.create_user(email=login_email, password='******', nickname=f'test{i+1}', gender='남', age=age) for k in range(100): taste = random.choice(random_plate) tour = random.choice(random_plate) activity = random.choice(random_plate) N = random.choice(range(2, 6)) days = random.choice(range(1, 30)) input_days = datetime.date(2020, 10, days) coursetotal = models.CourseTotal.objects.create( user_id=testuser.id, taste=taste, tour=tour, activity=activity, traveldate=input_days) location_sample = random.sample(getlist, N) for ind, location_id in enumerate(location_sample): models.CourseDetail.objects.create( location_id=location_id, order=ind + 1, coursetotal_id=coursetotal.id) print(f'{i+1}회 Done')
def get_queryset(self): active = Count("optionwheel", filter=Q(optionwheel__is_active=True)) completed = Count("optionwheel", filter=Q(optionwheel__is_active=False)) profit = Sum(F("optionwheel__total_profit") * F("optionwheel__quantity"), filter=Q(optionwheel__is_active=False), output_field=fields.DecimalField()) collateral = Sum(F("optionwheel__collatoral") * F("optionwheel__quantity"), filter=Q(optionwheel__is_active=False), output_field=fields.DecimalField()) total_wheels = Sum(F("optionwheel__quantity"), filter=Q(optionwheel__is_active=False)) total_days_active_weighted_by_collateral = Sum( F("optionwheel__total_days_active") * F("optionwheel__quantity") * F("optionwheel__collatoral"), filter=Q(optionwheel__is_active=False)) average_days = Cast(total_days_active_weighted_by_collateral, fields.FloatField()) / Cast( collateral, fields.FloatField()) annualized_rate_of_return = Power( 1 + profit / collateral, BUSINESS_DAYS_IN_YEAR / Coalesce(average_days, 252)) users = User.objects.annotate( active=active, completed=completed, profit=Round(100 * Coalesce(profit, 0)), collateral=Round(100 * Coalesce(collateral, 0)), return_percentage=Coalesce(profit / collateral, 0), total_wheels=Coalesce(total_wheels, 0), average_days=Coalesce(average_days, 0), annualized_rate_of_return=Coalesce(annualized_rate_of_return, 0), ) return users
def get_queryset(self): """ Optionally restricts the returned purchases to a given user, by filtering against a `username` query parameter in the URL. """ # Add upvote count. queryset = Submission.objects.annotate(upvote_count=Count("upvote")) user_pk = self.request.user.pk if self.request.user.is_authenticated else None if self.request.user.is_authenticated: queryset = queryset.annotate(upvoted=Exists( Upvote.objects.filter( user=self.request.user.pk, submission=OuterRef("pk"), ))) else: queryset = queryset.annotate(upvoted=Value(False, BooleanField())) # Filter by username if required. username = self.request.query_params.get("username", None) if username is not None: queryset = queryset.filter(user__github_id=username) ordering = self.request.query_params.get("ordering", None) if ordering == "hot": # Hotness algorithm: https://medium.com/hacking-and-gonzo/how-hacker-news-ranking-algorithm-works-1d9b0cf2c08d gravity = 1.8 class Age(Func): template = "(EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM %(expressions)s))/3600" queryset = queryset.annotate(hotness=ExpressionWrapper( F("upvote_count") / Power(Age("created_at", output_field=FloatField()), gravity), output_field=FloatField(), )) queryset = queryset.order_by("-hotness") elif ordering == "top": queryset = queryset.order_by("-upvote_count") elif ordering == "new": queryset = queryset.order_by("-created_at") return queryset
def disagreements_for_user(user_preferences, top=20, threshold_mse_normed=0.12): """Get top disagreements between the model and the representative.""" qs = ExpertRating.objects.all() qs = qs.filter(user=user_preferences) qs = qs.filter(video_1__videorating__user=user_preferences, video_2__videorating__user=user_preferences) # adding video scores annotations = {} for vid in [1, 2]: for f in VIDEO_FIELDS: annotations[f'video_{vid}_score_{f}'] = F(f'video_{vid}__videorating__{f}') qs = qs.annotate(**annotations) # computing model pairwise rating annotations = {} for f in VIDEO_FIELDS: # 1.0 / (1 + Math.exp(modelV1[feature] - modelV2[feature])) one = Value(1., output_field=FloatField()) def exp(x): return Func(x, function='EXP') delta = F(f'video_1_score_{f}') - F(f'video_2_score_{f}') delta = one + exp(delta) delta = one / delta annotations[f'model_{f}_01'] = delta qs = qs.annotate(**annotations) # scaling up/down annotations = {} for f in VIDEO_FIELDS: annotations[f'model_{f}'] = Value(MAX_VALUE, output_field=FloatField())\ * F(f'model_{f}_01') annotations[f'{f}_01'] = F(f) / Value(MAX_VALUE, output_field=FloatField()) qs = qs.annotate(**annotations) # computing disagreement and thresholding... annotations = {} for f in VIDEO_FIELDS: annotations[f'mse_{f}_01'] = Power(F(f'model_{f}_01') - F(f'{f}_01'), Value(2.0, output_field=FloatField())) qs = qs.annotate(**annotations) # sorting over all fields and returning top 20 qs_fs = [] tot_count = 0 for f in VIDEO_FIELDS: qs_f = qs.filter(**{f'mse_{f}_01__gte': threshold_mse_normed}) tot_count += qs_f.count() qs_f = qs_f.order_by(f'-mse_{f}_01') qs_f = qs_f[:top] qs_f = qs_f.annotate(mse_01=F(f'mse_{f}_01'), feature=Value(f, output_field=CharField()), model_score=F(f'model_{f}'), rating_score=F(f)) qs_fs.append(list(qs_f)) qs_f_all = [x for qs in qs_fs for x in qs] qs_f_all = sorted(qs_f_all, key=lambda x: -x.mse_01) # return qs_f_all qs_f_all = qs_f_all[:top] for item in qs_f_all: item.video_1__video_id = item.video_1.video_id item.video_2__video_id = item.video_2.video_id return {'count': tot_count, 'results': qs_f_all}
def test_decimal(self): DecimalModel.objects.create(n1=Decimal('1.0'), n2=Decimal('-0.6')) obj = DecimalModel.objects.annotate(n_power=Power('n1', 'n2')).first() self.assertIsInstance(obj.n_power, Decimal) self.assertAlmostEqual(obj.n_power, Decimal(obj.n1**obj.n2))
def is_available(self, ignore_cart=None, ignore_orderpos=None, ignore_voucher_id=None, sales_channel='web', ignore_distancing=False, distance_ignore_cart_id=None): from .orders import Order if self.blocked and sales_channel not in self.event.settings.seating_allow_blocked_seats_for_channel: return False opqs = self.orderposition_set.filter( order__status__in=[Order.STATUS_PENDING, Order.STATUS_PAID], canceled=False) cpqs = self.cartposition_set.filter(expires__gte=now()) vqs = self.vouchers.filter( Q(Q(valid_until__isnull=True) | Q(valid_until__gte=now())) & Q(redeemed__lt=F('max_usages'))) if ignore_cart and ignore_cart is not True: cpqs = cpqs.exclude(pk=ignore_cart.pk) if ignore_orderpos: opqs = opqs.exclude(pk=ignore_orderpos.pk) if ignore_voucher_id: vqs = vqs.exclude(pk=ignore_voucher_id) if opqs.exists() or (ignore_cart is not True and cpqs.exists()) or vqs.exists(): return False if self.event.settings.seating_minimal_distance > 0 and not ignore_distancing: ev = (self.subevent or self.event) qs_annotated = Seat.annotated( ev.seats, self.event_id, self.subevent, ignore_voucher_id=ignore_voucher_id, minimal_distance=0, ignore_order_id=ignore_orderpos.order_id if ignore_orderpos else None, ignore_cart_id=(distance_ignore_cart_id or (ignore_cart.cart_id if ignore_cart and ignore_cart is not True else None))) q = Q(has_order=True) | Q(has_voucher=True) if ignore_cart is not True: q |= Q(has_cart=True) # The following looks like it makes no sense. Why wouldn't we just use ``Value(self.x)``, we already now # the value? The reason is that x and y are floating point values generated from our JSON files. As it turns # out, PostgreSQL MIGHT store floating point values with a different precision based on the underlying system # architecture. So if we generate e.g. 670.247128887222289 from the JSON file and store it to the database, # PostgreSQL will store it as 670.247128887222289 internally. However if we query it again, we only get # 670.247128887222 back. But if we do calculations with a field in PostgreSQL itself, it uses the full # precision for the calculation. # We don't actually care about the results with this precision, but we care that the results from this # function are exactly the same as from event.free_seats(), so we do this subquery trick to deal with # PostgreSQL's internal values in both cases. # In the long run, we probably just want to round the numbers on insert... # See also https://www.postgresql.org/docs/11/runtime-config-client.html#GUC-EXTRA-FLOAT-DIGITS self_x = Subquery(Seat.objects.filter(pk=self.pk).values('x')) self_y = Subquery(Seat.objects.filter(pk=self.pk).values('y')) qs_closeby_taken = qs_annotated.annotate(distance=(Power( F('x') - self_x, Value(2), output_field=models.FloatField() ) + Power( F('y') - self_y, Value(2), output_field=models.FloatField() ))).exclude(pk=self.pk).filter( q, distance__lt=self.event.settings.seating_minimal_distance**2) if self.event.settings.seating_distance_within_row: qs_closeby_taken = qs_closeby_taken.filter( row_name=self.row_name) if qs_closeby_taken.exists(): return False return True
def test_float(self): FloatModel.objects.create(f1=2.3, f2=1.1) obj = FloatModel.objects.annotate(f_power=Power('f1', 'f2')).first() self.assertIsInstance(obj.f_power, float) self.assertAlmostEqual(obj.f_power, obj.f1**obj.f2)
def get_queryset(self): region = self.request.query_params.get("region", "") mapy = self.request.query_params.get("mapy", "") mapx = self.request.query_params.get("mapx", "") name = self.request.query_params.get("name", "") # 0. 아무것도 선택 X if region == "전체" and len(mapy) == 0 and len(mapx) == 0 and len( name) == 0: queryset = models.TourSpot.objects.all().order_by("-readcount") return queryset # 1. 지역만 elif region is not None and len(mapy) == 0 and len(mapx) == 0 and len( name) == 0: queryset = models.TourSpot.objects.all().filter( addr1__contains=region).order_by("-readcount") return queryset # 2. 장바구니(위도, 경도)만 elif region == "전체" and mapy is not None and mapx is not None and len( name) == 0: # 위도 경도 계산 -> 가까운 순 부터 print("22222222") dlat = Radians(F('mapy') - float(mapy)) dlong = Radians(F('mapx') - float(mapx)) a = (Power(Sin(dlat / 2), 2) + Cos(Radians(float(mapy))) * Cos(Radians(F('mapy'))) * Power(Sin(dlong / 2), 2)) c = 2 * ATan2(Sqrt(a), Sqrt(1 - a)) d = 6371 * c queryset = models.TourSpot.objects.annotate( distance=d).exclude(Q(mapy=mapy) & Q(mapx=mapx)).order_by('distance') return queryset # 3. 검색어만 elif region == "전체" and len(mapy) == 0 and len( mapx) == 0 and name is not None: queryset = models.TourSpot.objects.all().filter( Q(title__contains=name) | Q(addr1__contains=name)).order_by("-readcount") return queryset # 4. 지역, 장바구니(위도, 경도) 선택했을 때 elif region is not None and mapy is not None and mapx is not None and len( name) == 0: print("22222222") dlat = Radians(F('mapy') - float(mapy)) dlong = Radians(F('mapx') - float(mapx)) a = (Power(Sin(dlat / 2), 2) + Cos(Radians(float(mapy))) * Cos(Radians(F('mapy'))) * Power(Sin(dlong / 2), 2)) c = 2 * ATan2(Sqrt(a), Sqrt(1 - a)) d = 6371 * c queryset = models.TourSpot.objects.annotate(distance=d).filter( addr1__contains=region).exclude(Q(mapy=mapy) & Q( mapx=mapx)).order_by('distance') return queryset # 5. 지역, 검색어 선택했을 때 elif region is not None and len(mapy) == 0 and len( mapx) == 0 and name is not None: queryset = models.TourSpot.objects.filter( Q(addr1__contains=region), (Q(title__contains=name) | Q(addr1__contains=name))) return queryset # 6. 장바구니(위도, 경도), 검색어 선택했을 때 elif region == "전체" and mapy is not None and mapx is not None and name is not None: print("22222222") dlat = Radians(F('mapy') - float(mapy)) dlong = Radians(F('mapx') - float(mapx)) a = (Power(Sin(dlat / 2), 2) + Cos(Radians(float(mapy))) * Cos(Radians(F('mapy'))) * Power(Sin(dlong / 2), 2)) c = 2 * ATan2(Sqrt(a), Sqrt(1 - a)) d = 6371 * c queryset = models.TourSpot.objects.annotate(distance=d).filter( Q(title__contains=name) | Q(addr1__contains=name)).exclude( Q(mapy=mapy) & Q(mapx=mapx)).order_by('distance') return queryset # 7. 지역, 장바구니(위도, 경도), 검색어 선택했을 때 elif region is not None and mapy is not None and mapx is not None and name is not None: print("22222222") dlat = Radians(F('mapy') - float(mapy)) dlong = Radians(F('mapx') - float(mapx)) a = (Power(Sin(dlat / 2), 2) + Cos(Radians(float(mapy))) * Cos(Radians(F('mapy'))) * Power(Sin(dlong / 2), 2)) c = 2 * ATan2(Sqrt(a), Sqrt(1 - a)) d = 6371 * c queryset = models.TourSpot.objects.annotate(distance=d).filter( Q(addr1__contains=region), (Q(title__contains=name) | Q(addr1__contains=name) )).exclude(Q(mapy=mapy) & Q(mapx=mapx)).order_by('distance') return queryset else: queryset = models.TourSpot.objects.all().order_by("id") return queryset
def get_queryset(self): region = self.request.query_params.get("region", "") lat = self.request.query_params.get("lat", "") lon = self.request.query_params.get("lon", "") name = self.request.query_params.get("name", "") # 0. 아무것도 선택 X if region == "전체" and len(lat) == 0 and len(lon) == 0 and len( name) == 0: queryset = models.Store.objects.all().order_by("id") return queryset # 1. 지역만 elif region is not None and len(lat) == 0 and len(lon) == 0 and len( name) == 0: queryset = models.Store.objects.all().filter( address__contains=region).order_by("id") return queryset # 2. 장바구니(위도, 경도)만 elif region == "전체" and lat is not None and lon is not None and len( name) == 0: # 위도 경도 계산 -> 가까운 순 부터 print("22222222") dlat = Radians(F('latitude') - float(lat)) dlong = Radians(F('longitude') - float(lon)) a = (Power(Sin(dlat / 2), 2) + Cos(Radians(float(lat))) * Cos(Radians(F('latitude'))) * Power(Sin(dlong / 2), 2)) c = 2 * ATan2(Sqrt(a), Sqrt(1 - a)) d = 6371 * c queryset = models.Store.objects.annotate( distance=d).exclude(Q(latitude=lat) & Q(longitude=lon)).order_by('distance') return queryset # 3. 검색어만 elif region == "전체" and len(lat) == 0 and len( lon) == 0 and name is not None: queryset = models.Store.objects.all().filter( store_name__contains=name).order_by("id") return queryset # 4. 지역, 장바구니(위도, 경도) 선택했을 때 elif region is not None and lat is not None and lon is not None and len( name) == 0: print("22222222") dlat = Radians(F('latitude') - float(lat)) dlong = Radians(F('longitude') - float(lon)) a = (Power(Sin(dlat / 2), 2) + Cos(Radians(float(lat))) * Cos(Radians(F('latitude'))) * Power(Sin(dlong / 2), 2)) c = 2 * ATan2(Sqrt(a), Sqrt(1 - a)) d = 6371 * c queryset = models.Store.objects.annotate(distance=d).filter( address__contains=region).exclude( Q(latitude=lat) & Q(longitude=lon)).order_by('distance') return queryset # 5. 지역, 검색어 선택했을 때 elif region is not None and len(lat) == 0 and len( lon) == 0 and name is not None: queryset = models.Store.objects.filter(address__contains=region, store_name__contains=name) return queryset # 6. 장바구니(위도, 경도), 검색어 선택했을 때 elif region == "전체" and lat is not None and lon is not None and name is not None: dlat = Radians(F('latitude') - float(lat)) dlong = Radians(F('longitude') - float(lon)) a = (Power(Sin(dlat / 2), 2) + Cos(Radians(float(lat))) * Cos(Radians(F('latitude'))) * Power(Sin(dlong / 2), 2)) c = 2 * ATan2(Sqrt(a), Sqrt(1 - a)) d = 6371 * c queryset = models.Store.objects.annotate(distance=d).filter( store_name__contains=name).exclude( Q(latitude=lat) & Q(longitude=lon)).order_by('distance') return queryset # 7. 지역, 장바구니(위도, 경도), 검색어 선택했을 때 elif region is not None and lat is not None and lon is not None and name is not None: dlat = Radians(F('latitude') - float(lat)) dlong = Radians(F('longitude') - float(lon)) a = (Power(Sin(dlat / 2), 2) + Cos(Radians(float(lat))) * Cos(Radians(F('latitude'))) * Power(Sin(dlong / 2), 2)) c = 2 * ATan2(Sqrt(a), Sqrt(1 - a)) d = 6371 * c queryset = models.Store.objects.annotate(distance=d).filter( address__contains=region, store_name__contains=name).exclude( Q(latitude=lat) & Q(longitude=lon)).order_by('distance') return queryset else: queryset = models.Store.objects.all().order_by("id") return queryset
def test_decimal(self): DecimalModel.objects.create(n1=Decimal("1.0"), n2=Decimal("-0.6")) obj = DecimalModel.objects.annotate(n_power=Power("n1", "n2")).first() self.assertIsInstance(obj.n_power, Decimal) self.assertAlmostEqual(obj.n_power, Decimal(obj.n1**obj.n2))
def browse(request): """ To browse Bookmarks objects for given params and sorting order. @return Response of bookmarks objects list. """ params = request.query_params sort_by = params.get('sort_by', None) #Default sorting order if not sort_by: sort_by = 'customer_id' #Customer customer_id = params.get('customer_id', None) q_customer = Q() if customer_id: #Query Expression to get Bookmarks according to customer id. q_customer = Q(customer_id=customer_id) #Source name source_name = params.get('source_name', None) q_source_name = Q() if source_name: #Query Expression to get Bookmarks according to source name. q_source_name = Q(source_name__icontains=source_name) #Title title = params.get('title', None) q_title = Q() if title: #Query Expression to get Bookmarks according to title. q_title = Q(title__icontains=title) #Geo Location current_lat = params.get('lat', None) current_long = params.get('long', None) radius = params.get('radius', None) q_location = Q() if all(v is not None for v in [current_long, current_lat, radius]): """ Implementation of haversine formula to get customers whose geoloactions lies within the given radius taking the request param points as center location. a = sin²(Δφ/2) + cos φ1 ⋅ cos φ2 ⋅ sin²(Δλ/2) c = 2 ⋅ atan2( √a, √(1−a) ) d = 6371 #Earth radius """ current_lat = float(current_lat) current_long = float(current_long) dlat = Radians(F('latitude') - current_lat) dlong = Radians(F('longitude') - current_long) a = (Power(Sin(dlat / 2), 2) + Cos(Radians(current_lat)) * Cos(Radians(F('latitude'))) * Power(Sin(dlong / 2), 2)) c = 2 * ATan2(Sqrt(a), Sqrt(1 - a)) d = 6371 * c customers = Customer.objects.filter()\ .annotate(distance=d)\ .filter(distance__lt=radius) #Query expression for all customers lies within the radius to filter Bookmark model. q_location = Q(customer__in=customers) #Date Range start_date = params.get('start_date', None) end_date = params.get('end_date', None) q_date_range = Q() if start_date is not None and end_date is not None: start_date = datetime.strptime(start_date, "%Y-%m-%d") end_date = datetime.strptime(end_date, "%Y-%m-%d") end_date = end_date + timedelta(days=1) #Query Expression to get Bookmarks according to customer id. q_date_range = Q(created_at__range=(start_date, end_date)) #Filter and sort Bookmark model for the given query params. bookmarks = Bookmark.objects.filter(q_customer | q_source_name | q_title | q_date_range | q_location).order_by(sort_by) #Bookmark serializer serializer = BookmarkListSerializers(bookmarks, many=True) return Response(serializer.data)