Enhancement: Are possible do multi-model (joined) QueryBuilder

[MatsiukMykola]

I have difficulty for joining models, but qb_build_statement support only one model
Would you happen to have any plans to extend this essential feature?

Please.... 😭

[MatsiukMykola]

my draft (i hope correct):

class QBRequestBasicModel(BaseModel): # <- Pydantic model
    """Query Builder Basic model"""
    where: Union[Dict[str, Any], str, None] = None
    limit: Optional[int] = None
    skip: Optional[int] = None
    order_by: List[str] | str = None
    with_count: bool = False

    # TODO: examples not render
    class Config:
        orm_mode = True
        schema_extra = {
            'example':
                {
                    'limit': 5,
                    'skip': 1,
                    # can be string or List[str]
                    # 'order_by': 'price desc, name asc',
                    'order_by': ["price desc", "name asc"],

                    # can be string, search-term or dict
                    'where': {"or": [{"price": {"lt": 100}}, {"price": {"gt": 66}}]},

                    # calculate count (need only for pagination), don't use if you don't need them
                    'with_count': True
                }
        }


def build_order_clauses(order_by: List[str], models: List[type]) -> List[ColumnProperty]:
    """Builds SQLAlchemy order_by clauses based on a list of field names and a list of models."""
    order_by_clauses = []
    for field in order_by:
        descending = field.lower().endswith(" desc")
        ascending = field.lower().endswith(" asc")
        if descending or ascending:
            field = field[:-4].strip() if descending else field[:-3].strip()
        for model in models:
            column_property = getattr(model, field, None)
            if column_property is not None and isinstance(column_property.property, ColumnProperty):
                order_by_clause = desc(field) if descending else column_property
                order_by_clauses.append(order_by_clause)
    return order_by_clauses


class QBRequest(QBRequestBasicModel):
    """Query Builder model"""

    def qb_build_statement(self, stmt: Statement, models: Union[List[Model] | Model]):
        """Build QueryBuilder Select statement with WHERE, LIMIT, SKIP, ORDER BY"""

        if not isinstance(models, list):
            models = [models]

        # apply limit and skip
        stmt = stmt.limit(self.limit).offset(self.skip)

        # apply where clauses for each model
        q = None
        for model in models:
            model_q = normalize_where(self.where, model)
            if model_q is not None:
                q = q & model_q if q is not None else model_q

        if q is not None:
            stmt = stmt.where(q)

        # apply order_by clauses for each model
        order_by_clauses = []

        if self.order_by:
            order_by = [field.strip() for field in self.order_by]
            order_by_clauses = build_order_clauses(order_by, models)

        if order_by_clauses:
            stmt = stmt.order_by(*order_by_clauses)

        return stmt

work fine with joined models (where and order_by clause) (can call solo model or list models), help improve functionality

[jowilf]

Please provide more information by filling the feature request template so I can properly address the issue. Thank you

[MatsiukMykola]

i later create demo, anyway bit th for beautifull application