Question 20520 in Mysql Question posted in
The official MySQL documentation can be found here.

Use objects from one django model to filter objects from another django objects – Mysql

I’m doing my studying project. I have 2 models:

class RoutePoint(geo_models.Model):
    vehicle = models.ForeignKey(Vehicle, on_delete=models.CASCADE, null=False, blank=False,
                                related_name='routepoints')
    point = geo_models.PointField()
    datetime = models.DateTimeField()


class Travel(models.Model):
    vehicle = models.ForeignKey(Vehicle, on_delete=models.DO_NOTHING, null=False, blank=False,
                                related_name='travels')
    begin = models.DateTimeField()
    end = models.DateTimeField()

First one contains geopoints for vehicle and date and time for vehicle getting there.
Second one countains the begining and the ending of the rides for vehicles.

I’m having some curl request with two fields – start_date and end_date, let’s call it some kind request for report.

So, at first I need to get something like this:

SELECT vehicle_id, begin, end
FROM park_travel
WHERE begin > "2022-12-30" AND end < "2023-01-06"

I’ll call it result.

And after that i need to get something like:

SELECT vehicle_id, point, datetime
FROM park_routepoint
WHERE vehicle_id = result.vehicle_id
  AND datetime > result.begin
  AND datetime < result.end

And I have to do it without raw sql…

Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    I did it like this:

        vehicles_with_dates = travels.values('vehicle_id').annotate(min_dt=Min('begin'), max_dt=Max('end'))
    route_points = RoutePoint.objects.filter(
        vehicle_id__in=vehicles_with_dates.values('vehicle_id'),
        datetime__gt=vehicles_with_dates.values('min_dt'),
        datetime__lt=vehicles_with_dates.values('max_dt')
    )

    It worked perfectly fine, thanks to Iqbal Hussain.


  2. 0

    You can do this in one query using double underscore notation and F expressions:

    from django.db.models import F

route_points = RoutePoint.objects.filter(
    vehicle__travel__begin__lt=F("datetime"),
    vehicle__travel__end__gt=F("datetime")
)

    then simply iterate over the route_points queryset:

    for route_point in route_points:
    print(route_point.vehicle, route_point.point, route_point.datetime)
    Login or Signup to reply.
  3. 0

    You can filter the Travel Queryset:

    start_date = '2022-12-30'
end_date = '2023-01-06'
travel_qs = Travel.objects.filter(begin__gt=start_date, 
end__lt=end_date).values('vehicle_id', 'begin', 'end')

    Now you can use travel_qs to retrieve the Points from RoutePoint:

    route_point_qs = RoutePoint.objects.filter(vehicle_id__in=travel_qs.values('vehicle_id'),
                                        datetime__gt=travel_qs.values('begin'),
                                        datetime__lt=travel_qs.values('end')).values('vehicle_id', 'point', 'datetime')
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search