Electronics & Programming

develissimo

Open Source electronics development and programming

  • You are not logged in.

#1 June 10, 2010 14:56:39

i.
Registered: 2009-11-02
Reputation: +  0  -
Profile   Send e-mail  

Simplification of a DB "query"


Hi all,

I'm writing a django app and need help with filtering results from DB.

There is a conference room and it's usage is stored in django model.
There is a field "start_time" (model.TimeField); no "end_time". When I
query the table with a time range (i.e. 3pm - 5pm) I would like to get
not only events that start within given time range, but also the one
currently running (the last one that started before 3pm).

The only solution I have in my mind at the moment is to have two
queries: one would return events starting within the range, other
would retrieve events before that, would sort them descending by time
and I'd get just the first row. Then I'd try to join these two
results.

Although the above algorithm would/should work, I was wondering if
there was more elegant way to this?

Thanks!

--
You received this message because you are subscribed to the Google Groups
"Django users" group.
To post to this group, send email to django-us...@googlegroups.com.
To unsubscribe from this group, send email to
django-users+unsubscr...@googlegroups.com.
For more options, visit this group athttp://groups.google.com/group/django-users?hl=en.

Offline

#2 June 10, 2010 15:00:45

Kenneth G.
Registered: 2009-11-02
Reputation: +  0  -
Profile   Send e-mail  

Simplification of a DB "query"


On Thursday 10 June 2010 19:26:33 illuminated wrote:
> Although the above algorithm would/should work, I was wondering if
> there was more elegant way to this?
>

without an endtime, it is going to be difficult
--
Regards
Kenneth Gonsalves
Senior Associate
NRC-FOSS at AU-KBC

--
You received this message because you are subscribed to the Google Groups
"Django users" group.
To post to this group, send email to django-us...@googlegroups.com.
To unsubscribe from this group, send email to
django-users+unsubscr...@googlegroups.com.
For more options, visit this group athttp://groups.google.com/group/django-users?hl=en.

Offline

#3 June 11, 2010 21:28:23

Cesar D.
Registered: 2009-11-02
Reputation: +  0  -
Profile   Send e-mail  

Simplification of a DB "query"


I'm not sure how to do directly in Django ORM, but if you use plain
SQL queries, you could do try:

select * from conference_room cr1
where cr1.start_time >=
(select max(start_time) from conference_room cr2 where cr2.start_time
< :yout_filter_time)
and cr1.start_time < :your_filter_time

the "select max" gives you the row immediately before your time
range.

if you don't want to use a plain SQL approach, you will have to do two
accesses to the DB. then, there are several way to do so, like:

ConferenceRoom.objects.filter(start_time__gt=ConferenceRoom.objects.filter(start_time__lt=your_start_time).aggregate(Max('start_time'))
.get().start_time).filter(start_time__lt=your_end_time)

sorry. I'm not sure if I typed correctly, but it's the same idea of
the SQL statement I mentioned before:
1. find the maximum date before your the start date of your time frame
2. find all events after that date (1) including it (greater and equal
to) and before your end date

hope it helps,

regards,

---------
On 10 jun, 10:56, illuminated <petrovic.mi...@gmail.com> wrote:
> Hi all,
>
> I'm writing a django app and need help with filtering results from DB.
>
> There is a conference room and it's usage is stored in django model.
> There is a field "start_time" (model.TimeField); no "end_time". When I
> query the table with a time range (i.e. 3pm - 5pm) I would like to get
> not only events that start within given time range, but also the one
> currently running (the last one that started before 3pm).
>
> The only solution I have in my mind at the moment is to have two
> queries: one would return events starting within the range, other
> would retrieve events before that, would sort them descending by time
> and I'd get just the first row. Then I'd try to join these two
> results.
>
> Although the above algorithm would/should work, I was wondering if
> there was more elegant way to this?
>
> Thanks!

--
You received this message because you are subscribed to the Google Groups
"Django users" group.
To post to this group, send email to django-us...@googlegroups.com.
To unsubscribe from this group, send email to
django-users+unsubscr...@googlegroups.com.
For more options, visit this group athttp://groups.google.com/group/django-users?hl=en.

Offline

#4 June 11, 2010 21:53:09

Dan H.
Registered: 2009-11-02
Reputation: +  0  -
Profile   Send e-mail  

Simplification of a DB "query"


This is totally off the top of my head and may not compile or work :)
But something like this might be what you're looking for:

# Example Model
class MyModel(models.Model):
start_time = models.TimeField()

# Example query
from django.db.models import Q
from django.db.model import Max

# Get the maximum start time before the specified start time (3pm)
result =
MyModel.objects.filter(start_time__lt='15:00:00').aggregate(Max(start_time))

# Build two Q objects to represent the two cases we want
# 1.) The last meeting before the specified time (3pm) - we got the
last start time via the aggregation
latest_meeting = Q(start_time=result)

# 2.) All meetings between 3pm and 5pm
after_3_and_before_5 = Q(start_time__gte='15:00:00',
start_time__lte='17:00:00')

# Filter the objects by ORing the two Q objects together giving us:
# "show me all objects that are the last meeting before 3 or start
betweeen 3pm and 5pm"
MyModel.objects.filter(latest_meeting | after_3_and_before_5)

Not sure if this is what you're looking for or if it's even right :)

Dan Harris
dih0...@gmail.com

On Jun 11, 4:28 pm, Cesar Devera <cesardev...@gmail.com> wrote:
> I'm not sure how to do directly in Django ORM, but if you use plain
> SQL queries, you could do try:
>
> select * from conference_room cr1
> where cr1.start_time >=
>         (select max(start_time) from conference_room cr2 where cr2.start_time
> < :yout_filter_time)
> and   cr1.start_time < :your_filter_time
>
> the "select max" gives you the row immediately before your time
> range.
>
> if you don't want to use a plain SQL approach, you will have to do two
> accesses to the DB. then, there are several way to do so, like:
>
> ConferenceRoom.objects.filter(start_time__gt=ConferenceRoom.objects.filter(
> start_time__lt=your_start_time).aggregate(Max('start_time'))
> .get().start_time).filter(start_time__lt=your_end_time)
>
> sorry. I'm not sure if I typed correctly, but it's the same idea of
> the SQL statement I mentioned before:
> 1. find the maximum date before your the start date of your time frame
> 2. find all events after that date (1) including it (greater and equal
> to) and before your end date
>
> hope it helps,
>
> regards,
>
> ---------
> On 10 jun, 10:56, illuminated <petrovic.mi...@gmail.com> wrote:
>
>
>
> > Hi all,
>
> > I'm writing a django app and need help with filtering results from DB.
>
> > There is a conference room and it's usage is stored in django model.
> > There is a field "start_time" (model.TimeField); no "end_time". When I
> > query the table with a time range (i.e. 3pm - 5pm) I would like to get
> > not only events that start within given time range, but also the one
> > currently running (the last one that started before 3pm).
>
> > The only solution I have in my mind at the moment is to have two
> > queries: one would return events starting within the range, other
> > would retrieve events before that, would sort them descending by time
> > and I'd get just the first row. Then I'd try to join these two
> > results.
>
> > Although the above algorithm would/should work, I was wondering if
> > there was more elegant way to this?
>
> > Thanks!

--
You received this message because you are subscribed to the Google Groups
"Django users" group.
To post to this group, send email to django-us...@googlegroups.com.
To unsubscribe from this group, send email to
django-users+unsubscr...@googlegroups.com.
For more options, visit this group athttp://groups.google.com/group/django-users?hl=en.

Offline

Board footer

Moderator control

Enjoy the 19th of October
PoweredBy

The Forums are managed by develissimo stuff members, if you find any issues or misplaced content please help us to fix it. Thank you! Tell us via Contact Options
Leave a Message
Welcome to Develissimo Live Support