CODEWITHSUNDEEP
pythonpython-3.xpostgresqlsqlalchemyfull-text-search
magerine
magerine

Reputation: 53

Generic Full Text Search

I have a Postgresql database. I am trying to make the following easy:

  1. Searching a table that has relationships. I am not sure how to 'index' the relationships. For example:

Suppose I have the following:

class Book(Base):
    id = Column(Integer, primary_key=True)
    chapters = relationship("Chapter", backref="book")

class Chapter(Base):
    id = Column(Integer, primary_key=True)
    name = Column(String)
    book_id = Column(Integer, ForeignKey(Book.id))

def generic_search(self, query_string, indexes): 
    
    """
    Search for database tables using ts_vector column and ts_query
    """
    search_term = query_string
    session = self.session

    
     print("Searching ...")

     table = str(indexes[0])
     model_name = self.get_class_by_table_name(table)
        
     query = session.query(model)

      
     query_result = (
            
     try:    
       results = 
        session.query(model_name).filter(model_name.__ts_vector__.match(search_term))
        ).all()
       
           
     except DatabaseError:
        transaction.rollback()

Edit: I want to make chapters searchable on the Books model (Basically search for a string in the chapters column via the Books model. In short I want to be able to search a table and all it's related tables for a specific string.

Upvotes: 1

Views: 714

Answers (1)

snakecharmerb
snakecharmerb

Reputation: 55610

When using Postgresql as the back end, full text search can be performed using a column's match attribute. A simple filter would look like

Chapter.name.match(search_term)

which is equivalent to the Postgresql expression

name @@ to_ts_query(search_term)

It follows that search_term must be in a form that to_tsquery will accept*.

To find whether a Book contains any chapters that match the search term(s) we can do:

with Session() as s:
    result = (
        s.query(Book)
        .filter(
            Book.chapters.any(
                Chapter.name.match(search_term)
            )
        )
        .all()
    )
    print(result)

Alternatively, if we want to check which chapters in a particular book match the search terms we could do:

with Session() as s:
    # Get a book
    book = s.get(Book, 1)
    result = (
        s.query(Chapter)
        .filter(
            sa.and_(
                Chapter.book == book,
                Chapter.name.match(search_term),
            )
        )
        .all()
    )
    print(result)

The respective queries in SQLAlchemy 2.0 style would be:

with Session() as s:

    # Find books with matching chapters.
    result = s.scalars(
        sa.select(Book).where(
            Book.chapters.any(
                Chapter.name.match(search_term)
            )
        )
    ).all()
    print(result)
    # Find matching chapters for a book.
    result = s.scalars(
        sa.select(Chapter).where(
            sa.and_(
                Chapter.book == book,
                Chapter.name.match(search_term),
            )
        )
    ).all()
    print(result)

* The tip at the end of the linked documentation section provides the sensible advice

It’s important to remember that text searching in PostgreSQL is powerful but complicated, and SQLAlchemy users are advised to reference the PostgreSQL documentation regarding Full Text Search.

Upvotes: 2

Related Questions