sql - SQLAlchemy: select all posts that has tags in [..] (many-to-many) -

I have users, interests and events User (many-to-many) interests (many-to-many) Interests are the reason why I have two "intermediate" tables: user_to_interest and event_to_interest.

I would like to select all the events in any way whose interests are from the list of interests of the user (in other words, tag in all events [1, 144] I WHERE event_to_interest.interest_id IN (10, 144, 432)

How should I do this through SQLLame? (IF I am using FlaskSQLKey if necessary)

  user_to_interest = table ('user_to_interest', base); 
 

Version-1: You interest_id s The list should be able to do simple queries, which you basically desire for the SQL statement:

  interest_ids = [10, 144, 432] query = session. Query (Event.name) query = query.join (event_to_interest, event_to_interest.c.event_id == Event.id) query = query.filter (event_to_interest.c.interest_id .in_ (interest_ids))  

However, if there are incidents in which there are two or more interests from the list, then the query will be same as Event.name many times you work around it using different query = session.query (Event.name.distinct ())

Version 2: Alternatively, you can only relate it to relationships , Which EXISTS section with the SQL sub-code , But it should be spoken verbally:

  query = session.query (Event.name) query = query.filter (Event.interests.any (interest.id.in_ ( Interest_ids)))  

There is no problem with duplicates in this version.

However, I go back a step, and believe that you get interest_ids for a specific user, and user_id (or User.id )

Last version will generate a query: use twice:

  def Get_events_for_user (user_id): #query = session.query (Event.name) query = session.query (event) # @ Note: I think the name is not a valid query = query.filter (Event.int Erests.any (interest) .users.any (User.id == user_id)) return query.all ()  

Anyone can do this Process makes it very beautiful SQL statement, but it is exactly the beauty of using Skyuellemi hides the details of implementation.

---

Bonus: You really want to give more priority to those events which are more overlapping interests, in this case, the following help can be found:

  query = session.query (event, func.count ('*'). Label ("num_interests")) query = query.join (interest, event.interests) query = query.join (user, interest. Users) query = query.filter (User.id == user_id) query = query.group_by (event) #The first order by overlapping interests, however by event.date query = query.order_by (func.count ('*') Label ("num_interests"). Desc ()) #query = query.order_by (Event.date)