Re: Four Hop, Two Criteria Query

On Thu, May 27, 2010 at 2:03 AM, Lee Hinde <leehinde@gmail.com> wrote:

Hi;

I have this model design:

http://dpaste.com/hold/199818/

class Weather(models.Model):

    weather_date = models.DateField(auto_now_add=True,db_index=True)

    zip_code = models.CharField(max_length=20,db_index=True )

    icon_data = models.CharField(max_length=80)

class Store(models.Model):

    name = models.CharField(max_length=50,)

    zip_code = models.CharField(max_length=20)

        

class UserProfile(models.Model):

    store = models.ForeignKey(Store)

    user = models.OneToOneField(User, unique=True)

    role = models.CharField(max_length=80,db_index=True, choices=ROLE_CHOICES)

class Note(models.Model):

    created_by = models.ForeignKey(UserProfile,editable=False)

    created = models.DateField(auto_now_add=True)

For a given Note, I'd like to know the weather on the day and at the location for the user who created the note. (the weather data is entered via a custom management command.)

The SQL that I came up with is here:

http://dpaste.com/hold/199817/

select icon_data, weather_date from intranet_weather w

    where w.id in 

 (

 select w.id 

    from intranet_weather w

  left outer join

    intranet_store s on s.zip_code = w.zip_code 

   left outer join

      intranet_userprofile up on up.store_id = s.id

   left outer join

      intranet_note on intranet_note.created_by_id = up.user_id

  where intranet_note.id = 1

  )

   and w.id in

   (

 select w.id 

    from intranet_weather w

left outer join

  intranet_note n on n.created = w.weather_date

     where n.id = 1  

    )

I can't think of a way to express this in the ORM. This seems like an obvious case for "select()", but  I haven't had any luck with the various combinations of pure select and the new paramater options.

Any help would be appreciated.

 - Lee

Ha; Raw is my friend. Thanks Django 1.2 :-)

(but I'm still curious if there's a better way to do the query and or model the case.)

 

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