Detail and Edit view queries make joins for all related models even if those rrelationships are not specified as fields

[FFX01]

Checklist

• The bug is reproducible against the latest release or master.
• There are no similar issues or pull requests to fix it yet.

Describe the bug

My Stack:

• FastAPI
• SqlModel
• SqlAdmin
• PostgreSQL 12

If I have a SqlAdmin ModelView for a SqlModel model with many relationships defined, the query used to populate the details view and the edit view does a join on all relations even when the related fields are not included in the ModelView's column_details_list or form_columns.

This is an issue for me because one of my models has a many-to-many relationship with another table that can include 10's of thousands of records in the relationship. The model has other relations as well, but those have fewer records. When attempting to load the details or edit view for this model the request never completes because the join is so large and I need to restart the server.

I suspect this issue may be related to either SqlModel or SqlAlchemy internal implementation details/behavior. i.e. they are joining all relationships by default. However, I did see there was a fix released for SqlAdmin that fixed a similar issue for the list view here: #409

Steps to reproduce the bug

Please note that this minimal example will likely not reproduce the issue because it only occurs due to the high number of related records in my database. Brand is the model I'm trying to load the detail and edit view for. Asset is the related model with 10's of thousands of related records.

# models.py


class BrandAssetLink(SQLModel, table=True):
    __tablename__ = 'asset_asset_brands'

    id: Optional[int] = Field(default=None, primary_key=True)
    asset_id: str = Field(max_length=512)
    brand_id: int


class Brand(SQLModel, table=True):
    __tablename__ = 'brands'

    id: Optional[int] = Field(default=None, primary_key=True)
    name: str = Field(max_length=255)

    assets: List['Asset'] = Relationship(
        back_populates='brands',
        link_model=BrandAssetLink,
        sa_relationship_kwargs=dict(
            primaryjoin='Brand.id==BrandAssetLink.brand_id',
            secondaryjoin='Asset.page_url_id==BrandAssetLink.asset_id'
        )
    )


class Asset(SQLModel, table=True):
    __tablename__ = 'asset_asset'

    page_url_id: str = Field(max_length=512, primary_key=True)
    title: str = Field(max_length=1024)
    url: str = Field(max_length=4096)

    brands: List['Brand'] = Relationship(
        back_populates='assets',
        link_model=BrandAssetLink,
        sa_relationship_kwargs=dict(
            primaryjoin='Asset.page_url_id==BrandAssetLink.asset_id',
            secondaryjoin='Brand.id==BrandAssetLink.brand_id'
        )
    )

# admin.py


class BrandAdmin(ModelView, model=Brand):
    column_list = [
        Brand.id, Brand.name
    ]
    column_searchable_list = [Brand.name]
    column_sortable_list = [
        Brand.id, Brand.name
    ]
    column_default_sort = (Brand.id, False)
    column_details_list = [
        Brand.id,
        Brand.name
    ]
    form_columns = [
        Brand.name
    ]

Expected behavior

I would expect the detail and edit views to only perform joins for the relationships defined in the column_details_list and form_columns attributes.

Actual behavior

Detail and edit views perform joins for ALL related fields even when not specified in column_details_list and form_columns.

Debugging material

No response

Environment

• Mac
• application is running in a docker container: python:3.8-slim
• Connected to external Postgres Db

Additional context

I also tried putting the related fields in the ModelView's form_ajax_refs attribute to see if that would defer the joins, but that didn't seem to change anything.

[aminalaee]

Hey @FFX01
You are correct about a similar fix that was done and this issue.
This is because there's only one shared method get_model_by_pk that is causing this:

sqladmin/sqladmin/models.py

Lines 849 to 853 in 682b8fa

	async def get_model_by_pk(self, value: Any) -> Any:
	pk_value = get_column_python_type(self.pk_column)(value)
	stmt = select(self.model).where(self.pk_column == pk_value)
	for relation in self._relation_attrs:

The reason for that is that this method is called from different places and can't respect the relationships specified, I think a simple solution can be to separate this method for different purposes. Should be an easy fix, feel free to do a PR otherwise I will fix it soon.

[FFX01]

@aminalaee Thanks for fixing this! Sorry I didn't respond right away. Work threw me onto a new project for a while.