Smart querying performing multiple joins

[AbdealiLoKo]

I have the following db structure:

from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.orm import relationship


class Author(BaseModel):
    __tablename__ = 'author'
    id = Column(Integer, primary_key=True)
    first_name = Column(String(255), nullable=False)
    last_name = Column(String(255), nullable=False)


class Book(BaseModel):
    __tablename__ = 'book'
    id = Column(Integer, primary_key=True)
    name = Column(String(255), nullable=False)
    author_id = Column(Integer, ForeignKey('author.id'), nullable=False)

    author = relationship(Author, lazy='selectin')

And I was trying to do something like the following in django:

Book.query.filter(author__first_name='a').filter(author__last_name='b')

And I tried the following in smart_query, and found that it was doing 2 joins - while Django does just 1 join

>>> from sqlalchemy_mixins.smartquery import smart_query

>>> query = Book.query
>>> query = smart_query(query, filters={'author___name': 'a'})
>>> print(query)
SELECT * FROM book
LEFT OUTER JOIN author_ ON author.id = book.author_id
WHERE author.name = 'a'

>>> query = smart_query(query, filters={'author___name': 'b'})
>>> print(query)
SELECT * FROM book
LEFT OUTER JOIN author_1 ON author_1.id = book.author_id
LEFT OUTER JOIN author_2 ON author_2.id = book.author_id
WHERE author_1.first_name = 'a' AND author_2.last_name = 'b'

This seems like a major issue for me - because it doesn't allow me to chain my filters easily.

Am I missing something ? I was planning on adding smart_query to my BaseQuery in sqlalchemy and use that - but this makes it difficult for me to chain my filters

[lovetoburnswhen]

Any news?

[michaelbukachi]

@lovetoburnswhen I'm a bit swamped at the moment. Feel free to do a PR and I'll take a look.