Doing database JOIN in django

Question

I want to do equivalent query as SELECT user_name, item_name FROM users, items WHERE users.favor_item_id = items.item_id, which is to return user_name and item_name pairs. In the database, one user can have multiple favoured items.

I'm just curious what is a equivalent Django query for this SQL query??

My first thought is to list all (user, favor_item_id) pair from USERS, and then look for item_name with id item_id. But it seems that it will search the ITEM table N times(N is the number of pairs), which has complexity of O(NlogM)(M is number of items in ITEM), while using the SQL query above, the complexity is O(N).

Is there a more efficient way of doing this in django(or any ORM system)?

In your query, you say that the favorite item of a user is a column on the users relation, but in the very next sentence, you say that a user can have more than one favorite item. How is that possible, unless a single user can appear more than once in the users table? — SingleNegationElimination
– SingleNegationElimination, Commented Jul 4, 2011 at 3:22

SingleNegationElimination · Accepted Answer · 2011-07-04 03:03:14Z

2

(or any ORM system)?

Really? here's how you'd do it in sqlalchemy

Assuming reasonable mapped classes (here, using declarative):

Base = sqlalchemy.ext.declarative.declarative_base()

class Item(Base):
    __tablename__ = 'items'
    id = Column('item_id', Integer, primary_key=True)
    name = Column('item_name', String)

class User(Base):
    __tablename__ = 'users'
    name = Column('user_name', String(50), primary_key=True)
    favor_item_id = Column(Integer, ForeignKey(Item.id))

    favor_item = relationship(Item, backref="favored_by")

The query is super simple

>>> print sqlalchemy.orm.Query((User.name, Item.name)).join(Item.favored_by)
SELECT users.user_name AS users_user_name, items.item_name AS items_item_name 
FROM items JOIN users ON items.item_id = users.favor_item_id

answered Jul 4, 2011 at 3:03

SingleNegationElimination

157k35 gold badges269 silver badges306 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mike DeSimone · Accepted Answer · 2011-07-04 03:21:05Z

Assuming the model:

class User(models.Model):
    name = models.CharField(max_length=32)
    favorite = models.ForeignKey(Item)

class Item(models.Model):
    name = models.CharField(max_length=32)

The equivalent Django would be:

usersFavorites = User.objects.all().values_list("name", "favorite__name")

This doesn't use the exact names of the fields that you gave. For that, you just need to add appropriate db_table and db_column fields to the models.

P.S. This particular schema is not particularly good. IMHO, User and Item should have a many-to-many relationship:

class User(models.Model):
    name = models.CharField(max_length=32)
    favorites = models.ManyToManyField(Item, related_name="users")

class Item(models.Model):
    name = models.CharField(max_length=32)

unless an item can only have one user, in which case the ForeignKey should be in Item.

Then you can iterate in a double loop:

for user in User.objects.all().select_related("favorites"):
    for favorite in user.favorites:
        # use `user` and `favorite` in some way.

Because of the select_related() call, the query is done in one fell swoop in the first for loop.

Collectives™ on Stack Overflow

Doing database JOIN in django

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related