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

Question

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.

Answer 1

After my edit it was easier to solve the problem, so to do a select all books that has at least one tag of the array:

tags_id = [1,2,3]
books_query = Book.query.join(BooksTags, Tag)
books_query = books_query.filter(BooksTags.tag_id.in_(tags_id))
books_query = books_query.group_by(Book)

This will return something like this

SELECT 
  book.id as book_id,
  book.name as book_name,
  book.pages as book_pages
FROM
  book
  INNER JOIN book_tag ON book.id = book_tag.book_id 
  INNER JOIN tag ON tag.id = book_tag.tag_id 
WHERE 
  book_tag.tag_id IN (
        1, 
        2,
        3
  ) 
GROUP BY 
  book.id,
  book.name,
  book.pages

And to do a select all books that has all tags of the array:

tags_id = [1,2,3]
books_query = Book.query.join(BooksTags, Tag)
books_query = books_query.filter(BooksTags.tag_id.in_(tags_id))
books_query = books_query.group_by(Book)
books_query = books_query.having(func.count(Book.id) >= len(tags_id))

This will return something like this

SELECT 
  book.id as book_id,
  book.name as book_name,
  book.pages as book_pages
FROM
  book
  INNER JOIN book_tag ON book.id = book_tag.book_id 
  INNER JOIN tag ON tag.id = book_tag.tag_id 
WHERE 
  book_tag.tag_id IN (
        1, 
        2,
        3
  ) 
GROUP BY 
  book.id,
  book.name,
  book.pages
HAVING 
  count(book.id) >= 3