How to do a query using pyhton list (must have all OR must have at least one element) on a many to many structure with SQLAlchemy?

I'll create an not specific structure so the problem became more easy to understand. Considering a DB structure that store books information:

from flask_sqlalchemy import SQLAlchemy
from flask_marshmallow import Marshmallow

db = SQLAlchemy()

#Base class to create some good pratices fields
class Base(db.Model):
    __abstract__  = True

    id            = db.Column(db.Integer, primary_key=True)
    date_created  = db.Column(db.DateTime,  default=db.func.current_timestamp())
    date_modified = db.Column(db.DateTime,  default=db.func.current_timestamp(),
                                           onupdate=db.func.current_timestamp())
class Book(Base):
    name = db.Column(db.String(255), nullable=False)
    pages = db.Column(db.Integer, nullable=True)
    tags = db.relationship('BooksTags', back_populates="book")

class BooksTags(db.Model):
    __tablename__ = 'book_tag'
    tag_id = db.Column(db.Integer, db.ForeignKey('book.id'), primary_key=True)
    book_id = db.Column(db.Integer, db.ForeignKey('tag.id'), primary_key=True)
    book = db.relationship('Book', back_populates='tags')
    tag = db.relationship('Tag', back_populates='books')

class Tags(Base):
    name = db.Column(db.String(255), nullable=False)
    books = db.relationship('BooksTags', back_populates="tag")

With this code I have a "Many to Many" structure, and the works pretty well for me, but I'm stuck when I try to create queries based on tag, per example:

• How do I query this structure to get all book that has all tags present in a list of tags and a minimum number of pages?
• What if the need to have at least one tag present on the list?

I've been trying to use some aggregation functions like array_agg and group_concat, but I always returns different errors message, I'm not sure how to do it.

On pure SQL I'd query something like this:

SELECT 
  group_concat(tag.name) AS tags, 
  book.name as book_name
FROM 
  book 
  INNER JOIN book_tag ON book.id = book_tag.book_id 
  INNER JOIN tag ON tag.id = book_tag.tag_id 
GROUP BY 
  book.id

But IDK how to filter this query.

Ok after reading and asking some friends helps I've managed to do it with SQL:

In the case of "at least one" tag:

SELECT 
  group_concat(tag.name) AS tags, 
  book.name as book_name
FROM 
  book 
  INNER JOIN book_tag ON book.id = book_tag.book_id 
  INNER JOIN tag ON tag.id = book_tag.tag_id AND tag.name in (<insert tag list>)
GROUP BY 
  book.id

In the case of "must have all" tags:

SELECT 
  group_concat(tag.name) AS tags, 
  book.name as book_name
FROM 
  book 
  INNER JOIN book_tag ON book.id = book_tag.book_id 
  INNER JOIN tag ON tag.id = book_tag.tag_id AND tag.name in (<insert tag list>)
GROUP BY 
  book.id
WHERE
  COUNT(tag.name) > <sizeof tag array>

But I still don't know how to do it with SQLAlchemy.

from Recent Questions - Stack Overflow https://ift.tt/3cI6xdx
https://ift.tt/eA8V8J