Re: ManyToMany relationship and raw sql queries

[Tom Evans]

On Thu, Apr 11, 2013 at 3:42 PM, Matthieu Bouron
<matthie...@gmail.com> 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: "<Bookmark: Bookmark object>" 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

[Matthieu Bouron]

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

[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.

[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

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users...@googlegroups.com.
To post to this group, send email to django...@googlegroups.com.
Visit this group at http://groups.google.com/group/django-users?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.