Very often I see constructs like 

    MyModel.objects.all().filter(...)

which will return a QuerySet of the default Mananger. At first `all()` seems to be quite redundant, because

    MyMode.objects.filter(...)

delivers the same result.

However, this seems to be safe for the default Manager only, because of the following two statements in the Django documentation:

Excerpt from the Chapter &quot;Adding extra manager methods&quot;
&gt; A custom Manager method can return anything you want. It doesn’t have
&gt; to return a QuerySet.

Definition of the `all()` manager method:
&gt; 
&gt; all() 
&gt; Returns a copy of the current QuerySet (or QuerySet subclass).
&gt; This can be useful in situations where you might want to pass in
&gt; either a model manager or a QuerySet and do further filtering on the
&gt; result. After calling all() on either object, you’ll definitely have a
&gt; QuerySet to work with.

This seems a bit like a contradiction to me. On one hand Django offers the freedom to let a manager method return whatever object type is preferred and on the other hand it requires a QuerySet for the `all()` method. I&#39;m aware that each manager has a `get_queryset` method which is called by `all()`. But who stops me from overriding `all()` in my custom manager? Although I agree it would be bad design to do so.

 - So as far as I can see, the `all()` method does not guarantee to return a QuerySet. What exactly does `MyModel.objects` return? Does this statement call `all()`? or `get_queryset()?

 - Do you prefer `MyModel.objects.filter(...)` or `MyModel.objects.all().filter(...)`. And if so, why?
 
 - Have you ever encountered wonky managers that would mess with those methods in a undesirable way?

Django ORM - objects.filter() vs. objects.all().filter() - which one is preferred?

I know, I can run a case insensitive search from DJango ORM. Like,

    User.objects.filter(first_name__contains=&quot;jake&quot;)
    User.objects.filter(first_name__contains=&quot;sulley&quot;)
    User.objects.filter(first_name__icontains=&quot;Jake&quot;)
    User.objects.filter(first_name__icontains=&quot;Sulley&quot;)

And also, I can fetch them as

    user_list = User.objects.all().order_by(&quot;first_name&quot;)
    # sequence: (Jake, Sulley, jake, sulley)
    user_list = User.objects.all().order_by(&quot;-first_name&quot;) # for reverse
    # sequence: (sulley, jake, Sulley, Jake)

Is there a direct way for a case-insensitive fetch?? As in I want a sequence as

    # desired sequence: jake, Jake, sulley, Sulley

If not, then suggest a best way to do it. Thanks in advance.

django-orm case-insensitive order by

In Django, what&#39;s the difference between the following two:

    Article.objects.values_list(&#39;comment_id&#39;, flat=True).distinct()

vs 

    Article.objects.values(&#39;comment_id&#39;).distinct()

My goal is to get a list of unique comment ids under each `Article`. I&#39;ve read the documentation (and in fact have used both approaches). The results overtly seem similar.


Django values_list vs values

I&#39;m kind of new to webapps and database stuff in general so this might be a dumb question. I want to make a model (&quot;CategoryModel&quot;) with a field that points to the primary id of another instance of the model (its parent).

    class CategoryModel(models.Model):
        parent = models.ForeignKey(CategoryModel)

How do I do this? Thanks! 

Django self-referential foreign key

Is there a way I can print the query the Django ORM is generating?

Say I execute the following statement: `Model.objects.filter(name=&#39;test&#39;)`

How do I get to see the generated SQL query?

How to view corresponding SQL query of the Django ORM&#39;s queryset?

My model:

    class Sample(models.Model):
        users = models.ManyToManyField(User)

I want to save both `user1` and `user2` in that model:

    user1 = User.objects.get(pk=1)
    user2 = User.objects.get(pk=2)
    sample_object = Sample(users=user1, users=user2)
    sample_object.save()

I know that&#39;s wrong, but I&#39;m sure you get what I want to do. How would you do it ?



How to create an object for a Django model with a many to many field?

I want to do pretty much the same like in [this ticket at djangoproject.com][1], but with some additonal formatting. From this query

    &gt;&gt;&gt; MyModel.objects.values(&#39;cryptic_value_name&#39;)
    [{&#39;cryptic_value_name&#39;: 1}, {&#39;cryptic_value_name&#39;: 2}]

I want to get something like that:

    &gt;&gt;&gt; MyModel.objects.values(renamed_value=&#39;cryptic_value_name&#39;)
    [{&#39;renamed_value&#39;: 1}, {&#39;renamed_value&#39;: 2}]

Is there another, more builtin way or do I have to do this manually?

  [1]: https://code.djangoproject.com/ticket/12222

How to rename items in values() in Django?

Django automatically creates an id field as primary key.

Now I need to get the object by this id.


    object = Class.objects.filter() 

How to write this filter?

Django database query: How to get object by id?

I&#39;m struggling getting my head around the Django&#39;s ORM. What I want to do is get a list of distinct values within a field on my table .... the equivalent of one of the following:

    SELECT DISTINCT myfieldname FROM mytable

(or alternatively)

    SELECT myfieldname FROM mytable GROUP BY myfieldname

I&#39;d at least like to do it the Django way before resorting to raw sql.
For example, with a table:

&gt;id, street, city
&gt;
&gt;1, Main Street, Hull
&gt;
&gt;2, Other Street, Hull
&gt;
&gt;3, Bibble Way, Leicester
&gt;
&gt;4, Another Way, Leicester
&gt;
&gt;5, High Street, Londidium

I&#39;d like to get: 
&gt;Hull, Leicester, Londidium.

Select distinct values from a table field

I always assumed that chaining multiple filter() calls in Django was always the same as collecting them in a single call.

    # Equivalent
    Model.objects.filter(foo=1).filter(bar=2)
    Model.objects.filter(foo=1,bar=2)

but I have run across a complicated queryset in my code where this is not the case

    class Inventory(models.Model):
        book = models.ForeignKey(Book)

    class Profile(models.Model):
        user = models.OneToOneField(auth.models.User)
        vacation = models.BooleanField()
        country = models.CharField(max_length=30)

    # Not Equivalent!
    Book.objects.filter(inventory__user__profile__vacation=False).filter(inventory__user__profile__country=&#39;BR&#39;)
    Book.objects.filter(inventory__user__profile__vacation=False, inventory__user__profile__country=&#39;BR&#39;)

The generated SQL is

    SELECT &quot;library_book&quot;.&quot;id&quot;, &quot;library_book&quot;.&quot;asin&quot;, &quot;library_book&quot;.&quot;added&quot;, &quot;library_book&quot;.&quot;updated&quot; FROM &quot;library_book&quot; INNER JOIN &quot;library_inventory&quot; ON (&quot;library_book&quot;.&quot;id&quot; = &quot;library_inventory&quot;.&quot;book_id&quot;) INNER JOIN &quot;auth_user&quot; ON (&quot;library_inventory&quot;.&quot;user_id&quot; = &quot;auth_user&quot;.&quot;id&quot;) INNER JOIN &quot;library_profile&quot; ON (&quot;auth_user&quot;.&quot;id&quot; = &quot;library_profile&quot;.&quot;user_id&quot;) INNER JOIN &quot;library_inventory&quot; T5 ON (&quot;library_book&quot;.&quot;id&quot; = T5.&quot;book_id&quot;) INNER JOIN &quot;auth_user&quot; T6 ON (T5.&quot;user_id&quot; = T6.&quot;id&quot;) INNER JOIN &quot;library_profile&quot; T7 ON (T6.&quot;id&quot; = T7.&quot;user_id&quot;) WHERE (&quot;library_profile&quot;.&quot;vacation&quot; = False  AND T7.&quot;country&quot; = BR )
    SELECT &quot;library_book&quot;.&quot;id&quot;, &quot;library_book&quot;.&quot;asin&quot;, &quot;library_book&quot;.&quot;added&quot;, &quot;library_book&quot;.&quot;updated&quot; FROM &quot;library_book&quot; INNER JOIN &quot;library_inventory&quot; ON (&quot;library_book&quot;.&quot;id&quot; = &quot;library_inventory&quot;.&quot;book_id&quot;) INNER JOIN &quot;auth_user&quot; ON (&quot;library_inventory&quot;.&quot;user_id&quot; = &quot;auth_user&quot;.&quot;id&quot;) INNER JOIN &quot;library_profile&quot; ON (&quot;auth_user&quot;.&quot;id&quot; = &quot;library_profile&quot;.&quot;user_id&quot;) WHERE (&quot;library_profile&quot;.&quot;vacation&quot; = False  AND &quot;library_profile&quot;.&quot;country&quot; = BR )

The first queryset with the chained `filter()` calls joins the Inventory model twice effectively creating an OR between the two conditions whereas the second queryset ANDs the two conditions together. I was expecting that the first query would also AND the two conditions. Is this the expected behavior or is this a bug in Django?

The answer to a related question https://stackoverflow.com/questions/3733324/is-there-a-downside-to-using-filter-filter-filter-in-django seems to indicated that the two querysets should be equivalent.


Chaining multiple filter() in Django, is this a bug?

The table in question contains roughly ten million rows.

    for event in Event.objects.all():
        print event

This causes memory usage to increase steadily to 4 GB or so, at which point the rows print rapidly. The lengthy delay before the first row printed surprised me – I expected it to print almost instantly.

I also tried `Event.objects.iterator()` which behaved the same way.

I don&#39;t understand what Django is loading into memory or why it is doing this. I expected Django to iterate through the results at the database level, which&#39;d mean the results would be printed at roughly a constant rate (rather than all at once after a lengthy wait).

What have I misunderstood?

(I don&#39;t know whether it&#39;s relevant, but I&#39;m using PostgreSQL.)

Why is iterating through a large Django QuerySet consuming massive amounts of memory?

suppose we have a model in django defined as follows:

    class Literal:
        name = models.CharField(...)
        ...

Name field is not unique, and thus can have duplicate values. I need to accomplish the following task:
Select all rows from the model that have **at least one duplicate value** of the ``name`` field.

I know how to do it using plain SQL (may be not the best solution):

    select * from literal where name IN (
        select name from literal group by name having count((name)) &gt; 1
    );

So, is it possible to select this using django ORM? Or better SQL solution?



Django select only rows with duplicate field values

I&#39;m curious if there&#39;s any way to do a query in Django that&#39;s not a &quot;`SELECT * FROM...`&quot; underneath. I&#39;m trying to do a &quot;`SELECT DISTINCT columnName FROM ...`&quot; instead.

Specifically I have a model that looks like:

&lt;!-- language: lang-py --&gt;

    class ProductOrder(models.Model):
       Product  = models.CharField(max_length=20, promary_key=True)
       Category = models.CharField(max_length=30)
       Rank = models.IntegerField()

&lt;!-- end snippet --&gt;

where the `Rank` is a rank within a `Category`. I&#39;d like to be able to iterate over all the Categories doing some operation on each rank within that category.

I&#39;d like to first get a list of all the categories in the system and then query for all products in that category and repeat until every category is processed.

I&#39;d rather avoid raw SQL, but if I have to go there, that&#39;d be fine. Though I&#39;ve never coded raw SQL in Django/Python before.

Select DISTINCT individual columns in django?

I am trying to filter a bunch of objects through a many-to-many relation. Because the `trigger_roles` field may contain multiple entries I tried the `contains` filter. But as that is designed to be used with strings I&#39;m pretty much helpless how i should filter this relation (you can ignore the `values_list()` atm.).

This function is attached to the user profile:

    def getVisiblePackages(self):
        visiblePackages = {}   
        for product in self.products.all():
            moduleDict = {}
            for module in product.module_set.all():
                pkgList = []
                involvedStatus = module.workflow_set.filter(trigger_roles__contains=self.role.id,allowed=True).values_list(&#39;current_state&#39;, flat=True)

My workflow model looks like this (simplified):

    class Workflow(models.Model):
        module = models.ForeignKey(Module)
        current_state = models.ForeignKey(Status)
        next_state = models.ForeignKey(Status)
        allowed = models.BooleanField(default=False)
        involved_roles = models.ManyToManyField(Role, blank=True, null=True)
        trigger_roles = models.ManyToManyField(Role, blank=True, null=True)

Though the solution might be quiet simple, my brain won&#39;t tell me.

Thanks for your help.

Django filter many-to-many with contains

I have two models like this:

    class User(models.Model):
        email = models.EmailField()
    
    class Report(models.Model):
        user = models.ForeignKey(User)

In reality each model has more fields which are of no consequence to this question.

I want to filter all users who have an email which starts with &#39;a&#39; and have no reports. There will be more `.filter()` and `.exclude()` criteria based on other fields.

I want to approach it like this:

    users = User.objects.filter(email__like = &#39;a%&#39;)
    
    users = users.filter(&lt;other filters&gt;)
    
    users = ???

I would like ??? to filter out users who do not have reports associated with them. How would I do this? If this is not possible as I have presented it, what is an alternate approach?

In a Django QuerySet, how to filter for &quot;not exists&quot; in a many-to-one relationship

I&#39;ve been doing a lot of research lately into using Pyramid with SQLAlchemy versus keeping a current application in Django.  That by itself is an entire debate, but I&#39;m not here to discuss that.

What I do want to know is, why is SQLAlchemy universally considered better than Django ORM?  Almost every, if not every, comparison I&#39;ve found between the two favors SQLAlchemy.  I assume performance is a big one, as the structure of SQLAlchemy lets it translate to SQL much more smoothly.

But, I&#39;ve also heard that with harder tasks, Django ORM is nearly impossible to use.  I want to scope out how huge of an issue this can be.  I&#39;ve been reading one of the reasons to switch to SQLAlchemy is when Django ORM is no longer suiting your needs.

So, in short, could someone provide a query (doesn&#39;t have to be actual SQL syntax) that SQLAlchemy can do, but Django ORM cannot possibly do without adding in additional raw SQL?

**Update**:

I&#39;ve been noticing this question getting quite some attention since I first asked, so I&#39;d like to throw in my extra two cents.

In the end we ended up using SQLAlchemy and I must say I&#39;m happy with the decision.

I&#39;m revisiting this question to provide an additional feature of SQLAlchemy that, so far, I&#39;ve not been able to replicate in Django ORM.  If someone can provide an example of how to do this I&#39;ll gladly eat my words.

Let&#39;s say you want to use some postgresql function, such as similarity(), which provides a fuzzy comparison (see: https://stackoverflow.com/questions/11249635/finding-similar-strings-with-postgresql-quickly - tl;dr input two strings get back a percent similarity).

I&#39;ve done some searching on how to do this using the Django ORM and have found nothing other than using raw sql as seems to be apparent from their documentation: https://docs.djangoproject.com/en/dev/topics/db/sql/.

i.e.

    Model.objects.raw(&#39;SELECT * FROM app_model ORDER BY \
    similarity(name, %s) DESC;&#39;, [input_name])

SQLalchemy, however, has func(), as described here: http://docs.sqlalchemy.org/en/latest/core/sqlelement.html#sqlalchemy.sql.expression.func

    from sqlalchemy import desc, func
    session.query(Model).order_by(func.similarity(Model.name, input_name))

This allows you to generate sql for any defined sql/postgresql/etc function and not require raw sql.

Example of what SQLAlchemy can do, and Django ORM cannot

In Django doc,

&gt;  [```select_related()```](https://docs.djangoproject.com/en/stable/ref/models/querysets/#select-related) &quot;follows&quot; foreign-key relationships, selecting additional related-object data when it executes its query.
&gt;
&gt; [```prefetch_related()```](https://docs.djangoproject.com/en/stable/ref/models/querysets/#prefetch-related) does a separate lookup for each relationship, and does the &quot;joining&quot; in Python.

What does it mean by &quot;doing the joining in python&quot;? Can someone illustrate with an example?

My understanding is that for foreign key relationship, use `select_related`; and for M2M relationship, use `prefetch_related`. Is this correct?

What&#39;s the difference between select_related and prefetch_related in Django ORM?

I have a very simple query: `select * from tbl1 where title not in(&#39;asdasd&#39;, &#39;asdasd&#39;)`. 

How do I translate that to Django? It&#39;s like I want the opposite of: `Table.objects.filter(title__in=myListOfTitles)`


Django equivalent of SQL not in

I know [Django does not support foreign keys across multiple databases](https://docs.djangoproject.com/en/1.11/topics/db/multi-db/#no-cross-database-relations) (originally Django 1.3 docs)

But I&#39;m looking for a workaround.

##What doesn&#39;t work

I have two models each on a separate database.

routers.py:

    class NewsRouter(object):
        def db_for_read(self, model, **hints):
            if model._meta.app_label == &#39;news_app&#39;:
                return &#39;news_db&#39;
            return None

        def db_for_write(self, model, **hints):
            if model._meta.app_label == &#39;news_app&#39;:
                return &#39;news_db&#39;
            return None

        def allow_relation(self, obj1, obj2, **hints):
            if obj1._meta.app_label == &#39;news_app&#39; or obj2._meta.app_label == &#39;news_app&#39;:
                return True
            return None

        def allow_syncdb(self, db, model):
            if db == &#39;news_db&#39;:
                return model._meta.app_label == &#39;news_app&#39;
            elif model._meta.app_label == &#39;news_app&#39;:
                return False
            return None


Model 1 in fruit_app/models.py:

    from django.db import models

    class Fruit(models.Model):
        name = models.CharField(max_length=20)

Model 2 in news_app/models.py:

    from django.db import models

    class Article(models.Model):
        fruit = models.ForeignKey(&#39;fruit_app.Fruit&#39;)
        intro = models.TextField()

Trying to add a &quot;Article&quot; in the admin gives the following error because it is looking for the `Fruit` model on the wrong database (`&#39;news_db&#39;`):
    
    DatabaseError at /admin/news_app/article/add/

    (1146, &quot;Table &#39;fkad_news.fruit_app_fruit&#39; doesn&#39;t exist&quot;)

##Method 1: subclass IntegerField

I created a custom field, ForeignKeyAcrossDb, which is a subclass of IntegerField. Code is on github at: https://github.com/saltycrane/django-foreign-key-across-db-testproject/tree/integerfield_subclass

fields.py:

    from django.db import models


    class ForeignKeyAcrossDb(models.IntegerField):
        &#39;&#39;&#39;
        Exists because foreign keys do not work across databases
        &#39;&#39;&#39;
        def __init__(self, model_on_other_db, **kwargs):
            self.model_on_other_db = model_on_other_db
            super(ForeignKeyAcrossDb, self).__init__(**kwargs)

        def to_python(self, value):
            # TODO: this db lookup is duplicated in get_prep_lookup()
            if isinstance(value, self.model_on_other_db):
                return value
            else:
                return self.model_on_other_db._default_manager.get(pk=value)

        def get_prep_value(self, value):
            if isinstance(value, self.model_on_other_db):
                value = value.pk
            return super(ForeignKeyAcrossDb, self).get_prep_value(value)

        def get_prep_lookup(self, lookup_type, value):
            # TODO: this db lookup is duplicated in to_python()
            if not isinstance(value, self.model_on_other_db):
                value = self.model_on_other_db._default_manager.get(pk=value)

            return super(ForeignKeyAcrossDb, self).get_prep_lookup(lookup_type, value)

And I changed my Article model to be:

    class Article(models.Model):
        fruit = ForeignKeyAcrossDb(Fruit)
        intro = models.TextField()

The problem is, sometimes when I access Article.fruit, it is an integer, and sometimes it is the Fruit object. I want it to always be a Fruit object. What do I need to do to make accessing Article.fruit always return a Fruit object?

As a workaround for my workaround, I added a `fruit_obj` property, but I would like to eliminate this if possible:

    class Article(models.Model):
        fruit = ForeignKeyAcrossDb(Fruit)
        intro = models.TextField()

        # TODO: shouldn&#39;t need fruit_obj if ForeignKeyAcrossDb field worked properly
        @property
        def fruit_obj(self):
            if not hasattr(self, &#39;_fruit_obj&#39;):
                # TODO: why is it sometimes an int and sometimes a Fruit object?
                if isinstance(self.fruit, int) or isinstance(self.fruit, long):
                    print &#39;self.fruit IS a number&#39;
                    self._fruit_obj = Fruit.objects.get(pk=self.fruit)
                else:
                    print &#39;self.fruit IS NOT a number&#39;
                    self._fruit_obj = self.fruit
            return self._fruit_obj

        def fruit_name(self):
            return self.fruit_obj.name


##Method 2: subclass ForeignKey field

As a second attempt, I tried subclassing the ForeignKey field. I modified `ReverseSingleRelatedObjectDescriptor` to use the database specified by `forced_using` on the model manager of `Fruit`.  I also removed the `validate()` method on the `ForeignKey` subclass. This method did not have the same problem as method 1. Code on github at: https://github.com/saltycrane/django-foreign-key-across-db-testproject/tree/foreignkey_subclass

fields.py:

    from django.db import models
    from django.db import router
    from django.db.models.query import QuerySet


    class ReverseSingleRelatedObjectDescriptor(object):
        # This class provides the functionality that makes the related-object
        # managers available as attributes on a model class, for fields that have
        # a single &quot;remote&quot; value, on the class that defines the related field.
        # In the example &quot;choice.poll&quot;, the poll attribute is a
        # ReverseSingleRelatedObjectDescriptor instance.
        def __init__(self, field_with_rel):
            self.field = field_with_rel

        def __get__(self, instance, instance_type=None):
            if instance is None:
                return self

            cache_name = self.field.get_cache_name()
            try:
                return getattr(instance, cache_name)
            except AttributeError:
                val = getattr(instance, self.field.attname)
                if val is None:
                    # If NULL is an allowed value, return it.
                    if self.field.null:
                        return None
                    raise self.field.rel.to.DoesNotExist
                other_field = self.field.rel.get_related_field()
                if other_field.rel:
                    params = {&#39;%s__pk&#39; % self.field.rel.field_name: val}
                else:
                    params = {&#39;%s__exact&#39; % self.field.rel.field_name: val}

                # If the related manager indicates that it should be used for
                # related fields, respect that.
                rel_mgr = self.field.rel.to._default_manager
                db = router.db_for_read(self.field.rel.to, instance=instance)
                if getattr(rel_mgr, &#39;forced_using&#39;, False):
                    db = rel_mgr.forced_using
                    rel_obj = rel_mgr.using(db).get(**params)
                elif getattr(rel_mgr, &#39;use_for_related_fields&#39;, False):
                    rel_obj = rel_mgr.using(db).get(**params)
                else:
                    rel_obj = QuerySet(self.field.rel.to).using(db).get(**params)
                setattr(instance, cache_name, rel_obj)
                return rel_obj

        def __set__(self, instance, value):
            raise NotImplementedError()

    class ForeignKeyAcrossDb(models.ForeignKey):

        def contribute_to_class(self, cls, name):
            models.ForeignKey.contribute_to_class(self, cls, name)
            setattr(cls, self.name, ReverseSingleRelatedObjectDescriptor(self))
            if isinstance(self.rel.to, basestring):
                target = self.rel.to
            else:
                target = self.rel.to._meta.db_table
            cls._meta.duplicate_targets[self.column] = (target, &quot;o2m&quot;)

        def validate(self, value, model_instance):
            pass

fruit_app/models.py:

    from django.db import models


    class FruitManager(models.Manager):
        forced_using = &#39;default&#39;


    class Fruit(models.Model):
        name = models.CharField(max_length=20)

        objects = FruitManager()


news_app/models.py:

    from django.db import models

    from foreign_key_across_db_testproject.fields import ForeignKeyAcrossDb
    from foreign_key_across_db_testproject.fruit_app.models import Fruit


    class Article(models.Model):
        fruit = ForeignKeyAcrossDb(Fruit)
        intro = models.TextField()

        def fruit_name(self):
            return self.fruit.name


##Method 2a: Add a router for fruit_app

This solution uses an additional router for `fruit_app`. This solution does not require the modifications to `ForeignKey` that were required in Method 2. After looking at Django&#39;s default routing behavior in [`django.db.utils.ConnectionRouter`](http://code.djangoproject.com/browser/django/tags/releases/1.3/django/db/utils.py#L125), we found that even though we expected `fruit_app` to be on the `&#39;default&#39;` database by default, the `instance` hint passed to `db_for_read` for foreign key lookups put it on the `&#39;news_db&#39;` database. We added a second router to ensure `fruit_app` models were always read from the `&#39;default&#39;` database.  A `ForeignKey` subclass is only used to &quot;fix&quot; the `ForeignKey.validate()` method. (If Django wanted to support foreign keys across databases, I would say this is a Django bug.)
Code is on github at: https://github.com/saltycrane/django-foreign-key-across-db-testproject

routers.py:

    class NewsRouter(object):
        def db_for_read(self, model, **hints):
            if model._meta.app_label == &#39;news_app&#39;:
                return &#39;news_db&#39;
            return None

        def db_for_write(self, model, **hints):
            if model._meta.app_label == &#39;news_app&#39;:
                return &#39;news_db&#39;
            return None

        def allow_relation(self, obj1, obj2, **hints):
            if obj1._meta.app_label == &#39;news_app&#39; or obj2._meta.app_label == &#39;news_app&#39;:
                return True
            return None

        def allow_syncdb(self, db, model):
            if db == &#39;news_db&#39;:
                return model._meta.app_label == &#39;news_app&#39;
            elif model._meta.app_label == &#39;news_app&#39;:
                return False
            return None


    class FruitRouter(object):
        def db_for_read(self, model, **hints):
            if model._meta.app_label == &#39;fruit_app&#39;:
                return &#39;default&#39;
            return None

        def db_for_write(self, model, **hints):
            if model._meta.app_label == &#39;fruit_app&#39;:
                return &#39;default&#39;
            return None

        def allow_relation(self, obj1, obj2, **hints):
            if obj1._meta.app_label == &#39;fruit_app&#39; or obj2._meta.app_label == &#39;fruit_app&#39;:
                return True
            return None

        def allow_syncdb(self, db, model):
            if db == &#39;default&#39;:
                return model._meta.app_label == &#39;fruit_app&#39;
            elif model._meta.app_label == &#39;fruit_app&#39;:
                return False
            return None

fruit_app/models.py:

    from django.db import models


    class Fruit(models.Model):
        name = models.CharField(max_length=20)

news_app/models.py:

    from django.db import models

    from foreign_key_across_db_testproject.fields import ForeignKeyAcrossDb
    from foreign_key_across_db_testproject.fruit_app.models import Fruit


    class Article(models.Model):
        fruit = ForeignKeyAcrossDb(Fruit)
        intro = models.TextField()

        def fruit_name(self):
            return self.fruit.name

fields.py:

    from django.core import exceptions
    from django.db import models
    from django.db import router


    class ForeignKeyAcrossDb(models.ForeignKey):

        def validate(self, value, model_instance):
            if self.rel.parent_link:
                return
            models.Field.validate(self, value, model_instance)
            if value is None:
                return

            using = router.db_for_read(self.rel.to, instance=model_instance)  # is this more correct than Django&#39;s 1.2.5 version?
            qs = self.rel.to._default_manager.using(using).filter(
                    **{self.rel.field_name: value}
                 )
            qs = qs.complex_filter(self.rel.limit_choices_to)
            if not qs.exists():
                raise exceptions.ValidationError(self.error_messages[&#39;invalid&#39;] % {
                    &#39;model&#39;: self.rel.to._meta.verbose_name, &#39;pk&#39;: value})


##Additional information

 - Thread on the django-users list which has a lot of information: http://groups.google.com/group/django-users/browse_thread/thread/74bcd1afdeb2f0/0fdfce061124b915
 - Revision history for the multi-db documentation: http://code.djangoproject.com/log/django/trunk/docs/topics/db/multi-db.txt?verbose=on


##Update

We implemented the last method after tweaking our routers some more. The whole implementation has been pretty painful which makes us think that we must be doing it wrong. On the TODO list is writing unit tests for this.


How to work around lack of support for foreign keys across databases in Django

In my model, I want a field that has a list of triplets. e.g. `[[1, 3, 4], [4, 2, 6], [8, 12, 3], [3, 3, 9]]`. Is there a field that can store this data in the database?

List field in model?

I am new to Django and didn&#39;t find any reference regarding this issue. I am getting this error when i use many to many field in Django model (`models.py`). I guess the issue is assigning m2m field in view(`views.py`) from form(`forms.py`). 

How to assign m2m field in view?
(`Django version 2.0`, `python - 3.5`)

**models.py**
   
    class User(AbstractUser):
     username=models.CharField(max_length=20)
     email = models.EmailField(_(&#39;email address&#39;), unique=True)
 

    class Setupuser(models.Model):
     organization=models.CharField(max_length=200,blank=False,null=True)
     emails_for_help = models.ManyToManyField(User)


**views.py**
    
    class Set_user(FormView):
     template_name=&quot;pkm_templates/set_up_user.html&quot;
     form_class = Set_User_Form
     success_url = &#39;/thanks/&#39;

     def form_valid(self, form):
        org = form.cleaned_data.get(&#39;organization&#39;)
        emails = form.cleaned_data.get(&quot;emails_for_help&quot;)
        instance = Setupuser(organization=org,emails_for_help=emails)
        instance.save()
        return redirect(&quot;/&quot;)
**forms.py**

    class Set_User_Form(ModelForm):
      emails_for_help = forms.ModelMultipleChoiceField(
        queryset=User.objects.all(),
        widget=forms.CheckboxSelectMultiple
      )
     
      class Meta:
        model = Setupuser
        fields = [&quot;organization&quot;,&quot;emails_for_help&quot;]



Direct assignment to the forward side of a many-to-many set is prohibited. Use emails_for_help.set() instead

I want to write a Django query equivalent to this SQL query:

    SELECT * from user where income &gt;= 5000 or income is NULL.

How to construct the Django queryset filter?

    User.objects.filter(income__gte=5000, income=0)

This doesn&#39;t work, because it `AND`s the filters. I want to `OR` the filters to get union of individual querysets.

How to perform OR condition in django queryset?

How does one convert a django Model object to a dict with **all** of its fields?  All ideally includes foreign keys and fields with editable=False.

Let me elaborate.  Let&#39;s say I have a django model like the following:

    from django.db import models

    class OtherModel(models.Model): pass

    class SomeModel(models.Model):
        normal_value = models.IntegerField()
        readonly_value = models.IntegerField(editable=False)
        auto_now_add = models.DateTimeField(auto_now_add=True)
        foreign_key = models.ForeignKey(OtherModel, related_name=&quot;ref1&quot;)
        many_to_many = models.ManyToManyField(OtherModel, related_name=&quot;ref2&quot;)

In the terminal, I have done the following:

    other_model = OtherModel()
    other_model.save()
    instance = SomeModel()
    instance.normal_value = 1
    instance.readonly_value = 2
    instance.foreign_key = other_model
    instance.save()
    instance.many_to_many.add(other_model)
    instance.save()

I want to convert this to the following dictionary:

    {&#39;auto_now_add&#39;: datetime.datetime(2015, 3, 16, 21, 34, 14, 926738, tzinfo=&lt;UTC&gt;),
     &#39;foreign_key&#39;: 1,
     &#39;id&#39;: 1,
     &#39;many_to_many&#39;: [1],
     &#39;normal_value&#39;: 1,
     &#39;readonly_value&#39;: 2}

----------
Questions with unsatisfactory answers:

https://stackoverflow.com/questions/1123337/django-converting-an-entire-set-of-a-models-objects-into-a-single-dictionary

https://stackoverflow.com/questions/12382546/how-can-i-turn-django-model-objects-into-a-dictionary-and-still-have-their-forei

Convert Django Model object to dict with all of the fields intact

I am getting order_by fields in the form of a list.
I want to order_by by multiple fields with django orm.
List is like below:
 

    orderbyList = [&#39;check-in&#39;,&#39;check-out&#39;,&#39;location&#39;]

I am writing a query is like: 

    modelclassinstance.objects.all().order_by(*orderbyList)

Everything i am expecting in a list is dynamic. I don&#39;t have predifined set of data.Could some tell me how to write a django ORM with this?

Django: Order_by multiple fields

I&#39;m making a QA site that is similar to the page you&#39;re on right now.  I&#39;m attempting to order answers by their score, but answers which have no votes are having their score set to None rather than 0.  This results in answers with no votes being at the bottom of the page below negatively ranked answers.  How can I make the annotated score be zero when there are no votes for an answer?

Here&#39;s my model:

    from django.contrib.auth.models import User
    
    Answer(models.Model):
        //some fields here
        pass

    VOTE_CHOICES = ((-1, Down), (1, Up))
    
    Vote(models.Model):
        user = models.ForeignKey(User)
        answer = models.ForeignKey(Answer)
        type = models.IntegerField(choices = VOTE_CHOICES)
    
        class Meta:
            unique_together = (user, answer)

And here&#39;s my query:

    answers = Answer.objects.filter(&lt;something here&gt;)
                            .annotate(score=Sum(&#39;vote__type&#39;))
                            .order_by(&#39;-score&#39;)

edit: And to be clear, I&#39;d like to do this in the query.  I know I could turn it into a list and then sort it in my python code, but I&#39;d like to avoid that if possible.

Annotating a Sum results in None rather than zero

I am using Django 1.6 with Mysql.

I have these models:

    class Student(models.Model):
         username = models.CharField(max_length=200, unique = True)

    class Score(models.Model)
         student = models.ForeignKey(Student)
         date = models.DateTimeField()
         score = models.IntegerField()

I want to get the latest score record for each student.  
I have tried:

    Score.objects.values(&#39;student&#39;).annotate(latest_date=Max(&#39;date&#39;))

and:

    Score.objects.values(&#39;student__username&#39;).annotate(latest_date=Max(&#39;date&#39;))

as described [Django ORM - Get the latest record for the group][1]
but it did not help.


  [1]: https://stackoverflow.com/questions/17887075/django-orm-get-latest-record-for-group

Django orm get latest for each group

I am trying to find out the number of queries executed by a utility function. I have written a unit test for this function and the function is working well. What I would like to do is track the number of SQL queries executed by the function so that I can see if there is any improvement after some refactoring.

    def do_something_in_the_database():
        # Does something in the database
        # return result
    
    class DoSomethingTests(django.test.TestCase):
        def test_function_returns_correct_values(self):
            self.assertEqual(n, &lt;number of SQL queries executed&gt;)

EDIT: I found out that there is a pending Django [feature request][1] for this. However the ticket is still open. In the meantime is there another way to go about this?


  [1]: http://code.djangoproject.com/ticket/5416

Django: is there a way to count SQL queries from an unit test?

**Short Question**  
What is the default order of a list returned from a Django filter call when connected to a PostgreSQL database?  

**Background**  
By my own admission, I *had* made a poor assumption at the application layer in that the order in which a list is returned will be constant, that is without using &#39;order_by&#39;.  The list of items I was querying is not in alphabetic order or any other **deliberate** order.  It was thought to remain in the same order as which they were added to the database.  

This assumption held true for hundreds of queries, but a failure was reported by my application when the order changed unknowingly.  To my knowledge, none of these records were touched during this time as I am the only person who maintains the DB.  To add to the confusion, when running the Django app on Mac OS X, it still worked as expected, but on Win XP, it changed the order.  (Note that the mentioned hundreds of queries was on Win XP).  

Any insight to this would be helpful as I could not find anything in the Django or PostgreSQL documentation that explained the differences in operating systems. 

**Example Call**  

    required_tests = Card_Test.objects.using(get_database()).filter(name__icontains=key)


**EDIT**  
After speaking with some colleague&#39;s of mine today, I had come up with the same answer as Bj&#246;rn Lindqvist.  

Looking back, I definitely understand why this is done wrong so often.  One of the benefits to using an ORM Django, sqlalchemy, or whatever is that you can write commands without having to know or understand (in detail) the database it&#39;s connected to.  Admittedly I happen to have been one of these users.  However on the flip-side of this is that without knowing the database in detail debugging errors like this are quite troublesome and potentially catastrophic.  

What is the default order of a list returned from a Django filter call?

I have a model something like this:

```python
class Task(models.Model):
    progress = models.PositiveIntegerField()
    estimated_days = models.PositiveIntegerField()
```

Now I would like to do a calculation `Sum(progress * estimated_days)` on the database level. Using Django Aggregation I can have the sum for each field but not the summation of multiplication of fields. 

All Django Orm Solutions on Gang of Coders

Total of 29 Django Orm Solutions