Re: ManyToMany relationship and raw sql queries

[Siddharth Ghumre]

Hi ,

Did you try python psycopg2.
A simple deomostration.
Ex:-
import psycopg2
conn=psycopg2.connect("dbname='xxx' user='xxx' host='localhost'
password='xxx'")
cur = conn.cursor()

query="""SELECT * FROM bookmarkmanager_bookmark b '
 'LEFT JOIN bookmarkmanager_bookmark_tags bt ON (b.id =
 bt.bookmark_id) '
 'LEFT JOIN bookmarkmanager_tag t ON (bt.tag_id = t.id
);"""


cur.execute(query)
result=cur.fetchall()


you can iterate over result list for data-processing and getting the
requires resultset.

-Siddharth





On Sun, Apr 28, 2013 at 4:06 AM, Marc R  wrote:

> did you use Bookmark.objetcs.all().select_related() ?
>
> As this will create a join query... at least for your model you show in
> the message I have found that for very complex joins, you need to use
> raw query and not a Model.
>
> On Thursday, April 25, 2013 10:44:28 AM UTC-4, Matthieu Bouron wrote:
>>
>> On Thursday, April 11, 2013 5:39:57 PM UTC+2, Tom Evans wrote:
>>>
>>> On Thu, Apr 11, 2013 at 3:42 PM, Matthieu Bouron
>>>  wrote:
>>> > Hello,
>>> >
>>> > Is there a way to handle many-to-many relationship with raw sql
>>> queries ?
>>> > I have the following model:
>>> >
>>> > from django.db import models
>>> >
>>> > class Tag(models.Model):
>>> > name = models.CharField(max_length=**512, unique=True)
>>> >
>>> > class Bookmark(models.Model):
>>> > link = models.CharField(max_length=**512)
>>> > title = models.CharField(max_length=**512)
>>> > tags = models.ManyToManyField(Tag)
>>> > added_at = models.DateField()
>>> >
>>> > Using Bookmark.objetcs.all() will result in a subquery for each row of
>>> the
>>> > bookmark table which is not acceptable for performance reasons.
>>> > First question is there a way to fetch all the content with a single
>>> query ?
>>> > Second question is it possible to use raw sql queries. I tried the
>>> > following:
>>> >
>>> > bookmarks = Bookmark.objects.raw(
>>> > 'SELECT * FROM bookmarkmanager_bookmark b '
>>> > 'LEFT JOIN bookmarkmanager_bookmark_tags bt ON (
>>> b.id =
>>> > bt.bookmark_id) '
>>> > 'LEFT JOIN bookmarkmanager_tag t ON (bt.tag_id =
>>> t.id)'
>>> > )
>>> >
>>> > But when i tried to access the tags attribute on a bookmark i get the
>>> > following exception:
>>> >
>>> > ValueError: "" needs to have a value for
>>> field
>>> > "bookmark" before this many-to-many relationship can be used.
>>> >
>>> > Thanks in advance,
>>> > Matthieu
>>> >
>>>
>>> Are you tied to raw SQL queries? Django itself supports traversing M2M
>>> relationships in a sane manner if you tell it to do so:
>>>
>>> https://docs.djangoproject.**com/en/1.5/ref/models/**
>>> querysets/#prefetch-related
>>>
>>
>> Thanks.
>> Another problem remains ... performances are horrible even if i use
>> values().
>> I have 1000 rows in base, executing from a script (db sqlite):
>>   - 800ms to fetch all the objects (traversing m2m with prefetch_related).
>>   - 200ms to fetch all the row from bookmark table without traversing the
>> m2m relationship).
>>
>> Are my results expected ?
>> Are there any plan on improving django orm performance ?
>>
>> Matthieu
>>
>  --
> 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?hl=en.
> 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?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

Re: ManyToMany relationship and raw sql queries

[Marc R]

did you use Bookmark.objetcs.all().select_related() ?

As this will create a join query... at least for your model you show in the 
message I have found that for very complex joins, you need to use raw 
query and not a Model.

On Thursday, April 25, 2013 10:44:28 AM UTC-4, Matthieu Bouron wrote:
>
> On Thursday, April 11, 2013 5:39:57 PM UTC+2, Tom Evans wrote:
>>
>> On Thu, Apr 11, 2013 at 3:42 PM, Matthieu Bouron 
>>  wrote: 
>> > Hello, 
>> > 
>> > Is there a way to handle many-to-many relationship with raw sql queries 
>> ? 
>> > I have the following model: 
>> > 
>> > from django.db import models 
>> > 
>> > class Tag(models.Model): 
>> > name = models.CharField(max_length=512, unique=True) 
>> > 
>> > class Bookmark(models.Model): 
>> > link = models.CharField(max_length=512) 
>> > title = models.CharField(max_length=512) 
>> > tags = models.ManyToManyField(Tag) 
>> > added_at = models.DateField() 
>> > 
>> > Using Bookmark.objetcs.all() will result in a subquery for each row of 
>> the 
>> > bookmark table which is not acceptable for performance reasons. 
>> > First question is there a way to fetch all the content with a single 
>> query ? 
>> > Second question is it possible to use raw sql queries. I tried the 
>> > following: 
>> > 
>> > bookmarks = Bookmark.objects.raw( 
>> > 'SELECT * FROM bookmarkmanager_bookmark b ' 
>> > 'LEFT JOIN bookmarkmanager_bookmark_tags bt ON (
>> b.id = 
>> > bt.bookmark_id) ' 
>> > 'LEFT JOIN bookmarkmanager_tag t ON (bt.tag_id = 
>> t.id)' 
>> > ) 
>> > 
>> > But when i tried to access the tags attribute on a bookmark i get the 
>> > following exception: 
>> > 
>> > ValueError: "" needs to have a value for 
>> field 
>> > "bookmark" before this many-to-many relationship can be used. 
>> > 
>> > Thanks in advance, 
>> > Matthieu 
>> > 
>>
>> Are you tied to raw SQL queries? Django itself supports traversing M2M 
>> relationships in a sane manner if you tell it to do so: 
>>
>>
>> https://docs.djangoproject.com/en/1.5/ref/models/querysets/#prefetch-related 
>>
>
> Thanks.
> Another problem remains ... performances are horrible even if i use 
> values().
> I have 1000 rows in base, executing from a script (db sqlite):
>   - 800ms to fetch all the objects (traversing m2m with prefetch_related).
>   - 200ms to fetch all the row from bookmark table without traversing the 
> m2m relationship).
>
> Are my results expected ?
> Are there any plan on improving django orm performance ?
>
> Matthieu
>

-- 
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?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

Re: ManyToMany relationship and raw sql queries

[Matthieu Bouron]

On Thursday, April 11, 2013 5:39:57 PM UTC+2, Tom Evans wrote:
>
> On Thu, Apr 11, 2013 at 3:42 PM, Matthieu Bouron 
>  wrote: 
> > Hello, 
> > 
> > Is there a way to handle many-to-many relationship with raw sql queries 
> ? 
> > I have the following model: 
> > 
> > from django.db import models 
> > 
> > class Tag(models.Model): 
> > name = models.CharField(max_length=512, unique=True) 
> > 
> > class Bookmark(models.Model): 
> > link = models.CharField(max_length=512) 
> > title = models.CharField(max_length=512) 
> > tags = models.ManyToManyField(Tag) 
> > added_at = models.DateField() 
> > 
> > Using Bookmark.objetcs.all() will result in a subquery for each row of 
> the 
> > bookmark table which is not acceptable for performance reasons. 
> > First question is there a way to fetch all the content with a single 
> query ? 
> > Second question is it possible to use raw sql queries. I tried the 
> > following: 
> > 
> > bookmarks = Bookmark.objects.raw( 
> > 'SELECT * FROM bookmarkmanager_bookmark b ' 
> > 'LEFT JOIN bookmarkmanager_bookmark_tags bt ON (b.id= 
> > bt.bookmark_id) ' 
> > 'LEFT JOIN bookmarkmanager_tag t ON (bt.tag_id = 
> t.id)' 
> > ) 
> > 
> > But when i tried to access the tags attribute on a bookmark i get the 
> > following exception: 
> > 
> > ValueError: "" needs to have a value for 
> field 
> > "bookmark" before this many-to-many relationship can be used. 
> > 
> > Thanks in advance, 
> > Matthieu 
> > 
>
> Are you tied to raw SQL queries? Django itself supports traversing M2M 
> relationships in a sane manner if you tell it to do so: 
>
>
> https://docs.djangoproject.com/en/1.5/ref/models/querysets/#prefetch-related 
>

Thanks.
Another problem remains ... performances are horrible even if i use 
values().
I have 1000 rows in base, executing from a script (db sqlite):
  - 800ms to fetch all the objects (traversing m2m with prefetch_related).
  - 200ms to fetch all the row from bookmark table without traversing the 
m2m relationship).

Are my results expected ?
Are there any plan on improving django orm performance ?

Matthieu

-- 
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?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

Re: ManyToMany relationship and raw sql queries

[Tom Evans]

On Thu, Apr 11, 2013 at 3:42 PM, Matthieu Bouron
 wrote:
> Hello,
>
> Is there a way to handle many-to-many relationship with raw sql queries ?
> I have the following model:
>
> from django.db import models
>
> class Tag(models.Model):
> name = models.CharField(max_length=512, unique=True)
>
> class Bookmark(models.Model):
> link = models.CharField(max_length=512)
> title = models.CharField(max_length=512)
> tags = models.ManyToManyField(Tag)
> added_at = models.DateField()
>
> Using Bookmark.objetcs.all() will result in a subquery for each row of the
> bookmark table which is not acceptable for performance reasons.
> First question is there a way to fetch all the content with a single query ?
> Second question is it possible to use raw sql queries. I tried the
> following:
>
> bookmarks = Bookmark.objects.raw(
> 'SELECT * FROM bookmarkmanager_bookmark b '
> 'LEFT JOIN bookmarkmanager_bookmark_tags bt ON (b.id =
> bt.bookmark_id) '
> 'LEFT JOIN bookmarkmanager_tag t ON (bt.tag_id = t.id)'
> )
>
> But when i tried to access the tags attribute on a bookmark i get the
> following exception:
>
> ValueError: "" needs to have a value for field
> "bookmark" before this many-to-many relationship can be used.
>
> Thanks in advance,
> Matthieu
>

Are you tied to raw SQL queries? Django itself supports traversing M2M
relationships in a sane manner if you tell it to do so:

https://docs.djangoproject.com/en/1.5/ref/models/querysets/#prefetch-related

Cheers

Tom

-- 
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?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

ManyToMany relationship and raw sql queries

[Matthieu Bouron]

Hello,

Is there a way to handle many-to-many relationship with raw sql queries ?
I have the following model:

from django.db import models

class Tag(models.Model):
name = models.CharField(max_length=512, unique=True)

class Bookmark(models.Model):
link = models.CharField(max_length=512)
title = models.CharField(max_length=512)
tags = models.ManyToManyField(Tag)
added_at = models.DateField()

Using Bookmark.objetcs.all() will result in a subquery for each row of the 
bookmark table which is not acceptable for performance reasons.
First question is there a way to fetch all the content with a single query ?
Second question is it possible to use raw sql queries. I tried the 
following:

bookmarks = Bookmark.objects.raw(
'SELECT * FROM bookmarkmanager_bookmark b '
'LEFT JOIN bookmarkmanager_bookmark_tags bt ON (b.id = 
bt.bookmark_id) '
'LEFT JOIN bookmarkmanager_tag t ON (bt.tag_id = t.id)'
)

But when i tried to access the tags attribute on a bookmark i get the 
following exception:

ValueError: "" needs to have a value for field 
"bookmark" before this many-to-many relationship can be used.

Thanks in advance,
Matthieu

-- 
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?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.