Joining and labeling data on multiple tables

[alireza...@gmail.com]

Hello,

I have 3 main tables and 3 association tables between them

Main tables: Sites, Categories, Products

Association Tables: Sites<->Categories, Categories<->Products, Sites<->ChosenProducts

Here is a link to diagram:

https://dbdiagram.io/d/5f3a863d39081a20ae94d500

Here is my code:

https://gist.github.com/DarkSuniuM/df1cfaeaf7a0745326653078febedc10

I need to join Site to Category and then Category to Product to get all products associated with a Site object.

It's doable using the following statement:

`session.query(Product).join(Categories.products).join(Categories.sites).filter(Site.id == 1).all()`

I also am able to get chosein products by just getting the site and then using it's `chosen_products` attribute

But I need to label or even create a temporary object for each object

I need to be able to get the difference between available products (by categories for a site) and chosein products for a site

I need to have a runtime(?) attribute on each result like "chosed" and have a boolean value for them based on their existence in chosen_products of website

I need to be able list keywords for each product instead of having multiple objects of the same product cause of multiple categories

I don't even know if the thing I wanna do is the right way to do it...

So,

1. How can I join all these table together and be able to get the difference and intersect and full result?

2. How can I label them based on their existence in ChoseinProducts of a website

3. How can I have intersect of keywords between result (products) and site? (I guess this one should be done using AssociationProxy, but not sure how)

Thanks in advance...