Electronics & Programming

develissimo

Open Source electronics development and programming

  • You are not logged in.
  • Root
  • » Django
  • » Is there a way to find objects with duplicate values for some field using ORM? [RSS Feed]

#1 Jan. 19, 2011 11:02:11

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

Is there a way to find objects with duplicate values for some field using ORM?


Hi there.

I'm trying to do something like this:

SELECT *
FROM table
WHERE tablefield IN (
SELECT tablefield
FROM table
GROUP BY tablefield
HAVING (COUNT(tablefield ) > 1)
)

Tried it in many ways, but didn't suceed. Is there a way to do it with
Django ORM without having to iterate over objects or using raw SQL?

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

Offline

#2 Jan. 19, 2011 11:04:36

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

Is there a way to find objects with duplicate values for some field using ORM?


To make it more clear: I have something like a forum and want to find
duplicate messages (meaing the message field should be identical) to
create a report for the moderator.

On 19 янв, 14:02, "-AL.exe" <cpr.al....@gmail.com> wrote:
> Hi there.
>
> I'm trying to do something like this:
>
> SELECT *
> FROM table
> WHERE tablefield IN (
>  SELECT tablefield
>  FROM table
>  GROUP BY tablefield
>  HAVING (COUNT(tablefield ) > 1)
> )
>
> Tried it in many ways, but didn't suceed. Is there a way to do it with
> Django ORM without having to iterate over objects or using raw SQL?

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

Offline

#3 Jan. 19, 2011 11:04:52

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

Is there a way to find objects with duplicate values for some field using ORM?


May I ask why you didn't just use:

SELECT id from table GROUP BY tablefield HAVING (COUNT(tablefield) > 1)

On Wed, Jan 19, 2011 at 11:02 AM, -AL.exe <cpr.al....@gmail.com> wrote:

> Hi there.
>
> I'm trying to do something like this:
>
> SELECT *
> FROM table
> WHERE tablefield IN (
> SELECT tablefield
> FROM table
> GROUP BY tablefield
> HAVING (COUNT(tablefield ) > 1)
> )
>
> Tried it in many ways, but didn't suceed. Is there a way to do it with
> Django ORM without having to iterate over objects or using raw SQL?
>
> --
> 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<django-users%2bunsubscr...@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 athttp://groups.google.com/group/django-users?hl=en.

Offline

#4 Jan. 19, 2011 11:07:35

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

Is there a way to find objects with duplicate values for some field using ORM?


Umm... Don't know, actually. That seems, that it would be okay, too. Is
there a way to execute this one using ORM?

On Wed, Jan 19, 2011 at 2:04 PM, Cal Leeming <
cal.leem...@simplicitymedialtd.co.uk> wrote:

> May I ask why you didn't just use:
>
> SELECT id from table GROUP BY tablefield HAVING (COUNT(tablefield) > 1)
>
> On Wed, Jan 19, 2011 at 11:02 AM, -AL.exe <cpr.al....@gmail.com>wrote:
>
>> Hi there.
>>
>> I'm trying to do something like this:
>>
>> SELECT *
>> FROM table
>> WHERE tablefield IN (
>> SELECT tablefield
>> FROM table
>> GROUP BY tablefield
>> HAVING (COUNT(tablefield ) > 1)
>> )
>>
>> Tried it in many ways, but didn't suceed. Is there a way to do it with
>> Django ORM without having to iterate over objects or using raw SQL?
>>
>> --
>> 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<django-users%2bunsubscr...@googlegroups.com>
>> .
>> For more options, visit this group at
>>http://groups.google.com/group/django-users?hl=en.
>>
>>
>


--
Sincerely yours, Alexey.

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

Offline

#5 Jan. 19, 2011 11:18:45

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

Is there a way to find objects with duplicate values for some field using ORM?


Yeah you should be able to use annotate, something like:

from django.db.models import Count
res =
Book.objects.values('tablefield').annotate(Count('tablefield')).order_by('tablefield')
res2 = filter(lambda x: x.tablefield__count > 1, res)

The above is probably not going to work first time, but it would be
something along those lines most likely.

I would suggest that for complex queries, bypassing the ORM isn't
necessarily a bad thing, and there are many cases where a developer will
purposely bypass the ORM at bottlenecks and directly query SQL for
optimization.

On Wed, Jan 19, 2011 at 11:07 AM, -AL.exe <cpr.al....@gmail.com> wrote:

> Umm... Don't know, actually. That seems, that it would be okay, too. Is
> there a way to execute this one using ORM?
>
>
> On Wed, Jan 19, 2011 at 2:04 PM, Cal Leeming <
> cal.leem...@simplicitymedialtd.co.uk> wrote:
>
>> May I ask why you didn't just use:
>>
>> SELECT id from table GROUP BY tablefield HAVING (COUNT(tablefield) > 1)
>>
>> On Wed, Jan 19, 2011 at 11:02 AM, -AL.exe <cpr.al....@gmail.com>wrote:
>>
>>> Hi there.
>>>
>>> I'm trying to do something like this:
>>>
>>> SELECT *
>>> FROM table
>>> WHERE tablefield IN (
>>> SELECT tablefield
>>> FROM table
>>> GROUP BY tablefield
>>> HAVING (COUNT(tablefield ) > 1)
>>> )
>>>
>>> Tried it in many ways, but didn't suceed. Is there a way to do it with
>>> Django ORM without having to iterate over objects or using raw SQL?
>>>
>>> --
>>> 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<django-users%2bunsubscr...@googlegroups.com>
>>> .
>>> For more options, visit this group at
>>>http://groups.google.com/group/django-users?hl=en.
>>>
>>>
>>
>
>
> --
> Sincerely yours, Alexey.
>

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

Offline

#6 Jan. 19, 2011 11:26:40

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

Is there a way to find objects with duplicate values for some field using ORM?


Hmm, yeah, but that still iterates over the objects and filters out
them by count on the application server, not on the database one :(

I'm bypassing it already in my running project, but I try to avoid
using raw sql as longer as it is possible. So, the question, actually
remains open.

It should go something like :

Book.objects.values('tablefield').annotate(Count('tablefield')).filter('tablefield__count__gte
= 1)

But, of course, that wouldn't work, because annotate's return value is
not a queryset :(

On 19 янв, 14:18, "Cal Leeming "
<cal.leem...@simplicitymedialtd.co.uk> wrote:
> Yeah you should be able to use annotate, something like:
>
> from django.db.models import Count
> res =
> Book.objects.values('tablefield').annotate(Count('tablefield')).order_by('t
> ablefield')
> res2 = filter(lambda x: x.tablefield__count > 1, res)
>
> The above is probably not going to work first time, but it would be
> something along those lines most likely.
>
> I would suggest that for complex queries, bypassing the ORM isn't
> necessarily a bad thing, and there are many cases where a developer will
> purposely bypass the ORM at bottlenecks and directly query SQL for
> optimization.
>
>
>
>
>
>
>
> On Wed, Jan 19, 2011 at 11:07 AM, -AL.exe <cpr.al....@gmail.com> wrote:
> > Umm... Don't know, actually. That seems, that it would be okay, too. Is
> > there a way to execute this one using ORM?
>
> > On Wed, Jan 19, 2011 at 2:04 PM, Cal Leeming <
> > cal.leem...@simplicitymedialtd.co.uk> wrote:
>
> >> May I ask why you didn't just use:
>
> >> SELECT id from table GROUP BY tablefield HAVING (COUNT(tablefield) > 1)
>
> >> On Wed, Jan 19, 2011 at 11:02 AM, -AL.exe <cpr.al....@gmail.com>wrote:
>
> >>> Hi there.
>
> >>> I'm trying to do something like this:
>
> >>> SELECT *
> >>> FROM table
> >>> WHERE tablefield IN (
> >>>  SELECT tablefield
> >>>  FROM table
> >>>  GROUP BY tablefield
> >>>  HAVING (COUNT(tablefield ) > 1)
> >>> )
>
> >>> Tried it in many ways, but didn't suceed. Is there a way to do it with
> >>> Django ORM without having to iterate over objects or using raw SQL?
>
> >>> --
> >>> 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<django-users%2Bunsubscribe@google
> >>> groups.com>
> >>> .
> >>> For more options, visit this group at
> >>>http://groups.google.com/group/django-users?hl=en.
>
> > --
> > Sincerely yours, Alexey.

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

Offline

#7 Jan. 19, 2011 11:33:33

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

Is there a way to find objects with duplicate values for some field using ORM?


Here's something you could do.

Execute the raw SQL to bring back just the id's, then whilst iterating over
that, if you need to grab the ORM'd row, just do "row_orm =
model.get(id=iterated_id_here)"

This is what I tend to do for raw sql queries.

On Wed, Jan 19, 2011 at 11:26 AM, -AL.exe <cpr.al....@gmail.com> wrote:

> Hmm, yeah, but that still iterates over the objects and filters out
> them by count on the application server, not on the database one :(
>
> I'm bypassing it already in my running project, but I try to avoid
> using raw sql as longer as it is possible. So, the question, actually
> remains open.
>
> It should go something like :
>
>
> Book.objects.values('tablefield').annotate(Count('tablefield')).filter('tablefield__count__gte
> = 1)
>
> But, of course, that wouldn't work, because annotate's return value is
> not a queryset :(
>
> On 19 янв, 14:18, "Cal Leeming "
> <cal.leem...@simplicitymedialtd.co.uk> wrote:
> > Yeah you should be able to use annotate, something like:
> >
> > from django.db.models import Count
> > res =
> >
> Book.objects.values('tablefield').annotate(Count('tablefield')).order_by('t
> ablefield')
> > res2 = filter(lambda x: x.tablefield__count > 1, res)
> >
> > The above is probably not going to work first time, but it would be
> > something along those lines most likely.
> >
> > I would suggest that for complex queries, bypassing the ORM isn't
> > necessarily a bad thing, and there are many cases where a developer will
> > purposely bypass the ORM at bottlenecks and directly query SQL for
> > optimization.
> >
> >
> >
> >
> >
> >
> >
> > On Wed, Jan 19, 2011 at 11:07 AM, -AL.exe <cpr.al....@gmail.com>
> wrote:
> > > Umm... Don't know, actually. That seems, that it would be okay, too. Is
> > > there a way to execute this one using ORM?
> >
> > > On Wed, Jan 19, 2011 at 2:04 PM, Cal Leeming <
> > > cal.leem...@simplicitymedialtd.co.uk> wrote:
> >
> > >> May I ask why you didn't just use:
> >
> > >> SELECT id from table GROUP BY tablefield HAVING (COUNT(tablefield) >
> 1)
> >
> > >> On Wed, Jan 19, 2011 at 11:02 AM, -AL.exe <cpr.al....@gmail.com
> >wrote:
> >
> > >>> Hi there.
> >
> > >>> I'm trying to do something like this:
> >
> > >>> SELECT *
> > >>> FROM table
> > >>> WHERE tablefield IN (
> > >>> SELECT tablefield
> > >>> FROM table
> > >>> GROUP BY tablefield
> > >>> HAVING (COUNT(tablefield ) > 1)
> > >>> )
> >
> > >>> Tried it in many ways, but didn't suceed. Is there a way to do it
> with
> > >>> Django ORM without having to iterate over objects or using raw SQL?
> >
> > >>> --
> > >>> 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<django-users%2bunsubscr...@googlegroups.com>
> <django-users%2Bunsubscribe@google groups.com>
> > >>> .
> > >>> For more options, visit this group at
> > >>>http://groups.google.com/group/django-users?hl=en.
> >
> > > --
> > > Sincerely yours, Alexey.
>
> --
> 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<django-users%2bunsubscr...@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 athttp://groups.google.com/group/django-users?hl=en.

Offline

#8 Jan. 19, 2011 11:37:55

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

Is there a way to find objects with duplicate values for some field using ORM?


But that, again, includes executing raw sql =) And a lot of side-
queries, btw.

On 19 янв, 14:33, "Cal Leeming "
<cal.leem...@simplicitymedialtd.co.uk> wrote:
> Here's something you could do.
>
> Execute the raw SQL to bring back just the id's, then whilst iterating over
> that, if you need to grab the ORM'd row, just do "row_orm =
> model.get(id=iterated_id_here)"
>
> This is what I tend to do for raw sql queries.
>
>
>
>
>
>
>
> On Wed, Jan 19, 2011 at 11:26 AM, -AL.exe <cpr.al....@gmail.com> wrote:
> > Hmm, yeah, but that still iterates over the objects and filters out
> > them by count on the application server, not on the database one :(
>
> > I'm bypassing it already in my running project, but I try to avoid
> > using raw sql as longer as it is possible. So, the question, actually
> > remains open.
>
> > It should go something like :
>
> > Book.objects.values('tablefield').annotate(Count('tablefield')).filter('tab
> > lefield__count__gte
> > = 1)
>
> > But, of course, that wouldn't work, because annotate's return value is
> > not a queryset :(
>
> > On 19 янв, 14:18, "Cal Leeming "
> > <cal.leem...@simplicitymedialtd.co.uk> wrote:
> > > Yeah you should be able to use annotate, something like:
>
> > > from django.db.models import Count
> > > res =
>
> > Book.objects.values('tablefield').annotate(Count('tablefield')).order_by('t
> > ablefield')
> > > res2 = filter(lambda x: x.tablefield__count > 1, res)
>
> > > The above is probably not going to work first time, but it would be
> > > something along those lines most likely.
>
> > > I would suggest that for complex queries, bypassing the ORM isn't
> > > necessarily a bad thing, and there are many cases where a developer will
> > > purposely bypass the ORM at bottlenecks and directly query SQL for
> > > optimization.
>
> > > On Wed, Jan 19, 2011 at 11:07 AM, -AL.exe <cpr.al....@gmail.com>
> > wrote:
> > > > Umm... Don't know, actually. That seems, that it would be okay, too. Is
> > > > there a way to execute this one using ORM?
>
> > > > On Wed, Jan 19, 2011 at 2:04 PM, Cal Leeming <
> > > > cal.leem...@simplicitymedialtd.co.uk> wrote:
>
> > > >> May I ask why you didn't just use:
>
> > > >> SELECT id from table GROUP BY tablefield HAVING (COUNT(tablefield) >
> > 1)
>
> > > >> On Wed, Jan 19, 2011 at 11:02 AM, -AL.exe <cpr.al....@gmail.com
> > >wrote:
>
> > > >>> Hi there.
>
> > > >>> I'm trying to do something like this:
>
> > > >>> SELECT *
> > > >>> FROM table
> > > >>> WHERE tablefield IN (
> > > >>>  SELECT tablefield
> > > >>>  FROM table
> > > >>>  GROUP BY tablefield
> > > >>>  HAVING (COUNT(tablefield ) > 1)
> > > >>> )
>
> > > >>> Tried it in many ways, but didn't suceed. Is there a way to do it
> > with
> > > >>> Django ORM without having to iterate over objects or using raw SQL?
>
> > > >>> --
> > > >>> 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<django-users%2Bunsubscribe@google
> > > >>> groups.com>
> > <django-users%2Bunsubscribe@google groups.com>
> > > >>> .
> > > >>> For more options, visit this group at
> > > >>>http://groups.google.com/group/django-users?hl=en.
>
> > > > --
> > > > Sincerely yours, Alexey.
>
> > --
> > 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<django-users%2Bunsubscribe@google
> > groups.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 athttp://groups.google.com/group/django-users?hl=en.

Offline

#9 Jan. 19, 2011 11:50:10

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

Is there a way to find objects with duplicate values for some field using ORM?


On Wed, Jan 19, 2011 at 11:26 AM, -AL.exe <cpr.al....@gmail.com> wrote:
> Hmm, yeah, but that still iterates over the objects and filters out
> them by count on the application server, not on the database one :(
>
> I'm bypassing it already in my running project, but I try to avoid
> using raw sql as longer as it is possible. So, the question, actually
> remains open.
>
> It should go something like :
>
> Book.objects.values('tablefield').annotate(Count('tablefield')).filter('tablefield__count__gte
> = 1)
>
> But, of course, that wouldn't work, because annotate's return value is
> not a queryset :(
>

Er, says who?

>>> Organization.objects.annotate(num_grps=Count('usergroup')).filter(num_grps__gt=2).values_list('id',
>>> flat=True)


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

Offline

#10 Jan. 19, 2011 11:52:44

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

Is there a way to find objects with duplicate values for some field using ORM?


Wait, whhhhat... o.O I'l try that now, thx.

On Wed, Jan 19, 2011 at 2:50 PM, Tom Evans <tevans...@googlemail.com> wrote:

> On Wed, Jan 19, 2011 at 11:26 AM, -AL.exe <cpr.al....@gmail.com>
> wrote:
> > Hmm, yeah, but that still iterates over the objects and filters out
> > them by count on the application server, not on the database one :(
> >
> > I'm bypassing it already in my running project, but I try to avoid
> > using raw sql as longer as it is possible. So, the question, actually
> > remains open.
> >
> > It should go something like :
> >
> >
> Book.objects.values('tablefield').annotate(Count('tablefield')).filter('tablefield__count__gte
> > = 1)
> >
> > But, of course, that wouldn't work, because annotate's return value is
> > not a queryset :(
> >
>
> Er, says who?
>
> >>>
> Organization.objects.annotate(num_grps=Count('usergroup')).filter(num_grps__gt=2).values_list('id',
> flat=True)
>
>
> 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<django-users%2bunsubscr...@googlegroups.com>
> .
> For more options, visit this group at
>http://groups.google.com/group/django-users?hl=en.
>
>


--
Sincerely yours, Alexey.

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

Offline

  • Root
  • » Django
  • » Is there a way to find objects with duplicate values for some field using ORM? [RSS Feed]

Board footer

Moderator control

Enjoy the 24th of August
PoweredBy

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