Re: A tricky query in one to many relationship - atleast for me:)

[Web Architect]

Hi Michal,

Thanks for the solution. I tried the solution in a different way:

A.objects.annotate( 
max_date_created=Max('b__date_created')).filter(b__date_created=F('max_date_created'),
 
b__text='ABCD') 

Hope the above amounts to the same thing as you have suggested.

Thanks.


On Wednesday, August 31, 2016 at 1:48:38 PM UTC+5:30, Michal Petrucha wrote:
>
> On Tue, Aug 30, 2016 at 11:46:14PM -0700, Web Architect wrote: 
> > Hi Erik, 
> > 
> > I tried your solution but there are some issues: 
> > 
> > .filter(date_created=Max('a__b__date_created'))  - this is throwing 
> error 
> > saying not proper use of group function. 
> > 
> > If I remove the above, the result isn't correct where when I go through 
> > each 'a' in the result, associated latest B.text isn't always 'ABCD' - 
> if 
> > there are multiple instances of B associated with an instance of A and 
> one 
> > of the instances of B has text='ABCD' (might not be the latest 
> > date_created), that instance of B is also there in the query result. 
>
> Hmm, I haven't actually tried to run any code, but I'd try reordering 
> some of the method calls, and using an explcit field alias for the 
> annotation, which should let you then use an F() expression in 
> subsequent filters; something like this: 
>
> A.objects.filter( 
> b__in=B.objects 
> .annotate(max_date_created=Max('a__b__date_created')) 
> .filter(date_created=F('max_date_created'), text='ABCD') 
> ) 
>
> Good luck, 
>
> Michal 
>

-- 
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-users+unsubscr...@googlegroups.com.
To post to this group, send email to django-users@googlegroups.com.
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-users/88d20adc-6375-425c-88a5-870d0244471f%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Re: A tricky query in one to many relationship - atleast for me:)

[Michal Petrucha]

On Tue, Aug 30, 2016 at 11:46:14PM -0700, Web Architect wrote:
> Hi Erik,
> 
> I tried your solution but there are some issues:
> 
> .filter(date_created=Max('a__b__date_created'))  - this is throwing error 
> saying not proper use of group function.
> 
> If I remove the above, the result isn't correct where when I go through 
> each 'a' in the result, associated latest B.text isn't always 'ABCD' - if 
> there are multiple instances of B associated with an instance of A and one 
> of the instances of B has text='ABCD' (might not be the latest 
> date_created), that instance of B is also there in the query result.

Hmm, I haven't actually tried to run any code, but I'd try reordering
some of the method calls, and using an explcit field alias for the
annotation, which should let you then use an F() expression in
subsequent filters; something like this:

A.objects.filter( 
b__in=B.objects 
.annotate(max_date_created=Max('a__b__date_created')) 
.filter(date_created=F('max_date_created'), text='ABCD') 
) 

Good luck,

Michal

-- 
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-users+unsubscr...@googlegroups.com.
To post to this group, send email to django-users@googlegroups.com.
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-users/20160831081747.GF6601%40konk.org.
For more options, visit https://groups.google.com/d/optout.


signature.asc
Description: Digital signature

Re: A tricky query in one to many relationship - atleast for me:)

[Web Architect]

Hi Erik,

I tried your solution but there are some issues:

.filter(date_created=Max('a__b__date_created'))  - this is throwing error 
saying not proper use of group function.

If I remove the above, the result isn't correct where when I go through 
each 'a' in the result, associated latest B.text isn't always 'ABCD' - if 
there are multiple instances of B associated with an instance of A and one 
of the instances of B has text='ABCD' (might not be the latest 
date_created), that instance of B is also there in the query result.

Thanks.

On Tuesday, August 30, 2016 at 3:59:20 PM UTC+5:30, Erik Cederstrand wrote:
>
>
> > Den 30. aug. 2016 kl. 11.20 skrev Erik Cederstrand <
> erik+...@cederstrand.dk >: 
> > 
> > I'm not even sure that's possible to express in SQL, but it would 
> probably be quite convoluted if it is. Here's an easier-to-understand 
> solution: 
> > 
> > res = set() 
> > for b in 
> B.objects.all().select_related('a').annotate(Max('date_created')): 
> >if b.date_created != b.date_created__max: 
> >continue 
> >if b.text != 'ABCD': 
> >continue 
> >res.add(a) 
>
> I did some more experimenting. I think this actually does what you want: 
>
> res = [ 
> b.a for b in B.objects 
> .filter(date_created=Max('a__b__date_created')) 
> .annotate(Max('a__b__date_created')) 
> .filter(text='ABCD') 
> .select_related('a') 
> ] 
>
> which you can rewrite as: 
>
> A.objects.filter( 
> b__in=B.objects 
> .filter(date_created=Max('a__b__date_created')) 
> .annotate(Max('a__b__date_created')) 
> .filter(text='ABCD') 
> ) 
>
> Erik

-- 
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-users+unsubscr...@googlegroups.com.
To post to this group, send email to django-users@googlegroups.com.
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-users/13a40885-4cce-46af-ba50-368942608c37%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Re: A tricky query in one to many relationship - atleast for me:)

[Web Architect]

Thanks Erik. Will try out the solution you mentioned.

On Tuesday, August 30, 2016 at 3:59:20 PM UTC+5:30, Erik Cederstrand wrote:
>
>
> > Den 30. aug. 2016 kl. 11.20 skrev Erik Cederstrand <
> erik+...@cederstrand.dk >: 
> > 
> > I'm not even sure that's possible to express in SQL, but it would 
> probably be quite convoluted if it is. Here's an easier-to-understand 
> solution: 
> > 
> > res = set() 
> > for b in 
> B.objects.all().select_related('a').annotate(Max('date_created')): 
> >if b.date_created != b.date_created__max: 
> >continue 
> >if b.text != 'ABCD': 
> >continue 
> >res.add(a) 
>
> I did some more experimenting. I think this actually does what you want: 
>
> res = [ 
> b.a for b in B.objects 
> .filter(date_created=Max('a__b__date_created')) 
> .annotate(Max('a__b__date_created')) 
> .filter(text='ABCD') 
> .select_related('a') 
> ] 
>
> which you can rewrite as: 
>
> A.objects.filter( 
> b__in=B.objects 
> .filter(date_created=Max('a__b__date_created')) 
> .annotate(Max('a__b__date_created')) 
> .filter(text='ABCD') 
> ) 
>
> Erik

-- 
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-users+unsubscr...@googlegroups.com.
To post to this group, send email to django-users@googlegroups.com.
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-users/2dc0eaa1-1c80-454d-9e3d-9ba4cbcb8140%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Re: A tricky query in one to many relationship - atleast for me:)

[Marco Silva]

Maybe this can help you 
https://docs.djangoproject.com/en/1.10/topics/db/aggregation/

terça-feira, 30 de Agosto de 2016 às 07:33:14 UTC+1, Web Architect escreveu:
>
> Hi,
>
> I am looking for an elegant and efficient mechanism to have a query filter 
> or a solution for the following one to many relationship model. Please note 
> the following is just an illustration of the models - hope it should 
> provide what I am looking for:
>
> class A(models.Model):
>
> name = models.CharField(_("Name"), max_length=255, unique=True)
>
>
> class B(models.Model):
>
> text = models.CharField(_("Text"), max_length=255, unique=True)
>
> date_created = models.DateTimeField(_("Date Created"), auto_now_add=True) 
>
>  a = models.ForeignKey(A, related_name='b')
>
>
> To get all the instances of B associated with a specific instance of A 
> (say 'a'), I could do the following : a.b.all()
>
> The latest instance of B associated with 'a' would be : 
> a.b.latest('date_created')
>
> Now I would like to have a list of all instances of A where the latest 
> instance of B associated with each instance of A will have the 'text' field 
> as 'ABCD'. 
>
> A.objects.filter(b__text='ABCD') will give all instances of A where each 
> instance of A will have atleast one instance of B with 'text' = 'ABCD'.
>
> There could be a brute force way of getting a solution for the above where 
> in I can do A.objects.filter(b__text='ABCD')  and then go through each 
> instance  of A in a for loop over the queryset and check for the latest 
> instance of B for text='ABCD'.
>
> As mentioned earlier, I am looking for an elegant and optimal way (if any 
> in Django) for the above query.
>
> Thanks.
>
>  
>

-- 
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-users+unsubscr...@googlegroups.com.
To post to this group, send email to django-users@googlegroups.com.
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-users/e9835e6c-c23c-47ec-af2d-b50938d2e250%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Re: A tricky query in one to many relationship - atleast for me:)

[Erik Cederstrand]

> Den 30. aug. 2016 kl. 11.20 skrev Erik Cederstrand 
> :
> 
> I'm not even sure that's possible to express in SQL, but it would probably be 
> quite convoluted if it is. Here's an easier-to-understand solution:
> 
> res = set()
> for b in B.objects.all().select_related('a').annotate(Max('date_created')):
>if b.date_created != b.date_created__max:
>continue
>if b.text != 'ABCD':
>continue
>res.add(a)

I did some more experimenting. I think this actually does what you want:

res = [
b.a for b in B.objects
.filter(date_created=Max('a__b__date_created'))
.annotate(Max('a__b__date_created'))
.filter(text='ABCD')
.select_related('a')
]

which you can rewrite as:

A.objects.filter(
b__in=B.objects
.filter(date_created=Max('a__b__date_created'))
.annotate(Max('a__b__date_created'))
.filter(text='ABCD')
)

Erik

-- 
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-users+unsubscr...@googlegroups.com.
To post to this group, send email to django-users@googlegroups.com.
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-users/BE1AB708-D9DF-499D-92A4-950C11697778%40cederstrand.dk.
For more options, visit https://groups.google.com/d/optout.

Re: A tricky query in one to many relationship - atleast for me:)

[Web Architect]

Hi Erik,

Thanks for the solution.

So, I understand we get all instances of B which are max (date created)  or 
latest (date created) for each instance of A and then we check b.text == 
'ABCD' and select the corresponding instance of A. 
Can't we add additional filter to check if text='ABCD' instead of running 
it through the for loop? I am still yet to catch up with all the concepts 
of Django ORM hence, not sure about it.

Also, if we use Max('a__b__date_created') , do we need the following 
condition still in the for loop? :
if b.date_created != b.date_created__max: 
continue 

Thanks.


On Tuesday, August 30, 2016 at 3:03:44 PM UTC+5:30, Erik Cederstrand wrote:
>
> > for b in 
> B.objects.all().select_related('a').annotate(Max('date_created')): 
>
> That should probably be: Max('a__b__date_created') instead. 
>
> Erik 
>

-- 
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-users+unsubscr...@googlegroups.com.
To post to this group, send email to django-users@googlegroups.com.
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-users/49ab42be-053d-442d-a9b9-51d5123529fc%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Re: A tricky query in one to many relationship - atleast for me:)

[Erik Cederstrand]

> for b in B.objects.all().select_related('a').annotate(Max('date_created')):

That should probably be: Max('a__b__date_created') instead.

Erik

-- 
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-users+unsubscr...@googlegroups.com.
To post to this group, send email to django-users@googlegroups.com.
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-users/E911BFD1-0A2A-4943-AC80-E09D68C1A464%40cederstrand.dk.
For more options, visit https://groups.google.com/d/optout.

Re: A tricky query in one to many relationship - atleast for me:)

[Erik Cederstrand]

I'm not even sure that's possible to express in SQL, but it would probably be 
quite convoluted if it is. Here's an easier-to-understand solution:

res = set()
for b in B.objects.all().select_related('a').annotate(Max('date_created')):
if b.date_created != b.date_created__max:
continue
if b.text != 'ABCD':
continue
res.add(a)


You'll get too many B objects, but it's only one query. If you want a more 
optimal query, try to write it in SQL first. Maybe it's possible to express in 
the ORM.

I used set() because 'date_created' is not unique so there could be two B 
objects with the same 'a' and 'date_created' values.

Erik


> Den 30. aug. 2016 kl. 08.33 skrev Web Architect :
> 
> Hi,
> 
> I am looking for an elegant and efficient mechanism to have a query filter or 
> a solution for the following one to many relationship model. Please note the 
> following is just an illustration of the models - hope it should provide what 
> I am looking for:
> 
> class A(models.Model):
> name = models.CharField(_("Name"), max_length=255, unique=True)
> 
> 
> class B(models.Model):
> text = models.CharField(_("Text"), max_length=255, unique=True)
> date_created = models.DateTimeField(_("Date Created"), auto_now_add=True) 
>  a = models.ForeignKey(A, related_name='b')
> 
> To get all the instances of B associated with a specific instance of A (say 
> 'a'), I could do the following : a.b.all()
> 
> The latest instance of B associated with 'a' would be : 
> a.b.latest('date_created')
> 
> Now I would like to have a list of all instances of A where the latest 
> instance of B associated with each instance of A will have the 'text' field 
> as 'ABCD'. 
> 
> A.objects.filter(b__text='ABCD') will give all instances of A where each 
> instance of A will have atleast one instance of B with 'text' = 'ABCD'.
> 
> There could be a brute force way of getting a solution for the above where in 
> I can do A.objects.filter(b__text='ABCD')  and then go through each instance  
> of A in a for loop over the queryset and check for the latest instance of B 
> for text='ABCD'.
> 
> As mentioned earlier, I am looking for an elegant and optimal way (if any in 
> Django) for the above query.
> 
> Thanks.
> 
>  
> 
> -- 
> You received this message because you are subscribed to the Google Groups 
> "Django users" group.
> To unsubscribe from this group and stop receiving emails from it, send an 
> email to django-users+unsubscr...@googlegroups.com.
> To post to this group, send email to django-users@googlegroups.com.
> Visit this group at https://groups.google.com/group/django-users.
> To view this discussion on the web visit 
> https://groups.google.com/d/msgid/django-users/a9ea312a-578e-4a2f-a739-a38e87a4b15b%40googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.

-- 
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-users+unsubscr...@googlegroups.com.
To post to this group, send email to django-users@googlegroups.com.
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-users/713734EC-F9E9-4CFC-B024-99DC693421D1%40cederstrand.dk.
For more options, visit https://groups.google.com/d/optout.

Re: A tricky query in one to many relationship - atleast for me:)

[Web Architect]

Hi Mike,

Thanks for your response. 

I am aware of chaining filters.

The latest() API in django queryset returns an object and not a queryset. 
Hence, I cannot add a filter to it. 

I had thought about using chaining filters but couldn't find a way.

On Tuesday, August 30, 2016 at 12:18:56 PM UTC+5:30, Mike Dewhirst wrote:
>
> On 30/08/2016 4:33 PM, Web Architect wrote: 
> > Hi, 
> > 
> > I am looking for an elegant and efficient mechanism to have a query 
> > filter or a solution for the following one to many relationship model. 
> > Please note the following is just an illustration of the models - hope 
> > it should provide what I am looking for: 
> > 
> > class A(models.Model): 
> > 
> > name = models.CharField(_("Name"), max_length=255, unique=True) 
> > 
> > 
> > class B(models.Model): 
> > 
> > text = models.CharField(_("Text"), max_length=255, unique=True) 
> > 
> > date_created = models.DateTimeField(_("Date Created"), 
> > auto_now_add=True)Â 
> > 
> > Â a =Â models.ForeignKey(A, related_name='b') 
> > 
> > 
> > To get all the instances of B associated with a specific instance of A 
> > (say 'a'), I could do the following : a.b.all() 
> > 
> > The latest instance of B associated with 'a' would be : 
> > a.b.latest('date_created') 
> > 
> > Now I would like to have a list of all instances of A where the latest 
> > instance of B associated with each instance of A will have the 'text' 
> > field as 'ABCD'. 
> > 
> > A.objects.filter(b__text='ABCD') will give all instances of A where 
> > each instance of A will have atleast one instance of B with 'text' = 
> > 'ABCD'. 
> > 
> > There could be a brute force way of getting a solution for the above 
> > where in I can do A.objects.filter(b__text='ABCD') Â and then go 
> > through each instance  of A in a for loop over the queryset and check 
> > for the latest instance of B for text='ABCD'. 
>
> HAve you looked at ... 
>
> https://docs.djangoproject.com/en/1.8/topics/db/queries/#chaining-filters 
>
>
> > 
> > As mentioned earlier, I am looking for an elegant and optimal way (if 
> > any in Django) for the above query. 
> > 
> > Thanks. 
> > 
> > Â 
> > -- 
> > You received this message because you are subscribed to the Google 
> > Groups "Django users" group. 
> > To unsubscribe from this group and stop receiving emails from it, send 
> > an email to django-users...@googlegroups.com  
> > . 
> > To post to this group, send email to django...@googlegroups.com 
>  
> > . 
> > Visit this group at https://groups.google.com/group/django-users. 
> > To view this discussion on the web visit 
> > 
> https://groups.google.com/d/msgid/django-users/a9ea312a-578e-4a2f-a739-a38e87a4b15b%40googlegroups.com
>  
> > <
> https://groups.google.com/d/msgid/django-users/a9ea312a-578e-4a2f-a739-a38e87a4b15b%40googlegroups.com?utm_medium=email_source=footer>.
>  
>
> > For more options, visit https://groups.google.com/d/optout. 
>
>

-- 
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-users+unsubscr...@googlegroups.com.
To post to this group, send email to django-users@googlegroups.com.
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-users/508c9991-7d21-4583-82e5-1abea0584511%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Re: A tricky query in one to many relationship - atleast for me:)

[Mike Dewhirst]

On 30/08/2016 4:33 PM, Web Architect wrote:

Hi,

I am looking for an elegant and efficient mechanism to have a query 
filter or a solution for the following one to many relationship model. 
Please note the following is just an illustration of the models - hope 
it should provide what I am looking for:


class A(models.Model):

name = models.CharField(_("Name"), max_length=255, unique=True)


class B(models.Model):

text = models.CharField(_("Text"), max_length=255, unique=True)

date_created = models.DateTimeField(_("Date Created"),
auto_now_add=True)Â

 a = models.ForeignKey(A, related_name='b')


To get all the instances of B associated with a specific instance of A 
(say 'a'), I could do the following : a.b.all()


The latest instance of B associated with 'a' would be : 
a.b.latest('date_created')


Now I would like to have a list of all instances of A where the latest 
instance of B associated with each instance of A will have the 'text' 
field as 'ABCD'.Â


A.objects.filter(b__text='ABCD') will give all instances of A where 
each instance of A will have atleast one instance of B with 'text' = 
'ABCD'.


There could be a brute force way of getting a solution for the above 
where in I can do A.objects.filter(b__text='ABCD') Â and then go 
through each instance  of A in a for loop over the queryset and check 
for the latest instance of B for text='ABCD'.


HAve you looked at ...

https://docs.djangoproject.com/en/1.8/topics/db/queries/#chaining-filters




As mentioned earlier, I am looking for an elegant and optimal way (if 
any in Django) for the above query.


Thanks.

Â
--
You received this message because you are subscribed to the Google 
Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send 
an email to django-users+unsubscr...@googlegroups.com 
.
To post to this group, send email to django-users@googlegroups.com 
.

Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-users/a9ea312a-578e-4a2f-a739-a38e87a4b15b%40googlegroups.com 
.

For more options, visit https://groups.google.com/d/optout.


--
You received this message because you are subscribed to the Google Groups "Django 
users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-users+unsubscr...@googlegroups.com.
To post to this group, send email to django-users@googlegroups.com.
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-users/1ba91a1c-6c22-5bc2-f09b-7b9926ca30a7%40dewhirst.com.au.
For more options, visit https://groups.google.com/d/optout.

A tricky query in one to many relationship - atleast for me:)

[Web Architect]

Hi,

I am looking for an elegant and efficient mechanism to have a query filter 
or a solution for the following one to many relationship model. Please note 
the following is just an illustration of the models - hope it should 
provide what I am looking for:

class A(models.Model):

name = models.CharField(_("Name"), max_length=255, unique=True)


class B(models.Model):

text = models.CharField(_("Text"), max_length=255, unique=True)

date_created = models.DateTimeField(_("Date Created"), auto_now_add=True) 

 a = models.ForeignKey(A, related_name='b')


To get all the instances of B associated with a specific instance of A (say 
'a'), I could do the following : a.b.all()

The latest instance of B associated with 'a' would be : 
a.b.latest('date_created')

Now I would like to have a list of all instances of A where the latest 
instance of B associated with each instance of A will have the 'text' field 
as 'ABCD'. 

A.objects.filter(b__text='ABCD') will give all instances of A where each 
instance of A will have atleast one instance of B with 'text' = 'ABCD'.

There could be a brute force way of getting a solution for the above where 
in I can do A.objects.filter(b__text='ABCD')  and then go through each 
instance  of A in a for loop over the queryset and check for the latest 
instance of B for text='ABCD'.

As mentioned earlier, I am looking for an elegant and optimal way (if any 
in Django) for the above query.

Thanks.

 

-- 
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-users+unsubscr...@googlegroups.com.
To post to this group, send email to django-users@googlegroups.com.
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-users/a9ea312a-578e-4a2f-a739-a38e87a4b15b%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.