Complex Query

[sebasti...@gmail.com]

Hello Guys,

you find my question in attachment because tabs in tables are not shown 
when i send it in gmail.

Please help me on my question who make i a complex query.

Thanks in advance

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/django-users/935740cb-8289-4341-80c4-407b0100e3adn%40googlegroups.com.


Question
Description: Binary data

Re: Replicating complex query with two dense_rank() functions using ORM

[Simon Charette]

This is unfortunately not possible to do through the ORM right now due to 
lack of support for filtering against window expressions[0]

Until this ticket is solved you'll have to rely on raw SQL.

Cheers,
Simon

[0] ticket https://code.djangoproject.com/ticket/28333

Le mercredi 4 novembre 2020 à 12:25:30 UTC-5, nhb.ra...@gmail.com a écrit :

> Hi,
>
> In Django queryset you can query PuzzleAnswer, filter by author (assuming 
> that is the user), order_by('created)' and return the created dates only: 
> values_list('created', flat=True). Then take the len() of that list and 
> check the [0] and [-1] entries for first and last date. The rest is date 
> math.
>
> In general I try to keep the number of database accesses as low as 
> possible. A pre-calculated answer takes less resources to present than 
> calculating this over and over again, especially if you have many users. So 
> you could do the above and store the result in a new table every time a new 
> PuzzleAnswer has been added for a user.
>
> Ramon
>
> Op woensdag 4 november 2020 om 05:34:22 UTC+1 schreef Brad Buran:
>
>> I have a "puzzle of the day" that users can answer. I track the puzzle of 
>> the day using the Puzzle model. I track the answers using the PuzzleAnswer 
>> model. I would like to calculate the number of consecutive puzzles a 
>> particular user (i.e., the author) gets right in a row. The current SQL I 
>> use that can calculate the start date of the streak, end date of the streak 
>> and the number of days in the streak. As you can see, it does a dens_rank 
>> over the puzzles (to count them in order), then does a join with the 
>> PuzzleAnswer, then does a second dense rank over the merged tables. I 
>> figured out how to use the DenseRank function in the Django ORM on the 
>> Puzzle manager, but I cannot figure out how to do the left join next. Any 
>> advice?
>>
>> SELECT min(s.id) AS id, 
>>count(s.date) AS streak, 
>>min(s.date) AS start_streak, 
>>max(s.date) AS end_streak, 
>>s.author_id 
>>   FROM ( SELECT dense_rank() OVER (ORDER BY ROW(pa.author_id, pr.rank)) 
>> AS id, 
>>pa.created AS date, 
>>(pr.rank - dense_rank() OVER (ORDER BY ROW(pa.author_id, 
>> pr.rank))) AS g, 
>>pa.author_id 
>>   FROM (( SELECT "POTD_puzzle".id, 
>>dense_rank() OVER (ORDER BY "POTD_puzzle".published) 
>> AS rank 
>>   FROM public."POTD_puzzle") pr 
>> JOIN public."POTD_puzzleanswer" pa ON ((pr.id = 
>> pa.puzzle_id))) 
>>  WHERE pa.correct) s 
>>  GROUP BY s.author_id, s.g 
>>  ORDER BY count(s.date) DESC;
>>
>> The models are:
>>
>> class PuzzleAnswer(models.Model): 
>>puzzle = models.ForeignKey(Puzzle, editable=True, 
>> on_delete=models.CASCADE) 
>>
>>answer = models.CharField(max_length=64)   
>>correct = models.BooleanField(editable=False)
>>created = models.DateTimeField(auto_now_add=True) 
>>author = models.ForeignKey(settings.AUTH_USER_MODEL, blank=True, 
>> null=True, 
>>
>>   on_delete=models.SET_NULL) 
>>  
>>  
>>
>> class Puzzle(models.Model):
>>category = models.ForeignKey(PuzzleCategory, on_delete=models.CASCADE, 
>> help_text=category_help)
>>notation = models.CharField(max_length=64) 
>>correct_answer = models.CharField(max_length=64) 
>>published = models.DateField(blank=True, null=True, db_index=True, 
>> unique=True)
>> 
>>  
>>
>

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/django-users/7282e7dc-e098-462e-b9aa-050dfcbde60dn%40googlegroups.com.

Re: Replicating complex query with two dense_rank() functions using ORM

[Ramon NHB]

Hi,

In Django queryset you can query PuzzleAnswer, filter by author (assuming 
that is the user), order_by('created)' and return the created dates only: 
values_list('created', flat=True). Then take the len() of that list and 
check the [0] and [-1] entries for first and last date. The rest is date 
math.

In general I try to keep the number of database accesses as low as 
possible. A pre-calculated answer takes less resources to present than 
calculating this over and over again, especially if you have many users. So 
you could do the above and store the result in a new table every time a new 
PuzzleAnswer has been added for a user.

Ramon

Op woensdag 4 november 2020 om 05:34:22 UTC+1 schreef Brad Buran:

> I have a "puzzle of the day" that users can answer. I track the puzzle of 
> the day using the Puzzle model. I track the answers using the PuzzleAnswer 
> model. I would like to calculate the number of consecutive puzzles a 
> particular user (i.e., the author) gets right in a row. The current SQL I 
> use that can calculate the start date of the streak, end date of the streak 
> and the number of days in the streak. As you can see, it does a dens_rank 
> over the puzzles (to count them in order), then does a join with the 
> PuzzleAnswer, then does a second dense rank over the merged tables. I 
> figured out how to use the DenseRank function in the Django ORM on the 
> Puzzle manager, but I cannot figure out how to do the left join next. Any 
> advice?
>
> SELECT min(s.id) AS id, 
>count(s.date) AS streak, 
>min(s.date) AS start_streak, 
>max(s.date) AS end_streak, 
>s.author_id 
>   FROM ( SELECT dense_rank() OVER (ORDER BY ROW(pa.author_id, pr.rank)) AS 
> id, 
>pa.created AS date, 
>(pr.rank - dense_rank() OVER (ORDER BY ROW(pa.author_id, 
> pr.rank))) AS g, 
>pa.author_id 
>   FROM (( SELECT "POTD_puzzle".id, 
>dense_rank() OVER (ORDER BY "POTD_puzzle".published) AS 
> rank 
>   FROM public."POTD_puzzle") pr 
> JOIN public."POTD_puzzleanswer" pa ON ((pr.id = 
> pa.puzzle_id))) 
>  WHERE pa.correct) s 
>  GROUP BY s.author_id, s.g 
>  ORDER BY count(s.date) DESC;
>
> The models are:
>
> class PuzzleAnswer(models.Model): 
>puzzle = models.ForeignKey(Puzzle, editable=True, 
> on_delete=models.CASCADE) 
>
>answer = models.CharField(max_length=64)   
>correct = models.BooleanField(editable=False)
>created = models.DateTimeField(auto_now_add=True) 
>author = models.ForeignKey(settings.AUTH_USER_MODEL, blank=True, 
> null=True, 
>
>   on_delete=models.SET_NULL) 
>   
> 
>
> class Puzzle(models.Model):
>category = models.ForeignKey(PuzzleCategory, on_delete=models.CASCADE, 
> help_text=category_help)
>notation = models.CharField(max_length=64) 
>correct_answer = models.CharField(max_length=64) 
>published = models.DateField(blank=True, null=True, db_index=True, 
> unique=True)
> 
>  
>

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/django-users/953151f0-3e19-4111-ab08-e330169908bdn%40googlegroups.com.

Replicating complex query with two dense_rank() functions using ORM

[Brad Buran]

I have a "puzzle of the day" that users can answer. I track the puzzle of 
the day using the Puzzle model. I track the answers using the PuzzleAnswer 
model. I would like to calculate the number of consecutive puzzles a 
particular user (i.e., the author) gets right in a row. The current SQL I 
use that can calculate the start date of the streak, end date of the streak 
and the number of days in the streak. As you can see, it does a dens_rank 
over the puzzles (to count them in order), then does a join with the 
PuzzleAnswer, then does a second dense rank over the merged tables. I 
figured out how to use the DenseRank function in the Django ORM on the 
Puzzle manager, but I cannot figure out how to do the left join next. Any 
advice?

SELECT min(s.id) AS id, 
   count(s.date) AS streak, 
   min(s.date) AS start_streak, 
   max(s.date) AS end_streak, 
   s.author_id 
  FROM ( SELECT dense_rank() OVER (ORDER BY ROW(pa.author_id, pr.rank)) AS 
id, 
   pa.created AS date, 
   (pr.rank - dense_rank() OVER (ORDER BY ROW(pa.author_id, 
pr.rank))) AS g, 
   pa.author_id 
  FROM (( SELECT "POTD_puzzle".id, 
   dense_rank() OVER (ORDER BY "POTD_puzzle".published) AS 
rank 
  FROM public."POTD_puzzle") pr 
JOIN public."POTD_puzzleanswer" pa ON ((pr.id = pa.puzzle_id))) 
 WHERE pa.correct) s 
 GROUP BY s.author_id, s.g 
 ORDER BY count(s.date) DESC;

The models are:

class PuzzleAnswer(models.Model): 
   puzzle = models.ForeignKey(Puzzle, editable=True, on_delete=models.CASCADE) 

   answer = models.CharField(max_length=64)   
   correct = models.BooleanField(editable=False)
   created = models.DateTimeField(auto_now_add=True) 
   author = models.ForeignKey(settings.AUTH_USER_MODEL, blank=True, null=True, 

  on_delete=models.SET_NULL) 

  

class Puzzle(models.Model):
   category = models.ForeignKey(PuzzleCategory, on_delete=models.CASCADE, 
help_text=category_help)
   notation = models.CharField(max_length=64) 
   correct_answer = models.CharField(max_length=64) 
   published = models.DateField(blank=True, null=True, db_index=True, unique
=True)

 

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/django-users/3d8106a7-df44-4697-91fe-06c861132ae6n%40googlegroups.com.

Re: Complex query on inner join - case for __ne?

[nm]

I've only had a quick look at your problem, but looks like maybe this 
section of the Django documentation could be useful: 
https://docs.djangoproject.com/en/dev/topics/db/queries/#spanning-multi-valued-relationships


On Friday, 12 April 2019 15:10:59 UTC+2, Michael Thomas wrote:
>
> Hello everyone,
>
> I've run into what I believe is a limitation of the ORM that other people 
> must be dealing with somehow, but I can't seem to figure out a sensible 
> solution.
>
> I think it's easiest to describe the problem with code.
>
> For the following models:
>
> class Foo(models.Model):
> name = models.CharField(max_length=64)
>
>
> class Bar(models.Model):
> foo = models.ForeignKey(Foo, on_delete=models.CASCADE)
> attribute_1 = models.IntegerField()
> attribute_2 = models.IntegerField()
>
> I want to select all Foo() that have 1 or more bar with attribute_1 not 
> equal to 1, and attribute_2 equal to 2.
>
> Eg. SQL something like this:
>
> SELECT 
> "app_foo"."id", 
> "app_foo"."name" 
> FROM "app_foo" 
> INNER JOIN "app_bar" ON (
> "app_foo"."id" = "app_bar"."foo_id"
> ) 
> WHERE (
> "app_bar"."attribute_1" <> 1 
> AND "app_bar"."attribute_2" = 2
> )
>
> However, here's what I end up with...
>
>
> print(Foo.objects.exclude(bar__attribute_1=1).filter(bar__attribute_2=2).query)
> SELECT 
> "app_foo"."id", 
> "app_foo"."name" 
> FROM "app_foo" 
> INNER JOIN "app_bar" ON (
> "app_foo"."id" = "app_bar"."foo_id"
> ) 
> WHERE (
> NOT (
> "app_foo"."id" IN (
> SELECT 
> U1."foo_id" 
> FROM "app_bar" U1 
> WHERE U1."attribute_1" = 1
> )
> ) 
> AND "app_bar"."attribute_2" = 2
> )
>
> print(Foo.objects.filter(~Q(bar__attribute_1=1), bar__attribute_2=2).query)
> Exact same SQL output as above 
>
> Interestingly enough, a simple query for attribute_1=1 and attribute_2=2 
> works as expected, so it would be trivial to do this with a __ne operator 
> (if it existed), without any other changes to the ORM:
>
> print(Foo.objects.filter(bar__attribute_1=1, bar__attribute_2=2).query)
> SELECT 
> "app_foo"."id", 
> "app_foo"."name" 
> FROM "app_foo" 
> INNER JOIN "app_bar" ON (
> "app_foo"."id" = "app_bar"."foo_id"
> ) 
> WHERE (
> "app_bar"."attribute_1" = 1 
> AND "app_bar"."attribute_2" = 2
> )
>
> Am I missing something here? How are other people tackling this?
>
> Kind Regards,
> Michael Thomas
>
>

-- 
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/8142abe8-1d14-4eff-aa6b-0f281ce14181%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Re: Complex query on inner join - case for __ne?

[Michael Thomas]

Naimur: I am - that SQL was in response to  Aldian's question :)

On Fri, Apr 12, 2019 at 8:23 PM naimur rahman 
wrote:

> use ORM istead of SQL
>
> On Fri, Apr 12, 2019, 7:42 PM Michael Thomas <
> michael.thomas.s...@gmail.com> wrote:
>
>> SELECT
>> "app_foo"."id",
>> "app_foo"."name"
>> FROM "app_foo"
>> INNER JOIN "app_bar" ON (
>> "app_foo"."id" = "app_bar"."foo_id"
>> )
>> WHERE (
>> NOT (
>> "app_foo"."id" IN (
>> SELECT
>> U1."foo_id"
>> FROM "app_bar" U1
>> WHERE U1."attribute_1" = 1
>> )
>> )
>> AND "app_bar"."attribute_2" = 2
>> )
>>
>> On Fri, Apr 12, 2019 at 6:59 PM Aldian Fazrihady 
>> wrote:
>>
>>> What's the result of
>>>
>>> print(Foo.objects.exclude(bar__attribute=1).filter(
>>> bar__attribute_2=2).query)
>>>
>>> On Fri, 12 Apr 2019, 20:10 Michael Thomas, <
>>> michael.thomas.s...@gmail.com> wrote:
>>>
 Hello everyone,

 I've run into what I believe is a limitation of the ORM that other
 people must be dealing with somehow, but I can't seem to figure out a
 sensible solution.

 I think it's easiest to describe the problem with code.

 For the following models:

 class Foo(models.Model):
 name = models.CharField(max_length=64)


 class Bar(models.Model):
 foo = models.ForeignKey(Foo, on_delete=models.CASCADE)
 attribute_1 = models.IntegerField()
 attribute_2 = models.IntegerField()

 I want to select all Foo() that have 1 or more bar with attribute_1 not
 equal to 1, and attribute_2 equal to 2.

 Eg. SQL something like this:

 SELECT
 "app_foo"."id",
 "app_foo"."name"
 FROM "app_foo"
 INNER JOIN "app_bar" ON (
 "app_foo"."id" = "app_bar"."foo_id"
 )
 WHERE (
 "app_bar"."attribute_1" <> 1
 AND "app_bar"."attribute_2" = 2
 )

 However, here's what I end up with...


 print(Foo.objects.exclude(bar__attribute_1=1).filter(bar__attribute_2=2).query)
 SELECT
 "app_foo"."id",
 "app_foo"."name"
 FROM "app_foo"
 INNER JOIN "app_bar" ON (
 "app_foo"."id" = "app_bar"."foo_id"
 )
 WHERE (
 NOT (
 "app_foo"."id" IN (
 SELECT
 U1."foo_id"
 FROM "app_bar" U1
 WHERE U1."attribute_1" = 1
 )
 )
 AND "app_bar"."attribute_2" = 2
 )

 print(Foo.objects.filter(~Q(bar__attribute_1=1),
 bar__attribute_2=2).query)
 Exact same SQL output as above

 Interestingly enough, a simple query for attribute_1=1 and
 attribute_2=2 works as expected, so it would be trivial to do this with a
 __ne operator (if it existed), without any other changes to the ORM:

 print(Foo.objects.filter(bar__attribute_1=1, bar__attribute_2=2).query)
 SELECT
 "app_foo"."id",
 "app_foo"."name"
 FROM "app_foo"
 INNER JOIN "app_bar" ON (
 "app_foo"."id" = "app_bar"."foo_id"
 )
 WHERE (
 "app_bar"."attribute_1" = 1
 AND "app_bar"."attribute_2" = 2
 )

 Am I missing something here? How are other people tackling this?

 Kind Regards,
 Michael Thomas

 --
 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/d852fc10-5f5a-43e8-8dab-c796404867a8%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/CAN7EoAaXupYqNoPcHOcZ3OoA_07N--D4EPoEfZvkSWL%2BeLbD6A%40mail.gmail.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 

Re: Complex query on inner join - case for __ne?

[naimur rahman]

use ORM istead of SQL

On Fri, Apr 12, 2019, 7:42 PM Michael Thomas 
wrote:

> SELECT
> "app_foo"."id",
> "app_foo"."name"
> FROM "app_foo"
> INNER JOIN "app_bar" ON (
> "app_foo"."id" = "app_bar"."foo_id"
> )
> WHERE (
> NOT (
> "app_foo"."id" IN (
> SELECT
> U1."foo_id"
> FROM "app_bar" U1
> WHERE U1."attribute_1" = 1
> )
> )
> AND "app_bar"."attribute_2" = 2
> )
>
> On Fri, Apr 12, 2019 at 6:59 PM Aldian Fazrihady 
> wrote:
>
>> What's the result of
>>
>> print(Foo.objects.exclude(bar__attribute=1).filter(
>> bar__attribute_2=2).query)
>>
>> On Fri, 12 Apr 2019, 20:10 Michael Thomas, 
>> wrote:
>>
>>> Hello everyone,
>>>
>>> I've run into what I believe is a limitation of the ORM that other
>>> people must be dealing with somehow, but I can't seem to figure out a
>>> sensible solution.
>>>
>>> I think it's easiest to describe the problem with code.
>>>
>>> For the following models:
>>>
>>> class Foo(models.Model):
>>> name = models.CharField(max_length=64)
>>>
>>>
>>> class Bar(models.Model):
>>> foo = models.ForeignKey(Foo, on_delete=models.CASCADE)
>>> attribute_1 = models.IntegerField()
>>> attribute_2 = models.IntegerField()
>>>
>>> I want to select all Foo() that have 1 or more bar with attribute_1 not
>>> equal to 1, and attribute_2 equal to 2.
>>>
>>> Eg. SQL something like this:
>>>
>>> SELECT
>>> "app_foo"."id",
>>> "app_foo"."name"
>>> FROM "app_foo"
>>> INNER JOIN "app_bar" ON (
>>> "app_foo"."id" = "app_bar"."foo_id"
>>> )
>>> WHERE (
>>> "app_bar"."attribute_1" <> 1
>>> AND "app_bar"."attribute_2" = 2
>>> )
>>>
>>> However, here's what I end up with...
>>>
>>>
>>> print(Foo.objects.exclude(bar__attribute_1=1).filter(bar__attribute_2=2).query)
>>> SELECT
>>> "app_foo"."id",
>>> "app_foo"."name"
>>> FROM "app_foo"
>>> INNER JOIN "app_bar" ON (
>>> "app_foo"."id" = "app_bar"."foo_id"
>>> )
>>> WHERE (
>>> NOT (
>>> "app_foo"."id" IN (
>>> SELECT
>>> U1."foo_id"
>>> FROM "app_bar" U1
>>> WHERE U1."attribute_1" = 1
>>> )
>>> )
>>> AND "app_bar"."attribute_2" = 2
>>> )
>>>
>>> print(Foo.objects.filter(~Q(bar__attribute_1=1),
>>> bar__attribute_2=2).query)
>>> Exact same SQL output as above
>>>
>>> Interestingly enough, a simple query for attribute_1=1 and attribute_2=2
>>> works as expected, so it would be trivial to do this with a __ne operator
>>> (if it existed), without any other changes to the ORM:
>>>
>>> print(Foo.objects.filter(bar__attribute_1=1, bar__attribute_2=2).query)
>>> SELECT
>>> "app_foo"."id",
>>> "app_foo"."name"
>>> FROM "app_foo"
>>> INNER JOIN "app_bar" ON (
>>> "app_foo"."id" = "app_bar"."foo_id"
>>> )
>>> WHERE (
>>> "app_bar"."attribute_1" = 1
>>> AND "app_bar"."attribute_2" = 2
>>> )
>>>
>>> Am I missing something here? How are other people tackling this?
>>>
>>> Kind Regards,
>>> Michael Thomas
>>>
>>> --
>>> 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/d852fc10-5f5a-43e8-8dab-c796404867a8%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/CAN7EoAaXupYqNoPcHOcZ3OoA_07N--D4EPoEfZvkSWL%2BeLbD6A%40mail.gmail.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
> 

Re: Complex query on inner join - case for __ne?

[Michael Thomas]

SELECT
"app_foo"."id",
"app_foo"."name"
FROM "app_foo"
INNER JOIN "app_bar" ON (
"app_foo"."id" = "app_bar"."foo_id"
)
WHERE (
NOT (
"app_foo"."id" IN (
SELECT
U1."foo_id"
FROM "app_bar" U1
WHERE U1."attribute_1" = 1
)
)
AND "app_bar"."attribute_2" = 2
)

On Fri, Apr 12, 2019 at 6:59 PM Aldian Fazrihady  wrote:

> What's the result of
>
> print(Foo.objects.exclude(bar__attribute=1).filter(
> bar__attribute_2=2).query)
>
> On Fri, 12 Apr 2019, 20:10 Michael Thomas, 
> wrote:
>
>> Hello everyone,
>>
>> I've run into what I believe is a limitation of the ORM that other people
>> must be dealing with somehow, but I can't seem to figure out a sensible
>> solution.
>>
>> I think it's easiest to describe the problem with code.
>>
>> For the following models:
>>
>> class Foo(models.Model):
>> name = models.CharField(max_length=64)
>>
>>
>> class Bar(models.Model):
>> foo = models.ForeignKey(Foo, on_delete=models.CASCADE)
>> attribute_1 = models.IntegerField()
>> attribute_2 = models.IntegerField()
>>
>> I want to select all Foo() that have 1 or more bar with attribute_1 not
>> equal to 1, and attribute_2 equal to 2.
>>
>> Eg. SQL something like this:
>>
>> SELECT
>> "app_foo"."id",
>> "app_foo"."name"
>> FROM "app_foo"
>> INNER JOIN "app_bar" ON (
>> "app_foo"."id" = "app_bar"."foo_id"
>> )
>> WHERE (
>> "app_bar"."attribute_1" <> 1
>> AND "app_bar"."attribute_2" = 2
>> )
>>
>> However, here's what I end up with...
>>
>>
>> print(Foo.objects.exclude(bar__attribute_1=1).filter(bar__attribute_2=2).query)
>> SELECT
>> "app_foo"."id",
>> "app_foo"."name"
>> FROM "app_foo"
>> INNER JOIN "app_bar" ON (
>> "app_foo"."id" = "app_bar"."foo_id"
>> )
>> WHERE (
>> NOT (
>> "app_foo"."id" IN (
>> SELECT
>> U1."foo_id"
>> FROM "app_bar" U1
>> WHERE U1."attribute_1" = 1
>> )
>> )
>> AND "app_bar"."attribute_2" = 2
>> )
>>
>> print(Foo.objects.filter(~Q(bar__attribute_1=1),
>> bar__attribute_2=2).query)
>> Exact same SQL output as above
>>
>> Interestingly enough, a simple query for attribute_1=1 and attribute_2=2
>> works as expected, so it would be trivial to do this with a __ne operator
>> (if it existed), without any other changes to the ORM:
>>
>> print(Foo.objects.filter(bar__attribute_1=1, bar__attribute_2=2).query)
>> SELECT
>> "app_foo"."id",
>> "app_foo"."name"
>> FROM "app_foo"
>> INNER JOIN "app_bar" ON (
>> "app_foo"."id" = "app_bar"."foo_id"
>> )
>> WHERE (
>> "app_bar"."attribute_1" = 1
>> AND "app_bar"."attribute_2" = 2
>> )
>>
>> Am I missing something here? How are other people tackling this?
>>
>> Kind Regards,
>> Michael Thomas
>>
>> --
>> 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/d852fc10-5f5a-43e8-8dab-c796404867a8%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/CAN7EoAaXupYqNoPcHOcZ3OoA_07N--D4EPoEfZvkSWL%2BeLbD6A%40mail.gmail.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/CAEdx1fpYmYHXX14iu3Enwc18Ojkk3%3DBfzTv3mGWVdf5BzZVv7w%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.

Re: Complex query on inner join - case for __ne?

[Aldian Fazrihady]

What's the result of

print(Foo.objects.exclude(bar__attribute=1).filter(
bar__attribute_2=2).query)

On Fri, 12 Apr 2019, 20:10 Michael Thomas, 
wrote:

> Hello everyone,
>
> I've run into what I believe is a limitation of the ORM that other people
> must be dealing with somehow, but I can't seem to figure out a sensible
> solution.
>
> I think it's easiest to describe the problem with code.
>
> For the following models:
>
> class Foo(models.Model):
> name = models.CharField(max_length=64)
>
>
> class Bar(models.Model):
> foo = models.ForeignKey(Foo, on_delete=models.CASCADE)
> attribute_1 = models.IntegerField()
> attribute_2 = models.IntegerField()
>
> I want to select all Foo() that have 1 or more bar with attribute_1 not
> equal to 1, and attribute_2 equal to 2.
>
> Eg. SQL something like this:
>
> SELECT
> "app_foo"."id",
> "app_foo"."name"
> FROM "app_foo"
> INNER JOIN "app_bar" ON (
> "app_foo"."id" = "app_bar"."foo_id"
> )
> WHERE (
> "app_bar"."attribute_1" <> 1
> AND "app_bar"."attribute_2" = 2
> )
>
> However, here's what I end up with...
>
>
> print(Foo.objects.exclude(bar__attribute_1=1).filter(bar__attribute_2=2).query)
> SELECT
> "app_foo"."id",
> "app_foo"."name"
> FROM "app_foo"
> INNER JOIN "app_bar" ON (
> "app_foo"."id" = "app_bar"."foo_id"
> )
> WHERE (
> NOT (
> "app_foo"."id" IN (
> SELECT
> U1."foo_id"
> FROM "app_bar" U1
> WHERE U1."attribute_1" = 1
> )
> )
> AND "app_bar"."attribute_2" = 2
> )
>
> print(Foo.objects.filter(~Q(bar__attribute_1=1), bar__attribute_2=2).query)
> Exact same SQL output as above
>
> Interestingly enough, a simple query for attribute_1=1 and attribute_2=2
> works as expected, so it would be trivial to do this with a __ne operator
> (if it existed), without any other changes to the ORM:
>
> print(Foo.objects.filter(bar__attribute_1=1, bar__attribute_2=2).query)
> SELECT
> "app_foo"."id",
> "app_foo"."name"
> FROM "app_foo"
> INNER JOIN "app_bar" ON (
> "app_foo"."id" = "app_bar"."foo_id"
> )
> WHERE (
> "app_bar"."attribute_1" = 1
> AND "app_bar"."attribute_2" = 2
> )
>
> Am I missing something here? How are other people tackling this?
>
> Kind Regards,
> Michael Thomas
>
> --
> 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/d852fc10-5f5a-43e8-8dab-c796404867a8%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/CAN7EoAaXupYqNoPcHOcZ3OoA_07N--D4EPoEfZvkSWL%2BeLbD6A%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.

Complex query on inner join - case for __ne?

[Michael Thomas]

Hello everyone,

I've run into what I believe is a limitation of the ORM that other people 
must be dealing with somehow, but I can't seem to figure out a sensible 
solution.

I think it's easiest to describe the problem with code.

For the following models:

class Foo(models.Model):
name = models.CharField(max_length=64)


class Bar(models.Model):
foo = models.ForeignKey(Foo, on_delete=models.CASCADE)
attribute_1 = models.IntegerField()
attribute_2 = models.IntegerField()

I want to select all Foo() that have 1 or more bar with attribute_1 not 
equal to 1, and attribute_2 equal to 2.

Eg. SQL something like this:

SELECT 
"app_foo"."id", 
"app_foo"."name" 
FROM "app_foo" 
INNER JOIN "app_bar" ON (
"app_foo"."id" = "app_bar"."foo_id"
) 
WHERE (
"app_bar"."attribute_1" <> 1 
AND "app_bar"."attribute_2" = 2
)

However, here's what I end up with...

print(Foo.objects.exclude(bar__attribute_1=1).filter(bar__attribute_2=2).query)
SELECT 
"app_foo"."id", 
"app_foo"."name" 
FROM "app_foo" 
INNER JOIN "app_bar" ON (
"app_foo"."id" = "app_bar"."foo_id"
) 
WHERE (
NOT (
"app_foo"."id" IN (
SELECT 
U1."foo_id" 
FROM "app_bar" U1 
WHERE U1."attribute_1" = 1
)
) 
AND "app_bar"."attribute_2" = 2
)

print(Foo.objects.filter(~Q(bar__attribute_1=1), bar__attribute_2=2).query)
Exact same SQL output as above 

Interestingly enough, a simple query for attribute_1=1 and attribute_2=2 
works as expected, so it would be trivial to do this with a __ne operator 
(if it existed), without any other changes to the ORM:

print(Foo.objects.filter(bar__attribute_1=1, bar__attribute_2=2).query)
SELECT 
"app_foo"."id", 
"app_foo"."name" 
FROM "app_foo" 
INNER JOIN "app_bar" ON (
"app_foo"."id" = "app_bar"."foo_id"
) 
WHERE (
"app_bar"."attribute_1" = 1 
AND "app_bar"."attribute_2" = 2
)

Am I missing something here? How are other people tackling this?

Kind Regards,
Michael Thomas

-- 
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/d852fc10-5f5a-43e8-8dab-c796404867a8%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Re: Getting complex query set with a many to many relationship in django

[Andy]

If you want to have Articles then get 
Article.objects.filter(section=xy).oder_by('section__order')

Am Donnerstag, 22. Februar 2018 20:47:31 UTC+1 schrieb James Farris:
>
> I am trying to *get all article objects that are part of a section* in an 
> edition that is in an issue. I am stumped, even after looking at the 
> documentation 
> 
>  
> for django 2.x
>
> I can get all editions with all of their sections that are part of an 
> issue, but I cannot figure out how to get articles that are related to a 
> section per edition.
>
> Sample code is here:
> https://codeshare.io/5vXbAD
>
> Please note I have a many to many pass through table that has extra fields 
> I am trying to get.  Specifically the "order" field.
> Here is a screenshot for proof of concept
> https://pasteboard.co/H8N2zTt.png
>
> If you are able to get all articles returned from the loop in the code 
> linked above, I will be eternally grateful :)
>
> results would be passed to a template.
>

-- 
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/57baf37b-601f-4bb3-b7f2-b66daac6147c%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Getting complex query set with a many to many relationship in django

[James Farris]

I am trying to *get all article objects that are part of a section* in an 
edition that is in an issue. I am stumped, even after looking at the 
documentation 

 
for django 2.x

I can get all editions with all of their sections that are part of an 
issue, but I cannot figure out how to get articles that are related to a 
section per edition.

Sample code is here:
https://codeshare.io/5vXbAD

Please note I have a many to many pass through table that has extra fields 
I am trying to get.  Specifically the "order" field.
Here is a screenshot for proof of concept
https://pasteboard.co/H8N2zTt.png

If you are able to get all articles returned from the loop in the code 
linked above, I will be eternally grateful :)

results would be passed to a template.

-- 
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/1ba9dc4e-3eab-4d1e-b552-1102e8e7791a%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Re: Complex query, queryset.query.alias_map and query changes

[Felipe Prenholato]

Yeah, I know :\, but still maybe someone have a good idea :), lot of people
still using old Django versions.

I tried the expressions, the `F()` but it not worked well and I had no time
to debug why, but in next weeks I'll probably create a slug field to
compare directly.

I post here if have updates.

Thx Tim.

Felipe Prenholato.
Home page: http://devwithpassion.com | http://chronosbox.org/blog
GitHub: http://github.com/chronossc/ | Twitter: http://twitter.com/chronossc
LinkedIn: http://br.linkedin.com/in/felipeprenholato/

2016-09-23 13:36 GMT-03:00 Tim Graham :

> Not sure, this is using lots of private API and QuerySet.extra() which is
> discourage these days. I'm afraid you won't get much help for unsupported
> Django versions.
>
> After you upgrade to Django 1.9+, hopefully you can construct your query
> using the new expressions API!
>
> https://docs.djangoproject.com/en/stable/ref/models/expressions/
>
>
> On Friday, September 23, 2016 at 11:40:04 AM UTC-4, Felipe 'chronos'
> Prenholato wrote:
>>
>> Hello folks!
>>
>> I have a complex queryset here that do joins in at least 8 tables. The
>> query is fast, but has use of Postgresql `concat` method, and to make it
>> have correct aliases I'm using `qs.query.alias_map` where qs is my queryset
>> instance:
>>
>> def my_complex_queryset(self):
>> qs = self.filter(*lot_of_stuff_with_lot_of_joins)
>> alias_map = {
>> j.table_name: a for a, j in qs.query.alias_map.items()
>> if j.table_name in ['table_a', 'table_b']
>> }
>> concat_str = (
>> 'concat("{table_a}"."city", "{table_a}"."state") '
>> '!= concat("{table_b}"."city", "{table_b}"."state")'
>> ).format(
>> table_a=alias_map['table_a'],
>> table_b=alias_map['table_b']
>> )
>> qs = qs.extra(where=[concat_str])
>> return qs
>>
>> The problem is when the query is re-executed with some other table in the
>> joins because it will change the alias_map but won't run my method again
>> to  update the concat_str:
>>
>> my_qs = MyModel.objects.my_complex_queryset()
>> print my_qs
>> # now it will fail because alias changed:
>> print my_qs.filter(another_table_1__another_table_2__id__in=[1, 2, 3])
>>
>> The error I receive is:
>>
>> * ProgrammingError: invalid reference to FROM-clause entry for table
>> "table_a"*
>> *LINE 1: ... AND U0."some_id" IN (13361, 9820) AND (concat("table_a...*
>> * ^*
>> *HINT:  Perhaps you meant to reference the table alias "u9".*
>>
>> The great question is: Is possible to do the query with dynamic aliases
>> to be used in concat string? Maybe the `tables` argument to extra? I not
>> saw much doc about it :\.
>>
>> My Django version is 1.6.x, not because my choice but because software
>> here still a bit legacy about Django versions and we won't change until
>> January.
>>
>> Thanks for your attention!
>>
>> Felipe Prenholato.
>> Home page: http://devwithpassion.com | http://chronosbox.org/blog
>> GitHub: http://github.com/chronossc/ | Twitter: http://twitter.com/ch
>> ronossc
>> LinkedIn: http://br.linkedin.com/in/felipeprenholato/
>>
> --
> 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/77febd31-5b71-4ce2-a794-dab2f7c5e3e7%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/CADdUj3HxrWLJai5_wQaKWBKtBOi%2B9d_zZwCi0%3DPUm8yps_-gLA%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.

Re: Complex query, queryset.query.alias_map and query changes

[Tim Graham]

Not sure, this is using lots of private API and QuerySet.extra() which is 
discourage these days. I'm afraid you won't get much help for unsupported 
Django versions.

After you upgrade to Django 1.9+, hopefully you can construct your query 
using the new expressions API!

https://docs.djangoproject.com/en/stable/ref/models/expressions/

On Friday, September 23, 2016 at 11:40:04 AM UTC-4, Felipe 'chronos' 
Prenholato wrote:
>
> Hello folks!
>
> I have a complex queryset here that do joins in at least 8 tables. The 
> query is fast, but has use of Postgresql `concat` method, and to make it 
> have correct aliases I'm using `qs.query.alias_map` where qs is my queryset 
> instance:
>
> def my_complex_queryset(self):
> qs = self.filter(*lot_of_stuff_with_lot_of_joins)
> alias_map = {
> j.table_name: a for a, j in qs.query.alias_map.items() 
> if j.table_name in ['table_a', 'table_b']
> }
> concat_str = (
> 'concat("{table_a}"."city", "{table_a}"."state") '
> '!= concat("{table_b}"."city", "{table_b}"."state")'
> ).format(
> table_a=alias_map['table_a'],
> table_b=alias_map['table_b']
> )
> qs = qs.extra(where=[concat_str])
> return qs
>
> The problem is when the query is re-executed with some other table in the 
> joins because it will change the alias_map but won't run my method again 
> to  update the concat_str:
>
> my_qs = MyModel.objects.my_complex_queryset()
> print my_qs
> # now it will fail because alias changed:
> print my_qs.filter(another_table_1__another_table_2__id__in=[1, 2, 3])
>
> The error I receive is:
>
> * ProgrammingError: invalid reference to FROM-clause entry for table 
> "table_a"*
> *LINE 1: ... AND U0."some_id" IN (13361, 9820) AND (concat("table_a...*
> * ^*
> *HINT:  Perhaps you meant to reference the table alias "u9".*
>
> The great question is: Is possible to do the query with dynamic aliases to 
> be used in concat string? Maybe the `tables` argument to extra? I not saw 
> much doc about it :\.
>
> My Django version is 1.6.x, not because my choice but because software 
> here still a bit legacy about Django versions and we won't change until 
> January.
>
> Thanks for your attention!
>
> Felipe Prenholato.
> Home page: http://devwithpassion.com | http://chronosbox.org/blog
> GitHub: http://github.com/chronossc/ | Twitter: 
> http://twitter.com/chronossc
> LinkedIn: http://br.linkedin.com/in/felipeprenholato/
>

-- 
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/77febd31-5b71-4ce2-a794-dab2f7c5e3e7%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Complex query, queryset.query.alias_map and query changes

[Felipe Prenholato]

Hello folks!

I have a complex queryset here that do joins in at least 8 tables. The
query is fast, but has use of Postgresql `concat` method, and to make it
have correct aliases I'm using `qs.query.alias_map` where qs is my queryset
instance:

def my_complex_queryset(self):
qs = self.filter(*lot_of_stuff_with_lot_of_joins)
alias_map = {
j.table_name: a for a, j in qs.query.alias_map.items()
if j.table_name in ['table_a', 'table_b']
}
concat_str = (
'concat("{table_a}"."city", "{table_a}"."state") '
'!= concat("{table_b}"."city", "{table_b}"."state")'
).format(
table_a=alias_map['table_a'],
table_b=alias_map['table_b']
)
qs = qs.extra(where=[concat_str])
return qs

The problem is when the query is re-executed with some other table in the
joins because it will change the alias_map but won't run my method again to
 update the concat_str:

my_qs = MyModel.objects.my_complex_queryset()
print my_qs
# now it will fail because alias changed:
print my_qs.filter(another_table_1__another_table_2__id__in=[1, 2, 3])

The error I receive is:

* ProgrammingError: invalid reference to FROM-clause entry for table
"table_a"*
*LINE 1: ... AND U0."some_id" IN (13361, 9820) AND (concat("table_a...*
* ^*
*HINT:  Perhaps you meant to reference the table alias "u9".*

The great question is: Is possible to do the query with dynamic aliases to
be used in concat string? Maybe the `tables` argument to extra? I not saw
much doc about it :\.

My Django version is 1.6.x, not because my choice but because software here
still a bit legacy about Django versions and we won't change until January.

Thanks for your attention!

Felipe Prenholato.
Home page: http://devwithpassion.com | http://chronosbox.org/blog
GitHub: http://github.com/chronossc/ | Twitter: http://twitter.com/chronossc
LinkedIn: http://br.linkedin.com/in/felipeprenholato/

-- 
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/CADdUj3FCZxubjeUBW%3DcbxXqBLn5BAXwNp0Yyvczb1QpQb3%3DB3w%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.

Re: Django model complex query ( use table A.column1 as parameter to query result from table B)

[Vijay Khemlani]

I think you are trying to establish a many-to-many relationship between the
models, that way it would be like this

poiRes = models.ManyToManyField(POIS)

and the candidatePois is unnecessary in that case.



On Tue, Oct 21, 2014 at 3:16 PM, zhang rock  wrote:

> Hi All,
>
> I have a question: how to use table A.column1 as parameter to query result
> from table B?
>
> I have two tables :
>
> 1. UserStay
> idcandidatePoiIdsselectedPoiId
> 1101,102,103  100
>
>
> 2. POIs
> id  name  address
> 100   starbuck 100 main st,
> 101   mcdonalds  101 main st,
>
> i want to get all candiate POI's name and address when i fetch User stays,
> the following code does not work , i also tried Manager with raw SQL, but i
> dont' know how to pass the candidatePois to manager, can i get the string
> value of "candidatePois"  in model ?
>
> class UserStays(models.Model):
> startTime = models.IntegerField('startTime', max_length=255)
> candidatePois = models.CharField('CharField', max_length=255)
> poiRes = POIS.objects.filter(id in F('*candidatePois*') )
> // poiRes = POIS.objects.filter(id = 100 )
> //print poiRes.name + poiRes.address
>
>
> Thanks
> Rock
>
>  --
> 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 http://groups.google.com/group/django-users.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/django-users/722f80db-4069-4e30-a94a-c7d58a9c6c3d%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 http://groups.google.com/group/django-users.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-users/CALn3ei2hyGsgRNH2MGGjZ2J47LYLU%3Dc800_b2XTU0teHM_5aMw%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.

Django model complex query ( use table A.column1 as parameter to query result from table B)

[zhang rock]

Hi All,

I have a question: how to use table A.column1 as parameter to query result 
from table B?

I have two tables : 
 
1. UserStay
idcandidatePoiIdsselectedPoiId
1101,102,103  100


2. POIs
id  name  address
100   starbuck 100 main st, 
101   mcdonalds  101 main st, 

i want to get all candiate POI's name and address when i fetch User stays, 
the following code does not work , i also tried Manager with raw SQL, but i 
dont' know how to pass the candidatePois to manager, can i get the string 
value of "candidatePois"  in model ?

class UserStays(models.Model): 
startTime = models.IntegerField('startTime', max_length=255)
candidatePois = models.CharField('CharField', max_length=255)
poiRes = POIS.objects.filter(id in F('*candidatePois*') )
// poiRes = POIS.objects.filter(id = 100 )
//print poiRes.name + poiRes.address


Thanks
Rock

-- 
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 http://groups.google.com/group/django-users.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-users/722f80db-4069-4e30-a94a-c7d58a9c6c3d%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

How to convert SQL Complex QUERY to django QUERY

[Shoaib Ijaz]

I am trying to convert sql query to django query but failed to do this, can 
anyone help me

select id,name,round(value::numeric,2) as value, st_transform(geometry, 3857) 
as geometry
from net_rest
where state_id in (1,2) and name = 'height'
union
(select d.id,d.restriction,d.value, st_transform(d.geometry, 3857) as 
geometry from display_rest d
where d.restriction='height' and condition_id not in (select 
condition_id 
from net_rest_conditions where version_id = 2)

OR This

select id,name,value as value, geometry
from net_rest
where state_id in (1,2) and name = 'height'
union
(select d.id,d.restriction,d.value,geometry from display_rest d
where d.restriction='height' and condition_id not in (select 
condition_id 
from net_rest_conditions where version_id = 2)

*Updated the question fro here*

I am using django django rest framework serialize *net_rest* Model, 
basically i am working on project related to GIS where i have to make rest 
api to expose data

Here is some of my models

class net_rest(models.Model):
name = models.CharField(max_length=50, blank=True)
value = models.FloatField()
state_id = models.IntegerField(null=True, blank=True)
geometry = models.GeometryField(null=True, blank=True)
objects = models.GeoManager()

class Meta:
db_table = u'tbl_net_rest'

def __unicode__(self):
return '%s' % self.name


class display_rest(models.Model):
restriction = models.CharField(max_length=45, blank=True)
link_id = models.IntegerField(blank=True, null=True)
condition_id = models.IntegerField(blank=True, null=True)
value = models.FloatField(blank=True, null=True)
geometry = models.GeometryField(blank=True, null=True)
class Meta:
db_table = u'tbl_display_rest'


class net_rest_conditions(models.Model):
condition_id = models.IntegerField()
version_id =  models.IntegerField(blank=True, null=True)
class Meta:
db_table = u'tbl_net_rest_conditions'
class networkSerializer(serializers.GeoModelSerializer):
class Meta:
model = net_rest
fields = ('id', 'name', 'value', 'geometry')

Here is view

class networkSerializerViewSet(viewsets.ModelViewSet):

q1 = display_rest.objects.values_list('id', 'name', 'value', 
'geometry').filter(restriction='height')\

.exclude(condition_id__in=net_rest_conditions.objects.filter(version_id=2).values_list('condition_id',flat=True))

q2 = net_rest.objects.all().filter(Q(name="height"), Q(state_id=1) | 
Q(state_id=2)).values_list('id', 'value', 'geometry')

queryset = q1 | q2

serializer_class = networkSerializer

-- 
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 http://groups.google.com/group/django-users.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-users/6e137e70-e88d-49bb-b000-e6174aae3837%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Re: Complex query reduction

[Javier Guerra Giraldez]

On Fri, Nov 8, 2013 at 1:44 AM, Robin St.Clair  wrote:
> The last time I checked the use of IN, all the records from the database in
> the query were brought back to the workstation, rather than being processed
> on the backend and only the results returned to the workstation.


Django ORM's __in operator tries to use SQL facilities, but depending
on the type of object passed, it could read the list of options and
pass it explicitly, or it could merge two SQL commands.  always check
the type of SQL commands generated!  (debug toolbar is your friend)

i haven't seen it doing a post-retrieve check in python, thought.

-- 
Javier

-- 
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 http://groups.google.com/group/django-users.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-users/CAFkDaoRLfeLi%3DWVu5f%3DcyCXRH0x5mr1fbDiV0OzW%2Bn-fpk6uHw%40mail.gmail.com.
For more options, visit https://groups.google.com/groups/opt_out.

Re: Complex query reduction

[François Schiettecatte]

I am pretty sure the IN() performance issue in MySQL was fixed 5.5, and  
5.6/5.7 certainly don't have an issue with IN() whether you use a bunch of 
values or a subquery.

Cheers

François

On Nov 8, 2013, at 2:15 AM, akaariai  wrote:

> On Friday, November 8, 2013 8:44:09 AM UTC+2, Robin St.Clair wrote:
> Anssi
> 
> The last time I checked the use of IN, all the records from the database in 
> the query were brought back to the workstation, rather than being processed 
> on the backend and only the results returned to the workstation.
> 
> Have there been changes that carry out the entire query on the backend? What 
> has changed to cause you to prefer the use of the IN statement?
> 
> Django has had the ability to execute __in=qs in single query for a somewhat 
> long time (from Django 1.2 maybe?).
> 
> It is true that __in lookup against a large list of values is often a bad 
> choice even if you have the values at hand. Unfortunately this doesn't apply 
> to all databases, for example older versions of MySQL do not handle 
> subqueries well.
> 
>  - Anssi
> 
> -- 
> 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 http://groups.google.com/group/django-users.
> To view this discussion on the web visit 
> https://groups.google.com/d/msgid/django-users/f3428fda-e4be-4d75-8626-a95bd20f66c8%40googlegroups.com.
> For more options, visit https://groups.google.com/groups/opt_out.



signature.asc
Description: Message signed with OpenPGP using GPGMail

Re: Complex query reduction

[akaariai]

On Friday, November 8, 2013 8:44:09 AM UTC+2, Robin St.Clair wrote:
>
>  Anssi
>
> The last time I checked the use of IN, all the records from the database 
> in the query were brought back to the workstation, rather than being 
> processed on the backend and only the results returned to the workstation.
>
> Have there been changes that carry out the entire query on the backend? 
> What has changed to cause you to prefer the use of the IN statement?
>

Django has had the ability to execute __in=qs in single query for a 
somewhat long time (from Django 1.2 maybe?).

It is true that __in lookup against a large list of values is often a bad 
choice even if you have the values at hand. Unfortunately this doesn't 
apply to all databases, for example older versions of MySQL do not handle 
subqueries well.

 - Anssi

-- 
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 http://groups.google.com/group/django-users.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-users/f3428fda-e4be-4d75-8626-a95bd20f66c8%40googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

Re: Complex query reduction

[Robin St . Clair]

Anssi

The last time I checked the use of IN, all the records from the database 
in the query were brought back to the workstation, rather than being 
processed on the backend and only the results returned to the workstation.


Have there been changes that carry out the entire query on the backend? 
What has changed to cause you to prefer the use of the IN statement?


R+C
On 08/11/2013 05:55, akaariai wrote:

On Sunday, November 3, 2013 1:48:07 PM UTC+2, Robin St.Clair wrote:

*IN*

  * if using Django avoid the IN operation at all costs


If there are potentially more than 15 items in the list, rework
the IN as a JOIN against whatever the source of the keys is


I don't necessarily agree with everything else said in the post, but 
this one is just plain wrong. It is completely OK to use 
__in=queryset. In fact, it is recommended in cases where the alternate 
is using join + distinct.


 - Anssi
--
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 http://groups.google.com/group/django-users.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-users/6b5f9e3a-2e41-47b5-b0d6-94473fe323b8%40googlegroups.com.

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


--
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 http://groups.google.com/group/django-users.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-users/BLU0-SMTP357C2324CC6BEC3F975EB4AE2F20%40phx.gbl.
For more options, visit https://groups.google.com/groups/opt_out.

Re: Complex query reduction

[akaariai]

On Sunday, November 3, 2013 1:48:07 PM UTC+2, Robin St.Clair wrote:
>
> *IN*
>
>- if using Django avoid the IN operation at all costs
>
>
> If there are potentially more than 15 items in the list, rework the IN as 
> a JOIN against whatever the source of the keys is
>

I don't necessarily agree with everything else said in the post, but this 
one is just plain wrong. It is completely OK to use __in=queryset. In fact, 
it is recommended in cases where the alternate is using join + distinct.

 - Anssi 

-- 
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 http://groups.google.com/group/django-users.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-users/6b5f9e3a-2e41-47b5-b0d6-94473fe323b8%40googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

Re: Complex query reduction

[Apostolos Bessas]

On Sat, Nov 2, 2013 at 4:50 PM, Daniele Procida  wrote:
>
> But, the real killer is the combination of ordering (in the queryset or on 
> the model, it doesn't matter) with the distinct() - as soon as one is removed 
> from the equation, the execution time drops to around 250ms.
>
> That's for 55000 BibliographicRecords created by that last operation (before 
> distinct() is applied; distinct() reduces them to 28000).


Do you happen to use PostgreSQL? This could be a case of a
"non-optimal" configuration, that makes PostgreSQL use the disk to do
the sorting. Take a look at
http://www.postgresql.org/docs/current/static/runtime-config-resource.html#GUC-WORK-MEM.

Apostolis

-- 
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 http://groups.google.com/group/django-users.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-users/CAEa3b%2BoCFp3oPwtcR-uyoERW3YwHR4aXLubp7WUpa%2BHtYNhvmw%40mail.gmail.com.
For more options, visit https://groups.google.com/groups/opt_out.

RE: Complex query reduction

[Robin St . Clair]

Hi
You are running into typical production Django problems - works fine in 
testing, terrible in production.
Firstly
If you use attributed many-to-manys, create the table outside of Django and use 
raw SQL.Create a primary key on the two things being related (usually larger 
table first).
Multi-column indicesTwo choicesA single index that includes both (do this 
outside Django)Two indices, one for each columnThe size will be roughly 
equivalent between the two
Single composite indexsmaller than two indexes.faster to update.accelerates a 
more restricted range of queries.An index on (A, B) does nothing for queries 
just using BBut on the queries it does accelerate, it will be faster.
Two indiceslargerslower to updatewill help on queries on both columns, but not 
as much as a single composite indexwill help accelerate queries on either of 
the columnscan be expressed directly in Django
Helpful Toolpg_stat_activity shows a wealth of useful information - including
which indexes are actually being used
If an index is not being used (or not being used very often) drop itif you are 
surprised that it is not being used, find out why
Basic Performance Rules1. Do not iterate over QuerySets2. If you think you have 
to iterate over a QuerySet, see rule #13. If you are absolutely, certainly, 
100% positive that the only possible solution to your problem is iterating over 
a QuerySet, see rule #3
Iteration - A Health Warning• Ignores Django’s lazy-evaluation mechanism and 
copies everything into local memory• Copies data from the database just to 
process it locally• Does filtration or summation in the application that is 
processed more efficiently in the database.• Databases are good at this sort of 
stuff let the DB do it
Alternatives to Iteration• QuerySet.update()• cursor.execute(“UPDATE 
reader_hours ...”)• Stored procedures
Take in the code below
How much many objects are in memory at point A?
qs = Orders.objects.all() # There are about 2,500,000 rows in “orders”
for order in qs:order.age_in_days += 1 # POINT A order.save()
Answer  - 2,500,000
Why Is This?
Django does lazy evaluation… (everyone tells me so!)The Django code carefully 
asks for a slice of 100 objectswhich trickles down through lots of really 
convoluted Python to psycopg2which dutifully asks for 100 rows from 
Postgreswhich sends all 2,500,000 over the wire
Solution - Named CursorsThe protocol between the Postgres client and server 
only does partial sends when using named cursorspsycopg2 fully supports named 
cursorsDjango doesn’t use named cursorsTherefore, the first time you ask for any 
object in a QuerySet, you get all of themThis is a very good reason not to ask 
for large result sets
INif using Django avoid the IN operation at all costs
If there are potentially more than 15 items in the list, rework the IN as a 
JOIN against whatever the source of the keys is

At some stage you are going to have to get to grips with SQL, might as well be 
sooner than later
R+C
PS some time ago one of my clients was only able to process 4 transactons a 
second against their very complicated DB. They needed to process 70+ 
transactions per second. After 3 days of code analysis we had the transaction 
rate up to 240 per second and so could get rid of the 6 week backlog that had 
developed. Front end coders didn't consider the DBMS to be importantDate: Sun, 
3 Nov 2013 02:37:09 -0800
From: akaar...@gmail.com
To: django-users@googlegroups.com
Subject: Re: Complex query reduction

You should rewrite the query into a form that doesn't require distinct. In 
general, when you see a query that has joins and DISTINCT, that should be an 
alarm bell that something isn't written correctly in the query. Unfortunately 
Django's ORM generates such queries, and that isn't easy to fix as there are 
databases that like DISTINCT more than rewriting the query to use subqueries.

In any case, you should check how to write the query without the need of 
distinct. Something like this should work:

self.items = BibliographicRecord.objects.listable_objects().filter(

authored__researcher__in=researchers,

)

self.items = BibliographicRecord.objects.listable_objects().filter(

pk__in=self.items.values_list('pk')

)

But maybe you can push the __in to deeper into the authored__researches 
lookup...

 - Anssi

On Saturday, November 2, 2013 4:50:42 PM UTC+2, Daniele Procida wrote:On Fri, 
Nov 1, 2013, Javier Guerra Giraldez <jav...@guerrag.com> wrote:



>have you tried eliminating the second IN relationship?  something like

>

>entities = entity.get_descendants()

>

>items = BibliographicRecord.objects.filter

>(authored__researcher__person__member_of__entity__in=entities).distinct()



Indeed I have, but in that form it takes around 1770ms, compared to around 
1540ms in the original form. What I actually do is:



# breaking apart the queries allows the use of values_lists

entities = self.entity.get_descendants(

 

Re: Complex query reduction

[akaariai]

You should rewrite the query into a form that doesn't require distinct. In 
general, when you see a query that has joins and DISTINCT, that should be 
an alarm bell that something isn't written correctly in the query. 
Unfortunately Django's ORM generates such queries, and that isn't easy to 
fix as there are databases that like DISTINCT more than rewriting the query 
to use subqueries.

In any case, you should check how to write the query without the need of 
distinct. Something like this should work:

self.items = BibliographicRecord.objects.
listable_objects().filter( 
authored__researcher__in=researchers, 
)
self.items = BibliographicRecord.objects.
listable_objects().filter( 
pk__in=self.items.values_list('pk') 
)

But maybe you can push the __in to deeper into the authored__researches 
lookup...

 - Anssi

On Saturday, November 2, 2013 4:50:42 PM UTC+2, Daniele Procida wrote:
>
> On Fri, Nov 1, 2013, Javier Guerra Giraldez  
> wrote: 
>
> >have you tried eliminating the second IN relationship?  something like 
> > 
> >entities = entity.get_descendants() 
> > 
> >items = BibliographicRecord.objects.filter 
> >(authored__researcher__person__member_of__entity__in=entities).distinct() 
>
> Indeed I have, but in that form it takes around 1770ms, compared to around 
> 1540ms in the original form. What I actually do is: 
>
> # breaking apart the queries allows the use of values_lists 
> entities = self.entity.get_descendants( 
> include_self=True 
> ).values_list('id', flat=True) 
>
> # and the set() here is about 230ms faster than putting a distinct() 
> on 
> # the first query 
> researchers = set(Researcher.objects.filter( 
> person__entities__in=entities 
> ).values_list('person', flat=True)) 
>
> self.items = BibliographicRecord.objects.listable_objects().filter( 
> authored__researcher__in=researchers, 
> ).distinct() 
>
> I think that's partly because this way the SELECT doesn't have to grab all 
> the fields of publications_bibliographicrecord. 
>
> But, the real killer is the combination of ordering (in the queryset or on 
> the model, it doesn't matter) with the distinct() - as soon as one is 
> removed from the equation, the execution time drops to around 250ms. 
>
> That's for 55000 BibliographicRecords created by that last operation 
> (before distinct() is applied; distinct() reduces them to 28000). 
>
> That seems excessive to me. 
>
> BibliographicRecord has a custom primary key, and its id fields look like 
> "d9ce7e2f-663e-4fc6-8448-b214c6915aed:web-of-science". Could that be 
> implicated in performance? 
>
> Daniele 
>
>

-- 
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 http://groups.google.com/group/django-users.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-users/8208cc22-cc94-4fe6-9245-709bdc42647f%40googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

Re: Complex query reduction

[Daniele Procida]

On Fri, Nov 1, 2013, Javier Guerra Giraldez  wrote:

>have you tried eliminating the second IN relationship?  something like
>
>entities = entity.get_descendants()
>
>items = BibliographicRecord.objects.filter
>(authored__researcher__person__member_of__entity__in=entities).distinct()

Indeed I have, but in that form it takes around 1770ms, compared to around 
1540ms in the original form. What I actually do is:

# breaking apart the queries allows the use of values_lists
entities = self.entity.get_descendants(
include_self=True
).values_list('id', flat=True)

# and the set() here is about 230ms faster than putting a distinct() on 
# the first query
researchers = set(Researcher.objects.filter(
person__entities__in=entities
).values_list('person', flat=True))

self.items = BibliographicRecord.objects.listable_objects().filter(
authored__researcher__in=researchers,
).distinct()

I think that's partly because this way the SELECT doesn't have to grab all the 
fields of publications_bibliographicrecord.

But, the real killer is the combination of ordering (in the queryset or on the 
model, it doesn't matter) with the distinct() - as soon as one is removed from 
the equation, the execution time drops to around 250ms.

That's for 55000 BibliographicRecords created by that last operation (before 
distinct() is applied; distinct() reduces them to 28000).

That seems excessive to me. 

BibliographicRecord has a custom primary key, and its id fields look like 
"d9ce7e2f-663e-4fc6-8448-b214c6915aed:web-of-science". Could that be implicated 
in performance?

Daniele

-- 
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 http://groups.google.com/group/django-users.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-users/20131102145042.868061817%40smtp.modern-world.net.
For more options, visit https://groups.google.com/groups/opt_out.

Re: Complex query reduction

[Javier Guerra Giraldez]

On Fri, Nov 1, 2013 at 12:45 PM, Daniele Procida  wrote:
> In practice I use some tweaks (such as values_list) to speed this up, and 
> caching, but the fundamental pattern is the same. It's slow, because there 
> are 30 thousand BibliographicRecords.


the total number of records shouldn't matter.  more important is the
number of selected records at some points in the query.  i'd guess the
number of chosen entities (those that _are_ descendant of the first
one), and the number of chosen researches (those that _are_ members of
those entities) should be the most significant quantities.

the point is that most DB optimizers are somewhat shy to shuffle
conditions around IN operators.  but it might be easier to work on a
small (a few tens of values?) IN condition than on a very long JOIN
chain.

>
> I'm trying to rearrange the construction of the query in different ways to 
> find a speed improvement. I don't think that either prefetch_related or 
> select_related will help here, but perhaps there are some other tools that 
> would.


have you tried eliminating the second IN relationship?  something like

entities = entity.get_descendants()

items = 
BibliographicRecord.objects.filter(authored__researcher__person__member_of__entity__in=entities).distinct()

that should turn most of the query in a long sequence of "INNER JOIN",
giving the optimizer an easier job, avoiding the IN operation on
research records.  if the query chooses too many researches, this
could be a big part of the slowness.

-- 
Javier

-- 
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 http://groups.google.com/group/django-users.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-users/CAFkDaoSVg3K44wRUnOTrinqXZbVKm1ZZLHxFPB8LdgrzBtxViA%40mail.gmail.com.
For more options, visit https://groups.google.com/groups/opt_out.

Complex query reduction

[Daniele Procida]

I have been exploring a rather complex query:

# get all the MPTT descendants of entity
entities = entity.get_descendants()
 
# get all the Researchers in these Entities
researchers = Researcher.objects.filter(person__member_of__entity__in=entities)
 
# get all the BibliographicRecords for these Researchers
items = 
BibliographicRecord.objects.filter(authored__researcher__in=researchers).distinct()

In practice I use some tweaks (such as values_list) to speed this up, and 
caching, but the fundamental pattern is the same. It's slow, because there are 
30 thousand BibliographicRecords.

I'm trying to rearrange the construction of the query in different ways to find 
a speed improvement. I don't think that either prefetch_related or 
select_related will help here, but perhaps there are some other tools that 
would.

Any suggestions?

At <https://dpaste.de/8vwP> I've shown the SQL generated, and the models being 
used.

Thanks,

Daniele

-- 
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 http://groups.google.com/group/django-users.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-users/20131101174524.207157947%40smtp.modern-world.net.
For more options, visit https://groups.google.com/groups/opt_out.

Re: Complex query

[Drew Ferguson]

On Mon, 9 Sep 2013 06:57:46 -0700 (PDT)
Yegor Roganov  wrote:

> Thanks for you replies.
> But I wonder how real production web sites deal with this problem. The 
> problem is that I want to display about 30 topics per page which would 
> result in 30+1 queries if implemented naively. If I write the required 
> query in raw SQL, it should be faster, but probably not very fast
> anyway. Also I don't know how to cache properly since topics should be
> ordered by most recent posts (that is, if someone posts to topic on
> third page, this topic should become the first one on the first page).
> 

If you were using Postgres or similar, you could create some triggers
and/or stored-procedures that maintained an active list of 30 target
items - perhaps with a flag in one of the database tables. Then create a
view to generate the list of 30 items you needed.

This way the ORM & Django SQL access is reduced to a single query on the
view. The heavy lifting is all done server-side as comments/articles are
saved

Could this work?
-- 
Drew Ferguson

-- 
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 http://groups.google.com/group/django-users.
For more options, visit https://groups.google.com/groups/opt_out.

Re: Complex query

[Yegor Roganov]

Thanks for you replies.
But I wonder how real production web sites deal with this problem. The 
problem is that I want to display about 30 topics per page which would 
result in 30+1 queries if implemented naively. If I write the required 
query in raw SQL, it should be faster, but probably not very fast anyway. 
Also I don't know how to cache properly since topics should be ordered by 
most recent posts (that is, if someone posts to topic on third page, this 
topic should become the first one on the first page).

-- 
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 http://groups.google.com/group/django-users.
For more options, visit https://groups.google.com/groups/opt_out.

Re: Complex query

[akaariai]

On Monday, September 9, 2013 12:13:09 AM UTC+3, Yegor Roganov wrote:
>
> I found out this is a so called "top n per group" problem and it's not 
> that easy to implement in django. Maybe someone could help me to find 
> another way (for example, alter the schema in some way)?
>

This is something that would be nice to support with prefetch_related(). 
This is exactly the type of query where ORM abstraction would be very 
welcome. Writing this in SQL isn't easy, and the most efficient way to 
write the query varies a lot depending on the used database backend.

Unfortunately I don't see other solutions than running multiple queries or 
using raw SQL.

 - Anssi


-- 
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 http://groups.google.com/group/django-users.
For more options, visit https://groups.google.com/groups/opt_out.

Re: Complex query

[Jani Tiainen]

Problem is that you have to be able to express it in SQL. And thus ther is not 
much you can do in SQL to get what you actually wanted.

Considering amount of the data fetched - it's relatively low query count and 
unless you're hitting very busy site you wont notice much of difference doing 
this in 6 queries.

One to fetch last 5 topics and then 5 to get that 20 latest posts per topic.

When you later may hit really busy site you can cache list for a short time to 
relieve load on db.

On Sun, 8 Sep 2013 14:13:09 -0700 (PDT)
Yegor Roganov  wrote:

> I found out this is a so called "top n per group" problem and it's not that 
> easy to implement in django. Maybe someone could help me to find another 
> way (for example, alter the schema in some way)?
> 
> -- 
> 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 http://groups.google.com/group/django-users.
> For more options, visit https://groups.google.com/groups/opt_out.

-- 
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 http://groups.google.com/group/django-users.
For more options, visit https://groups.google.com/groups/opt_out.

Re: Complex query

[Yegor Roganov]

I found out this is a so called "top n per group" problem and it's not that 
easy to implement in django. Maybe someone could help me to find another 
way (for example, alter the schema in some way)?

-- 
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 http://groups.google.com/group/django-users.
For more options, visit https://groups.google.com/groups/opt_out.

Re: Complex query

[Nick Apostolakis]

On 08/09/2013 08:17 μμ, Yegor Roganov wrote:

I'm developing a forum and my db schema  is roughly equivalent to the
following:
class Topic(models.Model):
 head = models.CharField()
 body = models.TextField()
 created = models.DateTimeField(auto_now_add=True)

class Post(models.Model):
 body = models.TextField()
 topic = models.ForeignKey(Topic)
 created = models.DateTimeField(auto_now_add=True)

The question is: how to construct a query to fetch 20 latest Topics with 5
latest Posts for each of these Topics ? As I understood, prefetch_related
isn't suitable since it cannot limit the number of related objects.


It looks like you are looking for a subquery

maybe this will be useful

http://stackoverflow.com/questions/8217490/query-of-a-subquery-in-django

--
 --
 Nick Apostolakis
 Msc in IT, University of Glasgow
 e-mail: nicka...@oncrete.gr
 Web Site: http://nick.oncrete.gr
 --

--
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 http://groups.google.com/group/django-users.
For more options, visit https://groups.google.com/groups/opt_out.

Complex query

[Yegor Roganov]

I'm developing a forum and my db schema  is roughly equivalent to the 
following: 
class Topic(models.Model):
head = models.CharField()
body = models.TextField()
created = models.DateTimeField(auto_now_add=True)

class Post(models.Model):
body = models.TextField()
topic = models.ForeignKey(Topic)
created = models.DateTimeField(auto_now_add=True)

The question is: how to construct a query to fetch 20 latest Topics with 5 
latest Posts for each of these Topics ? As I understood, prefetch_related 
isn't suitable since it cannot limit the number of related objects.

-- 
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 http://groups.google.com/group/django-users.
For more options, visit https://groups.google.com/groups/opt_out.

How do I construct a queryset for this complex query?

[Devin]

I've got a complex query that uses a combination of Q objects and
qargs to produce the following query:

Note the T4 alias...this is central to the question.

SELECT
`t_answer`.`id`,
`t_answer`.`question_id`,
`t_answer`.`survey_id`,
`t_answer`.`content_type_id`,
`t_intanswer`.`answer_ptr_id`,
`t_intanswer`.`value`
FROM
`t_intanswer`
INNER JOIN `t_answer` ON (`t_intanswer`.`answer_ptr_id` =
`t_answer`.`id`)
INNER JOIN `t_survey` ON (`t_answer`.`survey_id` = `t_survey`.`id`)
INNER JOIN `t_answer` T4 ON (`t_survey`.`id` = T4.`survey_id`)
INNER JOIN `t_booleananswer` ON (T4.`id` =
`t_booleananswer`.`answer_ptr_id`)
INNER JOIN `t_choiceanswer` ON (T4.`id` =
`t_choiceanswer`.`answer_ptr_id`)
INNER JOIN `t_facility` ON (`t_survey`.`facility_id` =
`t_facility`.`id`)
WHERE
(
(
(`t_booleananswer`.`value` = True  AND T4.`question_id` =
1742) AND
(`t_choiceanswer`.`choice_id` = 1947  AND T4.`question_id` =
1743 )
) AND
`t_survey`.`public` = False  AND
`t_survey`.`facility_id` = 82  AND
`t_survey`.`doctor_id` = 124  AND
`t_answer`.`question_id` = 1753  AND
`t_facility`.`program_id` = 71  AND
`t_survey`.`completed` IS NOT NULL AND
`t_survey`.`flagged` = False  AND
`t_survey`.`completed` < '2012-05-20 00:00:00'  AND
`t_survey`.`completed` > '2012-05-18 00:00:00' )


I'm looking for a result that contains either a boolean or choice
answer, but I've used Django's model inheritance, and both tables are
trying to join through t_answer.

Only one t_answer row can be returned for this query, so I get back
zero results.  But I need one result.  Here is the modified query in
raw SQL that solves my problem.  I create a second FROM clause using
the alias T5, then I join choice on that.   Works great.


SELECT
`t_answer`.`id`,
`t_answer`.`question_id`,
`t_answer`.`survey_id`,
`t_answer`.`content_type_id`,
`t_intanswer`.`answer_ptr_id`,
`t_intanswer`.`value`
FROM
`t_intanswer`
INNER JOIN `t_answer` ON (`t_intanswer`.`answer_ptr_id` =
`t_answer`.`id`)
INNER JOIN `t_survey` ON (`t_answer`.`survey_id` = `t_survey`.`id`)
INNER JOIN `t_answer` T4 ON (`t_survey`.`id` = T4.`survey_id`)
INNER JOIN `t_answer` T5 ON (`t_survey`.`id` = T5.`survey_id`)
INNER JOIN `t_booleananswer` ON (T4.`id` =
`t_booleananswer`.`answer_ptr_id`)
INNER JOIN `t_choiceanswer` ON (T5.`id` =
`t_choiceanswer`.`answer_ptr_id`)
INNER JOIN `t_facility` ON (`t_survey`.`facility_id` =
`t_facility`.`id`)
WHERE
(
(
(`t_booleananswer`.`value` = True  AND T4.`question_id` =
1742) AND
(`t_choiceanswer`.`choice_id` = 1947  AND T5.`question_id` =
1743 )
) AND
`t_survey`.`public` = False  AND
`t_survey`.`facility_id` = 82  AND

Cool...so I have my solution---except that I don't know how to
construct it using the Django queryset.  I looked at "extra" (https://
docs.djangoproject.com/en/1.3/ref/models/querysets/#extra) but I'm not
clear on how to use it accomplish my second INNER JOIN T5.

Any thoughts experts?  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-users@googlegroups.com.
To unsubscribe from this group, send email to 
django-users+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en.

Re: Need help on a (maybe complex) query with aggregate/annotate

[Enrico]

Works like a charm.

Thanks!

Ciao
Enrico

On Feb 21, 11:06 pm, Michael Elkins  wrote:
> On Tue, Feb 21, 2012 at 01:10:28PM -0800, Enrico wrote:
> >But your query counts all the books, even the bad ones. I only need to
> >count the good ones...
>
> >For example, if my books are:
>
> >1. name: LOTR, rating: 10, publisher: A ...
> >2. name: ASOIAF, rating: 10, publisher: A ...
> >3. name: Twilight, rating 1, publisher: B ...
>
> >and my publishers are:
> >A and B
>
> >Your query returns:
> >[A, num_book=2]
> >[B, num_book=1]
>
> >the query on my first message returns:
> >[A, num_book=2]
>
> >and what I need is:
> >[A, num_book=2]
> >[B, num_book=0]
>
> >where num_books means number of good books.
>
> Sorry, I missed that detail in your question.  You need to use the
> .extra() queryset method to do what you are attempting.  The
> following will add a 'num_good_books' attribute to each Publisher
> object:
>
> qs = Publisher.objects.extra(select={'num_good_books': 'select count(*) from 
> publisher_book where publisher_book.publisher_id = publisher_publisher.id and 
> publisher_book.rating > 3.0'})
> for o in qs:
>    print o.name, o.num_good_books
>
> Note that the "publisher_" prefix in the SQL is the name of the
> Django application in which your models reside.  So if your Django
> application is named "myapp", then you'd need to change the
> "publisher_" prefix to "myapp_".

-- 
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+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en.

Re: Need help on a (maybe complex) query with aggregate/annotate

[Michael Elkins]

On Tue, Feb 21, 2012 at 01:10:28PM -0800, Enrico wrote:

But your query counts all the books, even the bad ones. I only need to
count the good ones...

For example, if my books are:

1. name: LOTR, rating: 10, publisher: A ...
2. name: ASOIAF, rating: 10, publisher: A ...
3. name: Twilight, rating 1, publisher: B ...

and my publishers are:
A and B

Your query returns:
[A, num_book=2]
[B, num_book=1]

the query on my first message returns:
[A, num_book=2]

and what I need is:
[A, num_book=2]
[B, num_book=0]

where num_books means number of good books.


Sorry, I missed that detail in your question.  You need to use the 
.extra() queryset method to do what you are attempting.  The 
following will add a 'num_good_books' attribute to each Publisher 
object:


qs = Publisher.objects.extra(select={'num_good_books': 'select count(*) from 
publisher_book where publisher_book.publisher_id = publisher_publisher.id and 
publisher_book.rating > 3.0'})
for o in qs:
  print o.name, o.num_good_books

Note that the "publisher_" prefix in the SQL is the name of the 
Django application in which your models reside.  So if your Django 
application is named "myapp", then you'd need to change the 
"publisher_" prefix to "myapp_".


--
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+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en.

Re: Need help on a (maybe complex) query with aggregate/annotate

[Enrico]

Thanks Micheal for the response!

But your query counts all the books, even the bad ones. I only need to
count the good ones...

For example, if my books are:

1. name: LOTR, rating: 10, publisher: A ...
2. name: ASOIAF, rating: 10, publisher: A ...
3. name: Twilight, rating 1, publisher: B ...

and my publishers are:
A and B

Your query returns:
[A, num_book=2]
[B, num_book=1]

the query on my first message returns:
[A, num_book=2]

and what I need is:
[A, num_book=2]
[B, num_book=0]

where num_books means number of good books.

I'm trying to do this with a single query because i need to iterate
over the queryset in one template with something like:
{% for p in publishers %}
{{ p.name }} - {{ p.number_good_books }}
{% endfor %}
Right now I'm using a custom method in the Publisher model
( get_number_of_good_books() ).
It works but it's really slow (one query for each publisher to count
the number of good books).

Ciao

On Feb 21, 7:50 pm, Michael  Elkins  wrote:
> On Feb 21, 6:11 am, Enrico  wrote:
>
> > This query:
> > Publisher.objects.filter(book__rating__gt=3.0).annotate(num_books=Count('bo 
> > ok'))
> > returns all the publishers with at least one good book (ranked > 3)
> > with annotated the number of good books for each publisher.
>
> > How can i modify the query to get the list of ALL publishers with
> > annotated the number of good books for each publisher?
> > In other words I want to keep in the results also the Publishers
> > without good books (num_books = 0).
>
> Hi Enrico,
>
> The Django documentation has an example of what you are trying to do:
>
> https://docs.djangoproject.com/en/1.3/topics/db/aggregation/#generati...
>
> The answer to your specific question is to do it this way:
>
> qs = Publisher.objects.annotate(num_books=Count('book'))
> for o in qs:
>   print o.num_books

-- 
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+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en.

Re: Need help on a (maybe complex) query with aggregate/annotate

[Michael Elkins]

On Feb 21, 6:11 am, Enrico  wrote:
> This query:
> Publisher.objects.filter(book__rating__gt=3.0).annotate(num_books=Count('bo 
> ok'))
> returns all the publishers with at least one good book (ranked > 3)
> with annotated the number of good books for each publisher.
>
> How can i modify the query to get the list of ALL publishers with
> annotated the number of good books for each publisher?
> In other words I want to keep in the results also the Publishers
> without good books (num_books = 0).

Hi Enrico,

The Django documentation has an example of what you are trying to do:

https://docs.djangoproject.com/en/1.3/topics/db/aggregation/#generating-aggregates-for-each-item-in-a-queryset

The answer to your specific question is to do it this way:

qs = Publisher.objects.annotate(num_books=Count('book'))
for o in qs:
  print o.num_books

-- 
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+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en.

Need help on a (maybe complex) query with aggregate/annotate

[Enrico]

I'm having a bit of hard time with aggregation.

For simplicity's sake consider the models available in the aggregation
docs:
https://docs.djangoproject.com/en/1.3/topics/db/aggregation/#

This query:
Publisher.objects.filter(book__rating__gt=3.0).annotate(num_books=Count('book'))
returns all the publishers with at least one good book (ranked > 3)
with annotated the number of good books for each publisher.

How can i modify the query to get the list of ALL publishers with
annotated the number of good books for each publisher?
In other words I want to keep in the results also the Publishers
without good books (num_books = 0).

Ciao
Enrico

PS: Sorry for my horrible english

-- 
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+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en.

Re: need help with calling following complex query

[Roshan Mathews]

On Mon, Jul 11, 2011 at 10:36, Venkatraman S  wrote:
> Raw-sql and the snippet provided by me would end up generating the same sql.
>
> One advantage of my snippet is, if your db does not support sin/cos 
> functions(like sqlite3),
> then you can go ahead defining funcs which computes the same. Something like..
>     from django.db import connection, transaction
>     cursor = connection.cursor()
>     import math
>     connection.connection.create_function('acos', 1, 
> math.acos)
>     connection.connection.create_function('cos', 1, 
> math.cos)
>     connection.connection.create_function('sin', 1, 
> math.sin)
>

Nice! Didn't know this could be done. Thanks for the update.

--
http://about.me/rosh

-- 
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+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en.

Re: need help with calling following complex query

[Venkatraman S]

On Mon, Jul 11, 2011 at 10:36 PM, Jonas Geiregat  wrote:

> >
> > results.extra(select={
> > 'distance':
> '(acos(sin(latitude)*sin(%(latitude)f)+cos(latitude)*cos(%(latitude)f)*cos(%(longitude)f-longitude)))'
>  % {
> >  'latitude': latitude,
> >  'longitude': longitude
> > }
> > }).order_by('distance')
> >
>
> This is an interesting solution to the issue. I never really looked into
> the extra method.
> So I read the docs and I'm wondering if it wouldn't be better to use the
> select_params parameter ?
>

I havent used this with select_params, but doesnt that require that you type
the same argument couple of times to match the parameters.
I prefer this 'named' parameter list, as it is easy to manage and read. Isnt
it?

-V

-- 
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+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en.

Re: need help with calling following complex query

[Venkatraman S]

On Tue, Jul 12, 2011 at 4:36 AM, sanket  wrote:

> Thanks @Venkatraman, I really appreciate your help.
> ability to create a function is pretty cool.
>

Thanks. Actually, i stumbled on this solution due to my use case - i test
all my apps in sqlite3 and then move to other dbs; as it is easy for me to
manage a single db file, and there is no db server overhead . And in this
case, i used the exact same sql excerpt and found that sqlite3 did not
support the math funcs. It was a pretty nifty hack.

-- 
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+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en.

Re: need help with calling following complex query

[sanket]

Thanks @Venkatraman, I really appreciate your help.
ability to create a function is pretty cool.

Thanks again,
sanket

On Jul 11, 1:06 pm, Jonas Geiregat  wrote:
> > results.extra(select={
> >                             'distance': 
> > '(acos(sin(latitude)*sin(%(latitude)f)+cos(latitude)*cos(%(latitude)f)*cos(%(longitude)f-longitude)))'
> >   % {
> >                                  'latitude': latitude,
> >                                  'longitude': longitude
> >                             }
> >                         }).order_by('distance')  
>
> This is an interesting solution to the issue. I never really looked into the 
> extra method.
> So I read the docs and I'm wondering if it wouldn't be better to use the 
> select_params parameter ?

-- 
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+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en.

Re: need help with calling following complex query

[Jonas Geiregat]

> 
> results.extra(select={
> 'distance': 
> '(acos(sin(latitude)*sin(%(latitude)f)+cos(latitude)*cos(%(latitude)f)*cos(%(longitude)f-longitude)))'
>   % {
>  'latitude': latitude,
>  'longitude': longitude
> }
> }).order_by('distance')  
> 

This is an interesting solution to the issue. I never really looked into the 
extra method. 
So I read the docs and I'm wondering if it wouldn't be better to use the 
select_params parameter ?


-- 
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+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en.

Re: need help with calling following complex query

[Cal Leeming [Simplicity Media Ltd]]

Thats pretty cool, I didnt know you could do create function. Will check
this out later :)
On 11 Jul 2011 06:06, "Venkatraman S"  wrote:
> On Mon, Jul 11, 2011 at 10:02 AM, sanket  wrote:
>
>> I think I would go ahead with executing the raw SQL in this case.
>> The solution by @Venatraman looks interesting too. I would give it a
>> try.
>>
>
> Raw-sql and the snippet provided by me would end up generating the same
sql.
>
> One advantage of my snippet is, if your db does not support sin/cos
> functions(like sqlite3),
> then you can go ahead defining funcs which computes the same. Something
> like..
> from django.db import connection, transaction
> cursor = connection.cursor()
> import math
> connection.connection.create_function('acos', 1,
> math.acos)
> connection.connection.create_function('cos', 1,
> math.cos)
> connection.connection.create_function('sin', 1,
> math.sin)
>
> -V
>
> --
> 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+unsubscr...@googlegroups.com.
> For more options, visit this group at
http://groups.google.com/group/django-users?hl=en.
>

-- 
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+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en.

Re: need help with calling following complex query

[Venkatraman S]

On Mon, Jul 11, 2011 at 10:02 AM, sanket  wrote:

> I think I would go ahead with executing the raw SQL in this case.
> The solution by @Venatraman looks interesting too. I would give it a
> try.
>

Raw-sql and the snippet provided by me would end up generating the same sql.

One advantage of my snippet is, if your db does not support sin/cos
functions(like sqlite3),
then you can go ahead defining funcs which computes the same. Something
like..
from django.db import connection, transaction
cursor = connection.cursor()
import math
connection.connection.create_function('acos', 1,
math.acos)
connection.connection.create_function('cos', 1,
math.cos)
connection.connection.create_function('sin', 1,
math.sin)

-V

-- 
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+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en.

Re: need help with calling following complex query

[sanket]

Thanks all for the help,
I really appreciate it.

I think I would go ahead with executing the raw SQL in this case.
The solution by @Venatraman looks interesting too. I would give it a
try.
I might write a stored procedure if I start getting performance issues
if data starts growing.

Thanks again,
sanket

On Jul 10, 7:33 pm, Venkatraman S  wrote:
> On Mon, Jul 11, 2011 at 7:10 AM, Cal Leeming [Simplicity Media Ltd] <
>
> cal.leem...@simplicitymedialtd.co.uk> wrote:
> > +1 on raw SQL in this instance.
>
> Not exactly, using ORM is pretty easy too. You would do something like this:
>
> results.extra(select={
>                             'distance':
> '(acos(sin(latitude)*sin(%(latitude)f)+cos(latitude)*cos(%(latitude)f)*cos(%(longitude)f-longitude)))'
> % {
>                                  'latitude': latitude,
>                                  'longitude': longitude
>                             }
>                         }).order_by('distance')
>
> -V

-- 
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+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en.

Re: need help with calling following complex query

[Venkatraman S]

On Mon, Jul 11, 2011 at 7:10 AM, Cal Leeming [Simplicity Media Ltd] <
cal.leem...@simplicitymedialtd.co.uk> wrote:

> +1 on raw SQL in this instance.


Not exactly, using ORM is pretty easy too. You would do something like this:

results.extra(select={
'distance':
'(acos(sin(latitude)*sin(%(latitude)f)+cos(latitude)*cos(%(latitude)f)*cos(%(longitude)f-longitude)))'
% {
 'latitude': latitude,
 'longitude': longitude
}
}).order_by('distance')

-V

-- 
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+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en.

Re: need help with calling following complex query

[Cal Leeming [Simplicity Media Ltd]]

+1 on raw SQL in this instance.

On Mon, Jul 11, 2011 at 2:36 AM, Jian Chang  wrote:

> Using raw sql is a better idea
>
>
> 2011/7/11 Jonas Geiregat 
>
>>
>>
>> In order to find the places in near by area I want to make following
>> mysql query. but I am not sure how should I translate it using django
>> models and managers.
>>
>>
>> You could execute the raw sql query string, see:
>> https://docs.djangoproject.com/en/dev/topics/db/sql/
>> for more information on the subject. Seeing the complexity of the query
>> and knowing it's a good working version why should you even bother
>> translating it to django's ORM framework ?
>>
>>
>> orig.lat = x
>> orig.lon = y
>>
>> "SELECT  destination.*,
>> 3956 * 2 * ASIN(SQRT(  POWER(SIN((orig.lat - dest.lat) * pi()/180 /
>> 2), 2) +
>> COS(orig.lat * pi()/180) *  COS(dest.lat * pi()/180) *
>> POWER(SIN((orig.lon -dest.lon) * pi()/180 / 2), 2)  )) as
>> distance
>> FROM place as dest
>> WHERE  dest.longitude
>> BETWEEN lon1 and lon2
>> AND dest.latitude
>> BETWEEN lat1 and lat2
>> "
>>
>> the model which talks to database is called Place in my case.
>>
>> Thank you all for the help,
>> sanket
>>
>> --
>> 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+unsubscr...@googlegroups.com.
>> For more options, visit this group at
>> http://groups.google.com/group/django-users?hl=en.
>>
>>
>>
>> Jonas Geiregat
>> jo...@geiregat.org
>>
>>
>>
>>
>>
>>  --
>> 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+unsubscr...@googlegroups.com.
>> For more options, visit this group at
>> http://groups.google.com/group/django-users?hl=en.
>>
>
>  --
> 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+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/django-users?hl=en.
>

-- 
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+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en.

Re: need help with calling following complex query

[Jian Chang]

Using raw sql is a better idea

2011/7/11 Jonas Geiregat 

>
>
> In order to find the places in near by area I want to make following
> mysql query. but I am not sure how should I translate it using django
> models and managers.
>
>
> You could execute the raw sql query string, see:
> https://docs.djangoproject.com/en/dev/topics/db/sql/
> for more information on the subject. Seeing the complexity of the query and
> knowing it's a good working version why should you even bother translating
> it to django's ORM framework ?
>
>
> orig.lat = x
> orig.lon = y
>
> "SELECT  destination.*,
> 3956 * 2 * ASIN(SQRT(  POWER(SIN((orig.lat - dest.lat) * pi()/180 /
> 2), 2) +
> COS(orig.lat * pi()/180) *  COS(dest.lat * pi()/180) *
> POWER(SIN((orig.lon -dest.lon) * pi()/180 / 2), 2)  )) as
> distance
> FROM place as dest
> WHERE  dest.longitude
> BETWEEN lon1 and lon2
> AND dest.latitude
> BETWEEN lat1 and lat2
> "
>
> the model which talks to database is called Place in my case.
>
> Thank you all for the help,
> sanket
>
> --
> 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+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/django-users?hl=en.
>
>
>
> Jonas Geiregat
> jo...@geiregat.org
>
>
>
>
>
>  --
> 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+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/django-users?hl=en.
>

-- 
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+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en.

Re: need help with calling following complex query

[Jonas Geiregat]

> 
> 
> In order to find the places in near by area I want to make following
> mysql query. but I am not sure how should I translate it using django
> models and managers.

You could execute the raw sql query string, see: 
https://docs.djangoproject.com/en/dev/topics/db/sql/
for more information on the subject. Seeing the complexity of the query and 
knowing it's a good working version why should you even bother translating it 
to django's ORM framework ?

> 
> orig.lat = x
> orig.lon = y
> 
> "SELECT  destination.*,
> 3956 * 2 * ASIN(SQRT(  POWER(SIN((orig.lat - dest.lat) * pi()/180 /
> 2), 2) +
> COS(orig.lat * pi()/180) *  COS(dest.lat * pi()/180) *
> POWER(SIN((orig.lon -dest.lon) * pi()/180 / 2), 2)  )) as
> distance
> FROM place as dest
> WHERE  dest.longitude
> BETWEEN lon1 and lon2
> AND dest.latitude
> BETWEEN lat1 and lat2
> "
> 
> the model which talks to database is called Place in my case.
> 
> Thank you all for the help,
> sanket
> 
> -- 
> 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+unsubscr...@googlegroups.com.
> For more options, visit this group at 
> http://groups.google.com/group/django-users?hl=en.
> 


Jonas Geiregat
jo...@geiregat.org





-- 
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+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en.

need help with calling following complex query

[sanket]

Hey All,

I am quite new to Django and recently started using django for
developing a location based service.
In order to find the places in near by area I want to make following
mysql query. but I am not sure how should I translate it using django
models and managers.

orig.lat = x
orig.lon = y

"SELECT  destination.*,
3956 * 2 * ASIN(SQRT(  POWER(SIN((orig.lat - dest.lat) * pi()/180 /
2), 2) +
COS(orig.lat * pi()/180) *  COS(dest.lat * pi()/180) *
POWER(SIN((orig.lon -dest.lon) * pi()/180 / 2), 2)  )) as
distance
FROM place as dest
WHERE  dest.longitude
BETWEEN lon1 and lon2
AND dest.latitude
BETWEEN lat1 and lat2
"

the model which talks to database is called Place in my case.

Thank you all for the help,
sanket

-- 
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+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en.

Re: Complex Query help - get items from the org to which the user belongs

[Venkatraman S]

On Thu, Jul 7, 2011 at 3:49 PM, DrBloodmoney  wrote:

> On Thu, Jul 7, 2011 at 12:04 AM, Venkatraman S  wrote:
> > I am doing some bechmarking on the performance of both the versions of
> the
> > query:
> > 1.
> >
> Item.objects.filter(created_by__employees__org__in=u.employees_set.all().values_list('org'))
> > and
> > 2. Items.objects.extra(where=['created_by_id in (select e.user_id from
> > myapp_employees e, myapp_organization o where e.org_id = o.id and o.id =
> > (select o2.id from myapp_organization o2, myapp_employees e2 where
> e2.org_id
> > = o2.id and e2.user_id=3 and e2.deleted=False)) '])
> >
> > I am seeing #1 to be faster(when i view from DDT). Will update in the
> next
> > few days or probably early next week.
> >
> > -V
>
> Personally, I'd add a FK on the Item to the Employee, particularly if
> they're in the same app (Actually I'd replace the FK to User with one
> to Employee). I suspect that is a simplified models.py so I don't
> actually know if there would be a requirement to FK to User. (I also
> try to limit my code touching django.contrib.auth since using it is my
> biggest pain point for django).
>
>
Cant actually, as the Item can be created by both employees, and
'outsiders'.

-- 
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+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en.

Re: Complex Query help - get items from the org to which the user belongs

[DrBloodmoney]

On Thu, Jul 7, 2011 at 12:04 AM, Venkatraman S  wrote:
> I am doing some bechmarking on the performance of both the versions of the
> query:
> 1.
> Item.objects.filter(created_by__employees__org__in=u.employees_set.all().values_list('org'))
> and
> 2. Items.objects.extra(where=['created_by_id in (select e.user_id from
> myapp_employees e, myapp_organization o where e.org_id = o.id and o.id =
> (select o2.id from myapp_organization o2, myapp_employees e2 where e2.org_id
> = o2.id and e2.user_id=3 and e2.deleted=False)) '])
>
> I am seeing #1 to be faster(when i view from DDT). Will update in the next
> few days or probably early next week.
>
> -V

Personally, I'd add a FK on the Item to the Employee, particularly if
they're in the same app (Actually I'd replace the FK to User with one
to Employee). I suspect that is a simplified models.py so I don't
actually know if there would be a requirement to FK to User. (I also
try to limit my code touching django.contrib.auth since using it is my
biggest pain point for django).

-- 
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+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en.

Re: Complex Query help - get items from the org to which the user belongs

[Venkatraman S]

I am doing some bechmarking on the performance of both the versions of the
query:
1.
Item.objects.filter(created_by__employees__org__in=u.employees_set.all().values_list('org'))
and
2. Items.objects.extra(where=['created_by_id in (select e.user_id from
myapp_employees e, myapp_organization o where e.org_id = o.id and o.id =
(select o2.id from myapp_organization o2, myapp_employees e2 where e2.org_id
= o2.id and e2.user_id=3 and e2.deleted=False)) '])

I am seeing #1 to be faster(when i view from DDT). Will update in the next
few days or probably early next week.

-V

-- 
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+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en.

Re: Complex Query help - get items from the org to which the user belongs

[Venkatraman S]

On Wed, Jul 6, 2011 at 8:09 PM, Marc Aymerich  wrote:

> I swear this time it will work!
>
> Item.objects.filter(created_by__employees__org__in=u.employees_set.all().values_list('org'))
>
>
I cried on seeing this ;) Thanks a tonne.  Guess this will go into the
django hall of fame!
But i have a Q : the query that i pinged (using 'where') works, so how
efficient is your version? (am parallely checking this using DDT).

-V

-- 
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+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en.

Re: Complex Query help - get items from the org to which the user belongs

[Marc Aymerich]

On Wed, Jul 6, 2011 at 2:46 PM, Venkatraman S  wrote:
>
>
> On Wed, Jul 6, 2011 at 2:10 PM, Marc Aymerich  wrote:
>>
>> This one should work:
>> Items.objects.filters(created_by__employee__org=A.org)
>
> Nope. Emp has a FK for User, not the other way round.

I swear this time it will work!
Item.objects.filter(created_by__employees__org__in=u.employees_set.all().values_list('org'))
--
Marc

-- 
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+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en.

Re: Complex Query help - get items from the org to which the user belongs

[Tom Evans]

On Wed, Jul 6, 2011 at 2:40 PM, akaariai  wrote:
> Maybe this?
>
> user = current_user
> user_org_employees = Employees.objects.filter(org=user.org)
> user_org_items =
> Items.objects.filter(created_by__in=user_org_employees)
>

No, users can belong to multiple organizations, so no using user.org.
Something like this:

orgs = Organization.objects.filter(employees__user=user)
users = User.objects.filter(employee__org__in=orgs)
items = Item.objects.filter(created_by__in=users)

Cheers

Tom

-- 
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+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en.

Re: Complex Query help - get items from the org to which the user belongs

[akaariai]

On Jul 5, 10:06 pm, Venkatraman S  wrote:
> I tried asking around in IRC, and stumbled on a few possible solutions,
> would be great if someone shed some more light:
>
> I have the following models:
>
> class Organization(models.Model):
>   name                = models.CharField(max_length=100, blank=False)
>
> class Employees(models.Model):
>   org                 = models.ForeignKey(Organization,
> related_name='employees')
>   user                = models.ForeignKey(User)
>   name                = models.CharField(max_length=100, blank=False)
>
> class Item(models.Model):
>   name                = models.CharField(max_length=100, blank=False)
>   created_by          =
> models.ForeignKey(User,related_name='created_by_whom')
>
> Problem : I need to get all Items from the Organization to which current
> User belongs to.
> So basically, get all employees from the Org that the current User belongs
> to, and then get all items created by these employees. (So, this query
> should also include the current User).

Maybe this?

user = current_user
user_org_employees = Employees.objects.filter(org=user.org)
user_org_items =
Items.objects.filter(created_by__in=user_org_employees)

 - Anssi

-- 
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+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en.

Re: Complex Query help - get items from the org to which the user belongs

[Venkatraman S]

On Wed, Jul 6, 2011 at 2:10 PM, Marc Aymerich  wrote:

> This one should work:
> Items.objects.filters(created_by__employee__org=A.org)
>

Nope. Emp has a FK for User, not the other way round.

-- 
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+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en.

Re: Complex Query help - get items from the org to which the user belongs

[Marc Aymerich]

On Wed, Jul 6, 2011 at 4:46 AM, Venkatraman S  wrote:

>
> On Wed, Jul 6, 2011 at 2:26 AM, Marc Aymerich  wrote:
>
>> Say there are 3 Users in an org : A, B and C with each creating 3,4,5
>> items respectively, and 'A' is the current user; then i need a query which
>> returns all these items(i.e, 12 items) when i supply A..
>>
>> Item.objects.filter(employees__org=A.org)
>> This works¿?
>>
>
> I dont think this would work; Items doesnt have an FK for Emps.
>

Sorry, I answered too fast.

This one should work:
Items.objects.filters(created_by__employee__org=A.org)


-- 
Marc

-- 
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+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en.

Re: Complex Query help - get items from the org to which the user belongs

[Venkatraman S]

On Wed, Jul 6, 2011 at 8:39 AM, Venkatraman S  wrote:

>
> On Wed, Jul 6, 2011 at 12:36 AM, Venkatraman S  wrote:
>
>> I tried asking around in IRC, and stumbled on a few possible solutions,
>> would be great if someone shed some more light:
>>
>> I have the following models:
>>
>> class Organization(models.Model):
>>   name= models.CharField(max_length=100, blank=False)
>>
>> class Employees(models.Model):
>>   org = models.ForeignKey(Organization,
>> related_name='employees')
>>   user= models.ForeignKey(User)
>>   name= models.CharField(max_length=100, blank=False)
>>
>> class Item(models.Model):
>>   name= models.CharField(max_length=100, blank=False)
>>   created_by  =
>> models.ForeignKey(User,related_name='created_by_whom')
>>
>> Problem : I need to get all Items from the Organization to which current
>> User belongs to.
>> So basically, get all employees from the Org that the current User belongs
>> to, and then get all items created by these employees. (So, this query
>> should also include the current User).
>>
>> Say there are 3 Users in an org : A, B and C with each creating 3,4,5
>> items respectively, and 'A' is the current user; then i need a query which
>> returns all these items(i.e, 12 items) when i supply A..
>>
>
>
> This is the equivalent raw sql:
> select count(1)
> from myapp_items a
> where a.created_by_id in
> (
> select e.user_id
> from myapp_employees e, myapp_organization o
> where e.org_id = o.id
> and o.id = (select o2.id from myapp_organization o2, myapp_employees e2
> where e2.org_id = o2.id and e2.user_id=<>)
> )
>
>
And this would be a probable query:
Items.objects.extra(where=['created_by_id in (select e.user_id from
myapp_employees e, myapp_organization o where e.org_id = o.id and o.id =
(select o2.id from myapp_organization o2, myapp_employees e2 where e2.org_id
= o2.id and e2.user_id=3)) ']).count()

Can some expert in django-ORM comment on this? (can the same be done without
a 'where' clause)

As  someone pointed out in IRC, i want to go up, down and then sideways in
this query ;)

-V

-- 
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+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en.

Re: Complex Query help - get items from the org to which the user belongs

[Venkatraman S]

On Wed, Jul 6, 2011 at 12:36 AM, Venkatraman S  wrote:

> I tried asking around in IRC, and stumbled on a few possible solutions,
> would be great if someone shed some more light:
>
> I have the following models:
>
> class Organization(models.Model):
>   name= models.CharField(max_length=100, blank=False)
>
> class Employees(models.Model):
>   org = models.ForeignKey(Organization,
> related_name='employees')
>   user= models.ForeignKey(User)
>   name= models.CharField(max_length=100, blank=False)
>
> class Item(models.Model):
>   name= models.CharField(max_length=100, blank=False)
>   created_by  =
> models.ForeignKey(User,related_name='created_by_whom')
>
> Problem : I need to get all Items from the Organization to which current
> User belongs to.
> So basically, get all employees from the Org that the current User belongs
> to, and then get all items created by these employees. (So, this query
> should also include the current User).
>
> Say there are 3 Users in an org : A, B and C with each creating 3,4,5 items
> respectively, and 'A' is the current user; then i need a query which returns
> all these items(i.e, 12 items) when i supply A..
>


This is the equivalent raw sql:
select count(1)
from myapp_items a
where a.created_by_id in
(
select e.user_id
from myapp_employees e, myapp_organization o
where e.org_id = o.id
and o.id = (select o2.id from myapp_organization o2, myapp_employees e2
where e2.org_id = o2.id and e2.user_id=<>)
)

-- 
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+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en.

Re: Complex Query help - get items from the org to which the user belongs

[Venkatraman S]

On Wed, Jul 6, 2011 at 2:26 AM, Marc Aymerich  wrote:

> Say there are 3 Users in an org : A, B and C with each creating 3,4,5 items
> respectively, and 'A' is the current user; then i need a query which returns
> all these items(i.e, 12 items) when i supply A..
>
> Item.objects.filter(employees__org=A.org)
> This works¿?
>

I dont think this would work; Items doesnt have an FK for Emps.

-V

-- 
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+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en.

Re: Complex Query help - get items from the org to which the user belongs

[Marc Aymerich]

On Tue, Jul 5, 2011 at 9:06 PM, Venkatraman S  wrote:

> I tried asking around in IRC, and stumbled on a few possible solutions,
> would be great if someone shed some more light:
>
> I have the following models:
>
> class Organization(models.Model):
>   name= models.CharField(max_length=100, blank=False)
>
> class Employees(models.Model):
>   org = models.ForeignKey(Organization,
> related_name='employees')
>   user= models.ForeignKey(User)
>   name= models.CharField(max_length=100, blank=False)
>
> class Item(models.Model):
>   name= models.CharField(max_length=100, blank=False)
>   created_by  =
> models.ForeignKey(User,related_name='created_by_whom')
>
> Problem : I need to get all Items from the Organization to which current
> User belongs to.
> So basically, get all employees from the Org that the current User belongs
> to, and then get all items created by these employees. (So, this query
> should also include the current User).
>
> Say there are 3 Users in an org : A, B and C with each creating 3,4,5 items
> respectively, and 'A' is the current user; then i need a query which returns
> all these items(i.e, 12 items) when i supply A..
>

Item.objects.filter(employees__org=A.org)
This works¿?


-- 
Marc

-- 
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+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en.

Complex Query help - get items from the org to which the user belongs

[Venkatraman S]

I tried asking around in IRC, and stumbled on a few possible solutions,
would be great if someone shed some more light:

I have the following models:

class Organization(models.Model):
  name= models.CharField(max_length=100, blank=False)

class Employees(models.Model):
  org = models.ForeignKey(Organization,
related_name='employees')
  user= models.ForeignKey(User)
  name= models.CharField(max_length=100, blank=False)

class Item(models.Model):
  name= models.CharField(max_length=100, blank=False)
  created_by  =
models.ForeignKey(User,related_name='created_by_whom')

Problem : I need to get all Items from the Organization to which current
User belongs to.
So basically, get all employees from the Org that the current User belongs
to, and then get all items created by these employees. (So, this query
should also include the current User).

Say there are 3 Users in an org : A, B and C with each creating 3,4,5 items
respectively, and 'A' is the current user; then i need a query which returns
all these items(i.e, 12 items) when i supply A..

-V

-- 
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+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en.

Re: complex query

[Andre Terra]

Hello

First, I must recommend that you use django-taggit, simpletag or any
other tagging app, otherwise you'll just be reinventing the wheel.

As for your question, you should read the aggregation docs. I don't
have the link handy, but this should be correct:
http://django.me/aggregation

Sincerely,
Andre Terra

On 7/3/11, Jonas Geiregat  wrote:
> Hello,
>
> I have the following models
>
> class Book(models.Model):
>   author = models.ManyToManyField(Author)
>   pub_date = models.DateTimeField()
>   ...
>
> class Author(models.Model):
>   tag = models.ManyToManyField(Tag)
>
> class Tag(models.Model):
>   name = models.CharField(max_length=20)
>
> What I want to query for is:
>
> The most used Tags (and their count) for all books who's pub_date is greater
> then today.
>
> I can solve the last part
> Book.objects.filter(pub_date__gt=datetime.date.today())
>
> But how do I count all the tags for all these books ?
>
> Jonas Geiregat
> jo...@geiregat.org
>
>
>
>
>
> --
> 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+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/django-users?hl=en.
>
>

-- 
Sent from my mobile device

-- 
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+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en.

complex query

[Jonas Geiregat]

Hello,

I have the following models

class Book(models.Model):
author = models.ManyToManyField(Author)
pub_date = models.DateTimeField()
...

class Author(models.Model):
tag = models.ManyToManyField(Tag)

class Tag(models.Model):
name = models.CharField(max_length=20)

What I want to query for is:

The most used Tags (and their count) for all books who's pub_date is greater 
then today.

I can solve the last part 
Book.objects.filter(pub_date__gt=datetime.date.today())

But how do I count all the tags for all these books ?

Jonas Geiregat
jo...@geiregat.org





-- 
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+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en.

Re: Complex query

[gontran]

Now that I changed my model, the query is a little bit different.
So, my models:

UserProfile(models.Model):
user = models.OneToOneField(User)
company = models.ForeignKey(Company)

Company(models.Model):
...some fields...

Product(models.Model):
...some fields...

ProductLicence(models.Model):
product = models.ForeignKey(Product)
company = models.ForeignKey(Company)

News(models.Model):
   related_products = models.ManyToManyField(Product)


And my query:
news =
News.objects.filter(related_products__productlicence__in=user.get_profile().company.productlicence_set.all()).distinct()


I hope that it will be useful to somebody.

Regards,

Gontran

On 29 mar, 10:34, Kenneth Gonsalves  wrote:
> On Tue, 2011-03-29 at 01:17 -0700, bruno desthuilliers wrote:
> > On 29 mar, 09:33, Kenneth Gonsalves  wrote:
> > > Product is not linked to them - they are linked to Product.
>
> > Sorry but I dont see the point here - you can follow a relationship
> > both way.
>
> pointless nitpick - apologies
> --
> regards
> KGhttp://lawgon.livejournal.com
> Coimbatore LUG roxhttp://ilugcbe.techstud.org/

-- 
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+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en.

Re: Complex query

[Kenneth Gonsalves]

On Tue, 2011-03-29 at 01:17 -0700, bruno desthuilliers wrote:
> On 29 mar, 09:33, Kenneth Gonsalves  wrote:
> > Product is not linked to them - they are linked to Product.
> 
> Sorry but I dont see the point here - you can follow a relationship
> both way. 

pointless nitpick - apologies
-- 
regards
KG
http://lawgon.livejournal.com
Coimbatore LUG rox
http://ilugcbe.techstud.org/

-- 
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+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en.

Re: Complex query

[gontran]

Hi Bruno,

yes you're right. I should change my model in the way you mentioned
it. I saw that a couple of minutes ago.

And to answer to my initial question, I might have found the answer.
The query would be:

news = News.objects.filter(related_products__productlicence__in =
user.get_profile().company.product_licences.all()).distinct()

On 29 mar, 10:24, bruno desthuilliers 
wrote:
> On 29 mar, 09:11, gontran  wrote:
>
> > Hi everybody,
>
> > considering the folowing models:
>
> > UserProfile(models.Model):
> >     user = models.OneToOneField(User)
> >     company = models.ForeignKey(Company)
>
> > Company(models.Model):
> >     product_licences = models.manyToManyField(ProductLicence)
>
> Sorry if I missed something, but are you sure you want a m2m
> relationship here ??? This means that a same licence can "belong" to
> many companies, which seems rather weird to me. As far as I'm
> concerned I'd make "company" a foreign key in ProductLicence - or I
> just don't understand your definition of what "licence" is ???
>
> > Product(models.Model):
> >     ...some fields...
>
> > ProductLicence(models.Model):
> >     product = models.ForeignKey(Product)
>
> (snip)

-- 
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+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en.

Re: Complex query

[bruno desthuilliers]

On 29 mar, 09:11, gontran  wrote:
> Hi everybody,
>
> considering the folowing models:
>
> UserProfile(models.Model):
>     user = models.OneToOneField(User)
>     company = models.ForeignKey(Company)
>
> Company(models.Model):
>     product_licences = models.manyToManyField(ProductLicence)


Sorry if I missed something, but are you sure you want a m2m
relationship here ??? This means that a same licence can "belong" to
many companies, which seems rather weird to me. As far as I'm
concerned I'd make "company" a foreign key in ProductLicence - or I
just don't understand your definition of what "licence" is ???


> Product(models.Model):
>     ...some fields...
>
> ProductLicence(models.Model):
>     product = models.ForeignKey(Product)
>


(snip)

-- 
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+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en.

Re: Complex query

[bruno desthuilliers]

On 29 mar, 09:33, Kenneth Gonsalves  wrote:
> Product is not linked to them - they are linked to Product.

Sorry but I dont see the point here - you can follow a relationship
both way.

-- 
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+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en.

Re: Complex query

[gontran]

Ok. I 'll try again
Thank you very much for your consideration.

Regards,

Gontran

On 29 mar, 09:33, Kenneth Gonsalves  wrote:
> Product is not linked to them - they are linked to Product. I do not
> think you can do it in one query because there are ManyToMany fields
> which have to be retrieved and then you have to iterate over them.
> Certainly more than one query.
>
>
>
>
>
>
>
>
>
> On Tue, 2011-03-29 at 00:24 -0700, gontran wrote:
> > Yes: ProductLicence and News
>
> > On 29 mar, 09:19, Kenneth Gonsalves  wrote:
> > > On Tue, 2011-03-29 at 00:11 -0700, gontran wrote:
> > > > UserProfile(models.Model):
> > > >     user = models.OneToOneField(User)
> > > >     company = models.ForeignKey(Company)
>
> > > > Company(models.Model):
> > > >     product_licences = models.manyToManyField(ProductLicence)
>
> > > > Product(models.Model):
> > > >     ...some fields...
>
> > > > ProductLicence(models.Model):
> > > >     product = models.ForeignKey(Product)
>
> > > > News(models.Model):
> > > >    related_products = models.ManyToManyField(Product)
>
> > > > I already know how to retrieve all distinct products for which the
> > > > company of the user owns licences, but now, what I want to do is to
> > > > retrieve all news, for a given user, that are related to products, for
> > > > which the company of the user owns licences.
>
> > > Product is not linked to any model?
> > > --
> > > regards
> > > KGhttp://lawgon.livejournal.com
> > > Coimbatore LUG roxhttp://ilugcbe.techstud.org/
>
> --
> regards
> KGhttp://lawgon.livejournal.com
> Coimbatore LUG roxhttp://ilugcbe.techstud.org/

-- 
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+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en.

Re: Complex query

[Kenneth Gonsalves]

Product is not linked to them - they are linked to Product. I do not
think you can do it in one query because there are ManyToMany fields
which have to be retrieved and then you have to iterate over them.
Certainly more than one query.

On Tue, 2011-03-29 at 00:24 -0700, gontran wrote:
> Yes: ProductLicence and News
> 
> On 29 mar, 09:19, Kenneth Gonsalves  wrote:
> > On Tue, 2011-03-29 at 00:11 -0700, gontran wrote:
> > > UserProfile(models.Model):
> > > user = models.OneToOneField(User)
> > > company = models.ForeignKey(Company)
> >
> > > Company(models.Model):
> > > product_licences = models.manyToManyField(ProductLicence)
> >
> > > Product(models.Model):
> > > ...some fields...
> >
> > > ProductLicence(models.Model):
> > > product = models.ForeignKey(Product)
> >
> > > News(models.Model):
> > >related_products = models.ManyToManyField(Product)
> >
> > > I already know how to retrieve all distinct products for which the
> > > company of the user owns licences, but now, what I want to do is to
> > > retrieve all news, for a given user, that are related to products, for
> > > which the company of the user owns licences.
> >
> > Product is not linked to any model?
> > --
> > regards
> > KGhttp://lawgon.livejournal.com
> > Coimbatore LUG roxhttp://ilugcbe.techstud.org/
> 


-- 
regards
KG
http://lawgon.livejournal.com
Coimbatore LUG rox
http://ilugcbe.techstud.org/

-- 
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+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en.

Re: Complex query

[gontran]

Yes: ProductLicence and News

On 29 mar, 09:19, Kenneth Gonsalves  wrote:
> On Tue, 2011-03-29 at 00:11 -0700, gontran wrote:
> > UserProfile(models.Model):
> >     user = models.OneToOneField(User)
> >     company = models.ForeignKey(Company)
>
> > Company(models.Model):
> >     product_licences = models.manyToManyField(ProductLicence)
>
> > Product(models.Model):
> >     ...some fields...
>
> > ProductLicence(models.Model):
> >     product = models.ForeignKey(Product)
>
> > News(models.Model):
> >    related_products = models.ManyToManyField(Product)
>
> > I already know how to retrieve all distinct products for which the
> > company of the user owns licences, but now, what I want to do is to
> > retrieve all news, for a given user, that are related to products, for
> > which the company of the user owns licences.
>
> Product is not linked to any model?
> --
> regards
> KGhttp://lawgon.livejournal.com
> Coimbatore LUG roxhttp://ilugcbe.techstud.org/

-- 
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+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en.

Re: Complex query

[Kenneth Gonsalves]

On Tue, 2011-03-29 at 00:11 -0700, gontran wrote:
> UserProfile(models.Model):
> user = models.OneToOneField(User)
> company = models.ForeignKey(Company)
> 
> Company(models.Model):
> product_licences = models.manyToManyField(ProductLicence)
> 
> Product(models.Model):
> ...some fields...
> 
> ProductLicence(models.Model):
> product = models.ForeignKey(Product)
> 
> News(models.Model):
>related_products = models.ManyToManyField(Product)
> 
> I already know how to retrieve all distinct products for which the
> company of the user owns licences, but now, what I want to do is to
> retrieve all news, for a given user, that are related to products, for
> which the company of the user owns licences.
> 
> 

Product is not linked to any model?
-- 
regards
KG
http://lawgon.livejournal.com
Coimbatore LUG rox
http://ilugcbe.techstud.org/

-- 
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+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en.

Complex query

[gontran]

Hi everybody,

considering the folowing models:

UserProfile(models.Model):
user = models.OneToOneField(User)
company = models.ForeignKey(Company)

Company(models.Model):
product_licences = models.manyToManyField(ProductLicence)

Product(models.Model):
...some fields...

ProductLicence(models.Model):
product = models.ForeignKey(Product)

News(models.Model):
   related_products = models.ManyToManyField(Product)

I already know how to retrieve all distinct products for which the
company of the user owns licences, but now, what I want to do is to
retrieve all news, for a given user, that are related to products, for
which the company of the user owns licences.

Is it possible to do it with a single query?


Thank you for your time,

regards,

Gontran

-- 
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+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en.

Re: Complex Query Question

[Javier Guerra]

mystand wrote:
> I need to do a query that selects all Events that a logged in user can edit.
> They can edit an event if they are in the default group, if they are in any
> of the other groups, or they are one of the users added to the event. 

first of all, it might be easier if you follow the database convention of 
naming your tables in singular.  in this case, your events model should be 
called 'Event' and not 'Events'.  that makes it more readable when you use 
singular in ForeignKey fields and plural in ManyToManyField's

in your case, the general strategy is to use Q() objects.  the idea is to 
define each possibility separately and then join them all with the '|' 
operator.  something like this (untested):

@login_required
def editable_groups(request):
user = request.user
evs_by_defgroup = Q(default_group__in=user.group_set)
evs_by_groups = Q(group__in=user.group_set)
evs_by_users = user.events_set

myevents = Events.objects.filter (evs_by_defgroup | evs_by_groups | 
evs_by_users)
return render_to_response ("template.html", {'myevents':myevents})


(i'm not sure about '|'ing the Q() objects with the queryset 'user.events_set', 
but i don't know how to express this as a Q() object)


-- 
Javier

--

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 at 
http://groups.google.com/group/django-users?hl=.

Complex Query Question

[mystand]

Hi all, 

I have a question about a complicated query I need to do. I have a model for
an event like this:

class Events(models.Model):
default_group = models.ForeignKey(Group, related_name='default_group',
null=True)
group = models.ManyToManyField(Group)
users = models.ManyToManyField(User)
title = models.CharField(max_length=255)
shortname = models.SlugField(max_length=25, unique=True) 

plus some more fields that are not relevant to this question.

This is for a event registration site. The default group must be chosen from
the event creator's groups. group can be any group and users can be any
users. 

I need to do a query that selects all Events that a logged in user can edit.
They can edit an event if they are in the default group, if they are in any
of the other groups, or they are one of the users added to the event. 

I've read the docs and am still missing something. How can I do this query
as elegantly as possible? 

Thanks,
A Django Newbie. 
-- 
View this message in context: 
http://old.nabble.com/Complex-Query-Question-tp26451658p26451658.html
Sent from the django-users mailing list archive at Nabble.com.

--

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 at 
http://groups.google.com/group/django-users?hl=.

Complex Query Optimization

[Phil]

I am trying to minimize the database hits. Take this example...

from django.contrib.auth.models import User

class Group(models.Model):
# a bunch of fields here
user = models.ForeignKey(User, related_name="groups")

class Member(models.Model):
# a bunch of fields here
user = models.ForeignKey(User, related_name="members")
groups= models.ForeignKey(Invitation, related_name="members")

Basically the idea of the above is that given a user, you can fetch a
list of members. Or, given a group, you can fetch a list of members
belonging to that group.

In one of my templates, I would like to paginate based on the groups,
lets say 10 groups per page. But for each group displayed, I want to
also display each member. The groups will be small enough so this
won't be ridiculous. I feel confident than this can be done _without_
having to perform 11 queries per page (one for the groups, and then 1
for getting the members of each group). Am I correct? If so, how?

Mildly off topic, I am trying to figure out what exactly happens when
using MyModel.objects.all(). I understand that it is just an iterable
but, using the example of pagination, when you do Paginator
(MyModel.objects.all(), 10), is there no database hit until you get a
specific page, where it will only fetch the 10 for the page it needs?

--~--~-~--~~~---~--~~
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+unsubscr...@googlegroups.com
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en
-~--~~~~--~~--~--~---

complex query - howto do with django orm?

[tom]

Hi all,

i have a complex query and want to do this query with the django orm.
I already use django, but the raw-sql-way:

def get_windenergy_data(self, windspeed_channel,
winddirection_channel, project_id, logger_serial):
from django.db import connection
cursor = connection.cursor()
cursor.execute(""" select S.value, D.value from
measurements_data as S
left join measurements_data as D
on S.datetime=D.datetime
where S.channel=%(windspeed_channel)s and
D.channel=%(winddirection_channel)s and
S.project_id=%(project_id)s and D.project_id=%
(project_id)s and
S.logger_serial=%(logger_serial)s and
D.logger_serial=%(logger_serial)s
;""",  locals() )
row = cursor.fetchall()
cursor.close()
return row


Can anybody help?
--~--~-~--~~~---~--~~
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+unsubscr...@googlegroups.com
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en
-~--~~~~--~~--~--~---

Change the where clause in a complex query involving a model with a self many to many

[Jacob Fenwick]

I have a hierarchy of models where the bottom object in the hierarchy has a
many to many relationship to itself.

Here are my models and views:

models.py
class Bar(models.Model):
name = models.CharField(max_length=200)

class BarPart(models.Model):
bar = models.ForeignKey(Bar)
name = models.CharField(max_length=255)

class BarComponentVersion(models.Model):
name = models.CharField(max_length=255)
barcomponentversions = models.ManyToManyField('self')


views.py
from django.shortcuts import render_to_response
from django.template import RequestContext
from django.db import models
from foo.bar.models import Bar, BarPart, BarComponentVersion

def all(request):
query = '10'
all =
Bar.objects.filter(barpart__barcomponentversion__barcomponentversions__barpart__bar__id=query).distinct()
sql, cols = all.query.as_sql()
c = RequestContext(request, {'all': all, 'sql': sql})
return render_to_response("foo/all.html", c)


When I run the query seen in the view, it works how it should, and generates
this query:

SELECT DISTINCT `foo_bar`.`id`, `foo_bar`.`name` FROM `foo_bar` INNER JOIN
`foo_barpart` ON (`foo_bar`.`id` = `foo_barpart`.`bar_id`) INNER JOIN
`foo_barcomponentversion` ON (`foo_barpart`.`id` =
`foo_barcomponentversion`.`barpart_id`) INNER JOIN
`foo_barcomponentversion_barcomponentversions` ON
(`foo_barcomponentversion`.`id` =
`foo_barcomponentversion_barcomponentversions`.`from_barcomponentversion_id`)
INNER JOIN `foo_barcomponentversion` T5 ON
(`foo_barcomponentversion_barcomponentversions`.`to_barcomponentversion_id`
= T5.`id`) INNER JOIN `foo_barpart` T6 ON (T5.`barpart_id` = T6.`id`) WHERE
T6.`bar_id` = %s


What this does is it goes FROM a specific bar to all the other bars that are
related to it.

I also want to go TO a specific bar from all the other bars it is related
to.

That means I want this query:

SELECT DISTINCT P2.`id`, P2.`name` FROM `foo_bar` P1 INNER JOIN
`foo_barpart` ON (P1.`id` = `foo_barpart`.`bar_id`) INNER JOIN
`foo_barcomponentversion` ON (`foo_barpart`.`id` =
`foo_barcomponentversion`.`barpart_id`) INNER JOIN
`foo_barcomponentversion_barcomponentversions` ON
(`foo_barcomponentversion`.`id` =
`foo_barcomponentversion_barcomponentversions`.`from_barcomponentversion_id`)
INNER JOIN `foo_barcomponentversion` T5 ON
(`foo_barcomponentversion_barcomponentversions`.`to_barcomponentversion_id`
= T5.`id`) INNER JOIN `foo_barpart` T6 ON (T5.`barpart_id` = T6.`id`) INNER
JOIN `foo_bar` P2 ON (T6.`bar_id` = P2.`id`) WHERE P1.`id` = %s

Essentially what I need to do is change the returned values to the SECOND
bar, and change the where clause to the FIRST bar.

Is there a way to do this with the ORM? Or should I just use the straight
SQL query?

Jacob

--~--~-~--~~~---~--~~
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+unsubscr...@googlegroups.com
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en
-~--~~~~--~~--~--~---

Re: Complex query in model manager

[CrabbyPete]

or another words can I use a Q object in a model manager?

On Aug 23, 12:05 am, CrabbyPete  wrote:
> I have the following code in model manager
>
> class SpotMessageManager(models.Manager):
>
>       def for_user(self, user, start = 0, end = None, friends ):
>             messages = self.filter(user = user).order_by
> ('date_added').reverse()
>             ...
>
> friends is a list of users, and I want the query set to include the
> user and all the friends in the list. What is the best way to do this?
--~--~-~--~~~---~--~~
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+unsubscr...@googlegroups.com
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en
-~--~~~~--~~--~--~---

Complex query in model manager

[CrabbyPete]

I have the following code in model manager

class SpotMessageManager(models.Manager):

  def for_user(self, user, start = 0, end = None, friends ):
messages = self.filter(user = user).order_by
('date_added').reverse()
...

friends is a list of users, and I want the query set to include the
user and all the friends in the list. What is the best way to do this?
--~--~-~--~~~---~--~~
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+unsubscr...@googlegroups.com
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en
-~--~~~~--~~--~--~---

Re: Parsing a complex query with many parentheses, ORs, ANDs, and (hopefully) NOTs

[Michael Keselman]

On Nov 21, 2008, at 11:46 PM, Malcolm Tredinnick wrote:

>
>
> On Fri, 2008-11-21 at 22:37 -0500, Michael Keselman wrote:
>> Hello,
>>
>> http://pastie.org/private/xvqf5tgjnimiolakhawgg (relevant code)
>>
>> Django's QuerySets allow you to do ORs (unions) pretty nicely with
>> Model.manager.filter(Q(...) | Q(...)). However, ANDs don't work quite
>> as nicely in my situation because I want AND to do what
>> Model.manager.filter(Q(...)).filter(Q(...)) would do, but
>> Model.manager.filter(Q(...) & Q(...)) does something very different.
>> It does not compare fields with other rows in the table but rather
>> with the same row against itself. For example, I have a Tag model  
>> with
>> a CharField I call 'name'. If I were to do
>> Tag.objects.filter(Q(name='text') & Q(name='password')), it would
>> compare each tag to check if the tag's name equals "text" AND that  
>> the
>> same tag's name equals "password".
>
> Yeah, this is the edge-case that isn't handled out of the box. On the
> (likely) probability that it's much less common than the alternative
> behaviour, we decided to go with the current approach. You use-case
> isn't invalid, but it's never the goal to support every possible
> use-case.
>
> Speaking as an implementor, I'll also note that it's the really,  
> really
> hard case to make it work efficiently in all cases (particularly with
> highly nested trees of ANDs and ORs), so it falls close to, or  
> possibly
> over the line marked "out of scope for the ORM; custom SQL is your
> friend".
>
> I can think of four(!) possible approaches for your problem. In rough
> order from least intrusive to the one requiring the most changes (and
> roughly least efficient to most efficient), we have the following. I
> haven't written code for any of these, so I'm using English rather  
> than
> Python. But my intuition is that they should all work.
>
> (1) After you have parsed the query, rewrite it in conjunctive normal
> form (CNF). That's a fairly routine transformation. Then each clause  
> in
> the CNF can be written as Q(...)|Q(...)|... and can be put in a  
> separate
> call to filter() (so you end up with one filter() call per clause in  
> the
> CNF). Guaranteed to give the right answer and requires no  
> understanding
> of Django's internals. The downside is that more comparisons than are
> possibly necessary will be made. Still, in practice, the difference
> possibly won't be noticed on a well-index table.
>
> (2) Write your own variation on Q() -- let's call it Q1 here, for
> brevity. This requires understanding what's going on when you call
> filter() a bit, but since you are looking at a case that isn't  
> normally
> handled, you need to roll up your sleeves and dive in. It's not that
> bad, really.
>
> Basically, filter() wraps up its parameters in a Q() object and then
> calls Query.add_q(). So calling Queryset.filter() is equivalent to an
> outer call to Query.add_q(). The way a single call to add_q() knows  
> that
> it can (and should) reuse certain aliases is the used_aliases  
> parameter
> passed into add_q(). So you could force a particular alias not to be
> reused if you adjusted used_aliases. Notice that used_aliases is also
> passed to the add_to_query() method of any Q-like object you might  
> pass
> in.
>
> In practice, that means you write a class Q1 that subclasses Q and  
> which
> has an add_to_query() method. In your code that constructs the filter
> call, you use Q1 when you are creating a conjunction of terms, rather
> than Q -- for example, Q1(Q(name="a") & Q(name="b")). Your
> Q1.add_to_query() method will then generally call Q.add_q(), except  
> that
> after each return it will remove any newly added aliases from
> used_aliases so that they aren't reused inside that Q1. You can do  
> this
> by deep copying used_aliases before and restoring it afterwards. Just
> before finally returning from your add_to_query() method, you might  
> want
> to add back all the used aliases so that any outer calls can reuse  
> them.
> It's this nested removing and readding of aliases that makes this a  
> hard
> problem (I would strongly suggest experimenting with something like  
> (A &
> (B|(C)) to make sure things work as expected).
>
> (3) You could write your own equivalent to add_q() and call that for
> your case. Since filter() calls add_q(), subclass QuerySet and add  
> your
> own disjoint_filter() method, say, that calls your own add_q()  
> method --
> which need not even be a separate method if you don't want to subclass
> Query as well and don't mind calling setup_filter() directly. In your
> own add_q() version, you might choose to not update used_aliases for
> some or all situations. I think not updating used_aliases at all will
> lead to very inefficient queries -- lots of unnecessary tables  
> involved
> -- so this could well reduce to option (2), above, in which case I'd  
> go
> with option (2). But have a think about it and see.
>
> (4) Since it looks like you're 

Re: Parsing a complex query with many parentheses, ORs, ANDs, and (hopefully) NOTs

[Malcolm Tredinnick]

On Fri, 2008-11-21 at 22:37 -0500, Michael Keselman wrote:
> Hello,
> 
> http://pastie.org/private/xvqf5tgjnimiolakhawgg (relevant code)
> 
> Django's QuerySets allow you to do ORs (unions) pretty nicely with  
> Model.manager.filter(Q(...) | Q(...)). However, ANDs don't work quite  
> as nicely in my situation because I want AND to do what  
> Model.manager.filter(Q(...)).filter(Q(...)) would do, but  
> Model.manager.filter(Q(...) & Q(...)) does something very different.  
> It does not compare fields with other rows in the table but rather  
> with the same row against itself. For example, I have a Tag model with  
> a CharField I call 'name'. If I were to do  
> Tag.objects.filter(Q(name='text') & Q(name='password')), it would  
> compare each tag to check if the tag's name equals "text" AND that the  
> same tag's name equals "password".

Yeah, this is the edge-case that isn't handled out of the box. On the
(likely) probability that it's much less common than the alternative
behaviour, we decided to go with the current approach. You use-case
isn't invalid, but it's never the goal to support every possible
use-case.

Speaking as an implementor, I'll also note that it's the really, really
hard case to make it work efficiently in all cases (particularly with
highly nested trees of ANDs and ORs), so it falls close to, or possibly
over the line marked "out of scope for the ORM; custom SQL is your
friend".

I can think of four(!) possible approaches for your problem. In rough
order from least intrusive to the one requiring the most changes (and
roughly least efficient to most efficient), we have the following. I
haven't written code for any of these, so I'm using English rather than
Python. But my intuition is that they should all work.

(1) After you have parsed the query, rewrite it in conjunctive normal
form (CNF). That's a fairly routine transformation. Then each clause in
the CNF can be written as Q(...)|Q(...)|... and can be put in a separate
call to filter() (so you end up with one filter() call per clause in the
CNF). Guaranteed to give the right answer and requires no understanding
of Django's internals. The downside is that more comparisons than are
possibly necessary will be made. Still, in practice, the difference
possibly won't be noticed on a well-index table.

(2) Write your own variation on Q() -- let's call it Q1 here, for
brevity. This requires understanding what's going on when you call
filter() a bit, but since you are looking at a case that isn't normally
handled, you need to roll up your sleeves and dive in. It's not that
bad, really.

Basically, filter() wraps up its parameters in a Q() object and then
calls Query.add_q(). So calling Queryset.filter() is equivalent to an
outer call to Query.add_q(). The way a single call to add_q() knows that
it can (and should) reuse certain aliases is the used_aliases parameter
passed into add_q(). So you could force a particular alias not to be
reused if you adjusted used_aliases. Notice that used_aliases is also
passed to the add_to_query() method of any Q-like object you might pass
in. 

In practice, that means you write a class Q1 that subclasses Q and which
has an add_to_query() method. In your code that constructs the filter
call, you use Q1 when you are creating a conjunction of terms, rather
than Q -- for example, Q1(Q(name="a") & Q(name="b")). Your
Q1.add_to_query() method will then generally call Q.add_q(), except that
after each return it will remove any newly added aliases from
used_aliases so that they aren't reused inside that Q1. You can do this
by deep copying used_aliases before and restoring it afterwards. Just
before finally returning from your add_to_query() method, you might want
to add back all the used aliases so that any outer calls can reuse them.
It's this nested removing and readding of aliases that makes this a hard
problem (I would strongly suggest experimenting with something like (A &
(B|(C)) to make sure things work as expected).

(3) You could write your own equivalent to add_q() and call that for
your case. Since filter() calls add_q(), subclass QuerySet and add your
own disjoint_filter() method, say, that calls your own add_q() method --
which need not even be a separate method if you don't want to subclass
Query as well and don't mind calling setup_filter() directly. In your
own add_q() version, you might choose to not update used_aliases for
some or all situations. I think not updating used_aliases at all will
lead to very inefficient queries -- lots of unnecessary tables involved
-- so this could well reduce to option (2), above, in which case I'd go
with option (2). But have a think about it and see.

(4) Since it looks like you're doing the equivalent of text searching
with boolean operators, my fourth solution would be to use a text
searching tool. Solr or Sphinx or something like that which indexes the
tag field in question. The reason for suggesting this option is that
most of those search engines have 

Parsing a complex query with many parentheses, ORs, ANDs, and (hopefully) NOTs

[Michael Keselman]

Hello,

http://pastie.org/private/xvqf5tgjnimiolakhawgg (relevant code)

Django's QuerySets allow you to do ORs (unions) pretty nicely with  
Model.manager.filter(Q(...) | Q(...)). However, ANDs don't work quite  
as nicely in my situation because I want AND to do what  
Model.manager.filter(Q(...)).filter(Q(...)) would do, but  
Model.manager.filter(Q(...) & Q(...)) does something very different.  
It does not compare fields with other rows in the table but rather  
with the same row against itself. For example, I have a Tag model with  
a CharField I call 'name'. If I were to do  
Tag.objects.filter(Q(name='text') & Q(name='password')), it would  
compare each tag to check if the tag's name equals "text" AND that the  
same tag's name equals "password".

Can you think of a way to fix eval_cmds() so that my app can parse a  
query like "(text & password) | (python & django)" which a user would  
enter? It seems like nothing I try will work.

Thank you,
Michael

--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en
-~--~~~~--~~--~--~---

Re: complex query in limit_choices_to

[leotr]

On database level that should be done by UNIQUE constraints.
And your problem should be made on form level.
read about ChoiceField in newforms documentation
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en
-~--~~~~--~~--~--~---

complex query in limit_choices_to

[Kenneth Gonsalves]

hi,

I have two models called Child and Sponsor. There is a model called  
Sponsorship which has foreign keys to Child and Sponsor. For the  
foreign key to Child, I want to limit choices to all those children  
who are *not* already a foreign key to sponsorship and who are  
active. This is the query I generate to get this list:

Child.objects.extra(
where=["""id not in(select child_id from web_sponsorship\
where web_sponsorship.iscurrent=True)"""]
).filter(iscurrent=True)

how do i get this into the limit_choices_to dictionary?

-- 

regards
kg
http://lawgon.livejournal.com
http://nrcfosshelpline.in/web/
Foss Conference for the common man: http://registration.fossconf.in/web/



--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en
-~--~~~~--~~--~--~---

Re: how to do complex query which filters on method not database field

[Ivan Sagalaev]

Rachel Willmer wrote:
> I have a complicated query which should return the subset of a table
> whose objects match one of several conditions.
> 
> What's the easiest way of doing this, given that I can't just do a SQL
> statement, because some of the conditions require me to feed the
> object's values into a function to evaluate it.
> 
> I've had a few ideas:
> 
> a) Pulling back the entire table, and deleting from the query set
> those objects which don't match.
> 
> Is this possible? I get the feeling that using delete() would delete
> the entry from the db, which i don't want to do.

You shouldn't actually delete anything from a full list of records. Just 
filter it in a new list (and then Python promptly discards unneeded 
records from memory):

 records = [r for r in Model.objects.all() if r.check_condition()]

(this assumes that check_condition returns something sensibly evaluating 
to True or False)

> I can't find any documentation to show how to union 2 querysets - is
> it possible?

Since you anyway will get them in memory entirely you can just create 
actual lists from querysets and then concatenate them (and may be check 
for duplicates if needed):

 result = list(queryset1) + list(queryset2)

But your option a) anyway looks more reasonable.

> c) Writing a custom Manager.

Managers don't do anything magical, you still have to choose wither a) 
or b) way. Whether to place this code into a Manager or just let it lay 
somewhere in the code is the whole different question, it's the matter 
of convenience.

--~--~-~--~~~---~--~~
 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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en
-~--~~~~--~~--~--~---

how to do complex query which filters on method not database field

[Rachel Willmer]

I have a complicated query which should return the subset of a table
whose objects match one of several conditions.

What's the easiest way of doing this, given that I can't just do a SQL
statement, because some of the conditions require me to feed the
object's values into a function to evaluate it.

I've had a few ideas:

a) Pulling back the entire table, and deleting from the query set
those objects which don't match.

Is this possible? I get the feeling that using delete() would delete
the entry from the db, which i don't want to do.

b) Doing multiple querysets and making a union out of the results.

I can't find any documentation to show how to union 2 querysets - is
it possible?

c) Writing a custom Manager.

Any pointers welcome
Rachel

--~--~-~--~~~---~--~~
 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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en
-~--~~~~--~~--~--~---