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.