CODEWITHSUNDEEP
pythonsqldatabasedjangoorm
FrostNovaZzz
FrostNovaZzz

Reputation: 832

Doing database JOIN in django

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)?

Upvotes: 0

Views: 2967

Answers (2)

Mike DeSimone
Mike DeSimone

Reputation: 42805

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.

Upvotes: 2

SingleNegationElimination
SingleNegationElimination

Reputation: 156138

(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

Upvotes: 2

Related Questions