search

miLibris / flask-rest-jsonapi

Flask extension to build REST APIs around JSONAPI 1.0 specification.
http://flask-rest-jsonapi.readthedocs.io
MIT License
597 stars 153 forks source link

How to apply a database filter to an included relationship? #55

Closed michaelgodshall closed 7 years ago

michaelgodshall commented 7 years ago

My include api request looks like this: http://127.0.0.1:5000/api/products?include=favorites

It's working correctly and includes favorites for products in the payload. However, I'd like to only include favorites for products that the current user has favorited. Where is the right place to set this up? The schema/view/data layer? I've looked through the source code quite a bit and can't seem to find the right way to filter the database query for the included favorites relationship (using SqlAlchemy). Any recommendations on how to approach this?

anuragagarwal561994 commented 7 years ago

I have 2 recommendations for solving this problem:

  1. The simplest solution would be to edit the data layer and change the query method (in list) and before_get_object method (in detail). Your query should relate the current user and join on the table fetching the resources according to user. Why data layer is because, it will only fetch the user related records. Let us say if you have 1000 records with 10 records belonging to 100 users. You will directly fetch those 10 records instead of querying in view layer and removing those other 990 records.

  2. Define row level security at database level. This is even simpler (but difficult to setup at first) and more secure way of solving this problem. I use this in most of my use cases but I am not aware about the feature compatibility with other databases. Postgres supports it. You can follow the code on this link to know how things work. It can be quite difficult to understand at first. So in brief this is what it does: a. There is a data schema which stores all your main tables. b. Subset of these tables are viewed in api layer which is being accessed by your code. Thus data layer is completely protected. c. api role is given access to edit the data tables (but not all you can control it). d. Roles are stored as database types and are created on database initialisations. They are granted to authenticator role which has access to only switch roles I suppose (not clear about this). e. Other roles are given access to edit the api schema views and also row level security is enabled which doing that.

So basically you can control which columns can a user see and which rows a user can see or edit with this. It will be difficult to set up but secure and fun to learn. For your view layer it will automatically send you back 10 records only provided it knows who is trying to access the records.

akira-dev commented 7 years ago

You can customize the query according to includes in your resource manager or you can create a dedicated relationship field in your model and customize to primaryjoin parameter to add filter (http://docs.sqlalchemy.org/en/latest/orm/join_conditions.html#specifying-alternate-join-conditions).