Using WhooshAlchemy with joined table inheritance

Question

I currently have something along the lines of this (taken from the SQLAlchemy documentation):

class Employee(Base):
    __tablename__ = 'employee'
    id = Column(Integer, primary_key=True)
    name = Column(String(50))
    type = Column(String(50))

    __mapper_args__ = {
        'polymorphic_identity':'employee',
        'polymorphic_on':type
    }

class Engineer(Employee):
    __tablename__ = 'engineer'
    id = Column(Integer, ForeignKey('employee.id'), primary_key=True)
    engineer_name = Column(String(30))

    __mapper_args__ = {
        'polymorphic_identity':'engineer',
    }

class Manager(Employee):
    __tablename__ = 'manager'
    id = Column(Integer, ForeignKey('employee.id'), primary_key=True)
    manager_name = Column(String(30))

    __mapper_args__ = {
        'polymorphic_identity':'manager',
    }

Using WhooshAlchemy, I'd like to make the name column searchable, but also engineer_name and manager_name for the Engineer and Manager class respectively.

I've tried putting a separate __searchable__ list containing their columns into each class, but that gives me the following error:

whoosh.fields.UnknownFieldError: No field named 'name' in <Schema: ['id', 'engineer_name']>

I've also tried putting a __searchable__ list into each one that only contains the columns defined on that specific class, but that gives me no results at all when I use whoosh_search on a query, despite having valid search terms that should return at least 1 result.

What's the easiest way to do this?

score 0 · Answer 1 · answered Jul 08 '17 at 23:28

You have to explicitly define a schema that can handle all of them. You would override the searchable fields on the inherited classes.

class Employee(Base):
    __tablename__ = 'employee'
    __searchable__ = ['name']
    id = Column(Integer, primary_key=True)
    name = Column(String(50))
    type = Column(String(50))

    __mapper_args__ = {
        'polymorphic_identity':'employee',
        'polymorphic_on':type
    }

class Engineer(Employee):
    __tablename__ = 'engineer'
    __searchable__ = ['name', 'engineer_name']
    id = Column(Integer, ForeignKey('employee.id'), primary_key=True)
    engineer_name = Column(String(30))

    __mapper_args__ = {
        'polymorphic_identity':'engineer',
    }

class Manager(Employee):
    __tablename__ = 'manager'
    __searchable__ = ['name', 'manager_name ']
    id = Column(Integer, ForeignKey('employee.id'), primary_key=True)
    manager_name = Column(String(30))

    __mapper_args__ = {
        'polymorphic_identity':'manager',
    }

Then when you build your index:

from whooshalchemy import IndexService
from whoosh.fields import Schema, TEXT

# However your app initializes it
index_service = IndexService(config=app.config, session=db.session)  

my_schema = Schema(name=TEXT, engineer_name=TEXT, manager_name=TEXT)
index = index_service.index_for_model_class(model)

with index.writer() as writer:

    for engineer in Engineer.query.all():
        index_attrs = {}
        for field in Engineer.__searchable__:
            index_attrs[field] = str(getattr(engineer, field))

        index_attrs["id"] = engineer.id

        writer.add_document(**index_attrs)

    for manager in Manager.query.all():
        index_attrs = {}
        for field in Manager.__searchable__:
            index_attrs[field] = str(getattr(manager, field))

        index_attrs["id"] = manager.id

        writer.add_document(**index_attrs)

And later when searching:

engineer_search_results = Engineer.search_query("SomeText").all()
manager_search_results = Manager.search_query("SomeText").all()

Renjie Cai · Answer 2 · 2016-02-03T05:53:05.730

the flask-whooshalchemy dosen't

if you use flask too, you can try my fork of flask_whooshalchemy named Flask-WhooshAlchemyPlus which can index inherited model

you can add searchable of its own field names to each model

I'll just paste the code here

def _get_whoosh_schema_and_primary_key(model, analyzer):
    schema = {}
    primary = None
    searchable = set(model.__searchable__)

    for field in model.__table__.columns:
        if field.primary_key:
            schema[field.name] = whoosh.fields.ID(stored=True, unique=True)
            primary = field.name

        if field.name in searchable and isinstance(field.type,
                                               (sqlalchemy.types.Text,
                                                sqlalchemy.types.String,
                                                sqlalchemy.types.Unicode)):
            schema[field.name] = whoosh.fields.TEXT(
                analyzer=analyzer, vector=True)

    for parent_class in model.__bases__:
        if hasattr(parent_class, "_sa_class_manager"):
            if parent_class.__searchable__:
                for i in set(parent_class.__searchable__):
                    if hasattr(parent_class, i):
                        schema[i] = whoosh.fields.TEXT(
                            analyzer=analyzer, vector=True)

    return Schema(**schema), primary

Using WhooshAlchemy with joined table inheritance

2 Answers2