0

I have a bunch of users and want to get a list of all the products they have bought. I currently do this with 

list_of_products = [user.products.all() for user in all_users]

Where the User table and the Product table are connected by a many-to-many relationship. I noticed that this does not scale very well. If the number of users increases, it can take quite a while. I am trying to understand whether this is due to a overhead when performing a call or whether this is just the scaling because more products are needed?

Basically, what is the fastest way to retrieve all products for a list of users.

Just in case, here are the tables

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    ...

    products = db.relationship('product',
                               secondary=sales,
                               lazy='dynamic')

class Product(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    ...

sales = db.Table(
    'sales',
    db.Column('product_id', db.Integer, db.ForeignKey('product.id')),
    db.Column('user_id', db.Integer, db.ForeignKey('user.id'))
)
carl
  • 4,216
  • 9
  • 55
  • 103
  • This is the [N+1 queries problem](https://stackoverflow.com/questions/97197/what-is-n1-select-query-issue). `user.products.all()` performs a separate query for each user. Instead if you had a set of user ids for which you want products, you could perform a single query that fetches all products related to those users. Your association table seems to have no indexes, so that's going to cause some slowdown as well, if you have a lot of associations. – Ilja Everilä Jan 30 '18 at 07:59
  • hi ilja, yes that is exactly my question, how would I combine this in one query? And can you also explain how you would rearrange my db to make it faster (by adding indexes) thanks – carl Jan 30 '18 at 08:08
  • You basically need this: https://stackoverflow.com/questions/6542967/querying-a-many-to-many-relationship-in-sqlalchemy, in one form or another. A simple "rearrangement" to your association table *sales* would be to add both columns as a part of a composite primary key. That'd kill 2 birds with 1 stone: you'd get an index and no duplicates could be inserted. – Ilja Everilä Jan 30 '18 at 08:20

0 Answers0