Is it possible to use custom queries?

[villqrd]

Hello,

first of all thank you for the great job. Very intuitive package !

I would like to go a bit further with possibly using more complex queries.

For instance I have a User table and a Bookings table.

Say I would like to do a query with group by and display the results a view, is it currently possible?

E.g. the query would look like something like this with Query API

db.query(Booking)
        .outerjoin(User, User.id == Booking.owner_id))
        .group_by(User.id)
        .with_entities(
            User.firstname,
            func.max(Booking.date_created),
            func.count(Booking.id)
        )

I hope my question makes some sense and again thanks for the amazing work!

[aminalaee]

Hi, thank you for the kind words.

I think what you are looking for is custom views: https://aminalaee.dev/sqladmin/configurations/#custom-views
In that way you can add new views and display anything you want, of course with the manual work.
You need to do the query inside the view, and pass the results within context to the templates to render them.

Does that make sense?

[villqrd]

Thanks for pointing out custom views. The thing is that it would be too much work as it would lose all of the existing exporting logic etc.

I found a good way using only plain sqlalchemy actually, by creating mapper over a select, which allows to use all of the cool features from sqladmin out of the box.

The above example would look like something like this

user_customer_select = (
    select(
        User,
        func.count(Booking.id).label("n_bookings"),
        func.max(Booking.date_created).label("last_booking"),
    )
    .join_from(Booking, User, Booking.owner_id == User.id, True)
    .group_by(Booking.owner_id)
    .subquery()
)

class AUser(Base):
    __tablename__ = "admin_user"
    __table__ = user_customer_select

class UserAdmin(ModelView, model=AUser):

    column_list = [
        AUser.firstname,
        AUser.n_bookings,
        AUser.last_booking,
    ]

[aminalaee]

Huh, nice yes that's very good if you want to keep the same views.