igorbenav / fastcrud Goto Github PK

Is your feature request related to a problem? Please describe.
When writing multiple records, if some fail, there is no option to roll back the changes.

Describe the solution you'd like
It should be possible to omit db.commit() within write methods, allowing users to call session commit or rollback externally. This would enable multiple records to be written within the same transaction.

created_record = await crud_xyz.create(db=db, object=record, skip_commit=True)

Thank you for the recent update! We appreciate the enhancements and improvements made. It's not critical, but I think it's worth discussing.

Describe the bug or question
The problem lies in encountering an error due to duplicate table names when writing SQL queries using SQLAlchemy. This occurs when there's ambiguity in the query because the same table name is used multiple times without explicitly specifying aliases. As a result, SQLAlchemy fails to process the query correctly, leading to a query execution error.

To Reproduce

booking_join_config = [
    JoinConfig(
        model=models.UserModel,
        join_on=models.Booking.owner_id == models.UserModel.id,
        join_prefix="owner_",
        schema_to_select=schemas.UserBase,
        join_type="inner",
    ),
    JoinConfig(
        model=models.UserModel,
        join_on=models.Booking.user_id == models.UserModel.id,
        join_prefix="user_",
        schema_to_select=schemas.UserBase,
        join_type="inner",
    ),
]

Description
Output: table name "users" specified more than once

FROM bookings 
JOIN users ON bookings.owner_id = users.id 
JOIN users ON bookings.user_id = users.id 

Additional context

FROM bookings 
JOIN users AS owner_users ON bookings.owner_id = owner_users.id 
JOIN users AS user_users ON bookings.user_id = user_users.id 

First mentioned in #101 by @Justinianus2001:

"
I aim to retrieve a project and its nested objects based on client_id, department_id, and assignee_id as follows:

db_project = await crud_projects.get_joined(
    db=db, id=id, is_deleted=False, nest_joins=True, schema_to_select=ProjectRead,
    joins_config=[
        JoinConfig(
            model=Client,
            join_on=Project.client_id == Client.id,
            join_prefix="client",
            schema_to_select=ClientRead,
            join_type="left",
        ),
        JoinConfig(
            model=Department,
            join_on=Project.department_id == Department.id,
            join_prefix="department",
            schema_to_select=DepartmentRead,
            join_type="left",
        ),
        JoinConfig(
            model=User,
            join_on=Project.assignee_id == User.id,
            join_prefix="assignee",
            schema_to_select=UserReadSub,
            join_type="left",
        ),
    ],
)

However, I encounter an issue: when a foreign key is null, the object is still returned but with all fields set to None.

{
    'id': 1,
    'project_code': 'ABC1',
    'status': 'Approved',
    'name': 'Project Example',
    'start_date': datetime.datetime(2024, 6, 5, 9, 26, 48, 914000, tzinfo=datetime.timezone.utc),
    'end_date': datetime.datetime(2024, 6, 5, 9, 26, 48, 914000, tzinfo=datetime.timezone.utc),
    'client': {
        'name': None,
        'contact': None,
        'phone': None,
        'email': None,
        'id': None
    },
    'department': {
        'id': 1,
        'name': 'Department Name'
    },
    'assignee': {
        'id': None,
        'name': None,
        'username': None,
        'email': None,
        'phone': None,
        'profile_image_url': None,
        'department_id': None,
        'company_id': None
    }
}

How can I modify the query so that if a nested object's foreign key is null, the entire nested object is returned as None (e.g., 'client': None)?
"

Is your feature request related to a problem? Please describe.
I would like to be able to filter get_multi endpoint.

Describe the solution you'd like
I want to be filtering and searching in the get_multi endpoint. This is such a common feature in most CRUD system that I consider relevant. This is also available in flask-muck.

Describe alternatives you've considered
Some out-of-the-box solution are out there:
https://github.com/arthurio/fastapi-filter (sqlalchemy + mongo)
https://github.com/OleksandrZhydyk/FastAPI-SQLAlchemy-Filters (specific sqlalchemy)

Additional context
Add any other context or screenshots about the feature request here.
Ifnot being implemented could an example be provided?

The issue arises when using a column with the type Geometry("POINT") from geoalchemy2, as attempting to utilize FastCRUD results in an error: 'utf-8' codec can't decode byte 0xe6 in position 5: invalid continuation byte. It would be beneficial to add support for geoalchemy2. If that's not feasible, is there an alternative approach for handling such a column type? Or is it necessary to write custom queries and routes? As always thank for you answer!

Describe the bug or question
Consider the following models:

class Ability(Base, UUIDMixin, TimestampMixin, SoftDeleteMixin):
    __tablename__ = "abilities"

    name: Mapped[str] = mapped_column(nullable=False)
    strength: Mapped[int] = mapped_column(nullable=False)
    heroes: Mapped[list["Hero"]] = relationship(back_populates="ability")

class Hero(Base, UUIDMixin, TimestampMixin, SoftDeleteMixin):
    __tablename__ = "heroes"

    name: Mapped[str] = mapped_column(nullable=False)
    ability_id: Mapped[int] = mapped_column(ForeignKey("abilities.id"))
    ability: Mapped["Ability"] = relationship(back_populates="heroes")

When running a nested join query like this:

heroes = await crud_hero.get_multi_joined(db, join_model=Ability, join_prefix="ability_", nest_joins=True)

The returned values are:

{
    "data": [
        {
            "name": "Diana",
            "ability": {
                "id": UUID("6e52176e-8a92-4a8d-b0b3-1fcd55acc666"),
                "name": "Superstrength",
                "strength": 10,
                "id_1": UUID("6e52176e-8a92-4a8d-b0b3-1fcd55acc666"),
            },
            "id": UUID("8212bccb-ce20-489a-a675-45772ad60eb8"),
        },
    ],
    "total_count": 2,
}

Note how the nested set contains the id twice, once as id and once as id_1 and note that the original field of Hero called ability_id is missing, which is failing when returning the data as the Pydantic model will complain about a missing field.

When setting the join_prefix to something stupid, the attribute is in place correctly but obviously that also changes the name of the nested dict:

heroes = await crud_hero.get_multi_joined(db, join_model=Ability, join_prefix="xyz_", nest_joins=True)

{
    "data": [
        {
            "name": "Diana",
            "ability_id": UUID("6e52176e-8a92-4a8d-b0b3-1fcd55acc666"),
            "id": UUID("8212bccb-ce20-489a-a675-45772ad60eb8"),
            "xyz": {
                "name": "Superstrength",
                "strength": 10,
                "id": UUID("6e52176e-8a92-4a8d-b0b3-1fcd55acc666"),
            },
        },
    ],
    "total_count": 2,
}

Describe the bug or question
Handling something like this scenario:

router = APIRouter(prefix="/user/{user_id}")


@router.get("/posts/{post_id}")
def get_post(user_id: int, post_id: int):
    ...
    return {"user": user_id, "post:": post_id, "text": post_text}

curl localhost:8000/user/1/posts/2

{
  "user": 1,
  "post:": 2
  "text": "sample text"
}

Is your feature request related to a problem? Please describe.
In some cases it is reduntant to calculate a total_count of objects in database. Especially if the database is huge and there are multiple filters in the query, it would make sense to ommit it.

Describe the solution you'd like
Add a flag to get_multi and get_multi_joned methods (possibly something else?) to optionally disable count call to database. The flag should be True by default, to maintain backwards compatibility. I am willing to contribute to that in PR, if getting approved.

Is your feature request related to a problem? Please describe.

I understand that the ability to do pagination with get_multi etc. is considered a feature, but I'd like to be able to get all rows without dealing with that, and there's no simple way to do it.

Describe the solution you'd like

I'd like to be able to specify that I don't want to set a limit on the number of rows to fetch. (Actually, ideally unlimited/unpaginated should've probably been the default, IMO, but I understand that doing that now would be a breaking change since the current default is 100, so I'll settle for it being on by default and being able to turn it off.)

Describe alternatives you've considered

The first thing I tried was setting limit to 0, since I'd noticed that the code checks for limit < 0 as an error but not limit == 0. Instead, what I got was no rows whatsoever, which I suppose is technically what one should maybe expect from saying you want at most zero rows, but also feels useless enough to not be intentional.

I've been able to work around the situation by using count to get the total number of rows and setting limit to the result, but that feels like a gruesome hack that I'd rather avoid.

Additional context

I'm willing to work on a PR for this, and am filing this issue at least in part to see a. whether this would even be welcomed, and b. whether you'd prefer:

1. Turning off pagination in the existing methods by setting limit=0
2. Turning off pagination in the existing methods by setting limit=None
3. Splitting off and having a separate set of methods for unpaginated multi-row gets (regular and joined).

(I suppose an additional benefit of option 3 would be that the methods would just return the rows instead of wrapping them in a dict that the ["data"] needs to get dug out of.)

In order for EndpointCreator to work, "id" must be the primary_key column name. It should be in the docs

Describe the bug or question
When you try running code from examples in documentation, all created endpoints have mandatory query parameter local_kw.

To Reproduce
Just create and run app, using the examples from documentation.

Description
The problem is that in the examples async_session object is used as a session dependency. The __call__ method of this object has **local_kw: Any as a parameter. And FastAPI treats it as mandatory query parameter for that endpoint.

Screenshots

Additional context

FastCrud behaves unexpectedly with relationship (sqlalchemy). For a start I have noticed that Foreignkey are defaulted to None if there's a relationship through the FK.

ERROR: {'type': 'get_attribute_error', 'loc': ('response', 'user'), 'msg': "Error extracting attribute: MissingGreenlet: greenlet_spawn has not been called; can't call await_only() here. Was IO attempted in an unexpected place? (Background on this error at: https://sqlalche.me/e/20/xd2s)", 'input': Entity(id=UUID('18baaf7d-b11c-4ea4-994c-xxxxxxx')), 'ctx': {'error': "MissingGreenlet: greenlet_spawn has not been called; can't call await_only() here. Was IO attempted in an unexpected place? (Background on this error at: https://sqlalche.me/e/20/xd2s)"}}

To Reproduce

Please provide a self-contained, minimal, and reproducible example of your use case

class Base(DeclarativeBase, MappedAsDataclass):
    pass


class Entity(Base):
   id: Mapped[uuid.Uuid] = mapped_column(primary_key=True)
   user_id: Mapped[uuid.Uuid] = mapped_column(ForeignKey("users.id"))
   user: Mapped["User"] = relationship("User", back_populates="xer")

#It works when I uncomment user from entityRead 



#shema.py

class entityRead(BaseModel):
   id: uuid.UUID
   user: Optional[list[UserRead]] = None



#route.py

@router.post("/entity", status_code=201)
async def write_entity(request Request, entity: EntityCreate):
   ....
   entity_in = EntityCreateInternal(**processed_dict)
   created_entity: entityRead = await crud_entities.create(db=db, object=entity_in)  
   print(created_entity) #FAILS
   return created_entity
	 

#If I use create without `entityRead` then this works too.

   created_entity = await crud_entities.create(db=db, object=entity_in) 
   print(created_entity) #SUCCEEDS
   return created_entity
 

Expected behaviour would be to create the entity without the issue - the error is invoked but I can see the data in the DB.

I understand that Sqlalchemy is complaining due to Eager Loading but how does this work as expected with FastCrud?

I am looking to change Base to:

class Base(DeclarativeBase, AsyncAttrs):
    pass

Edited: I added the versions for SQLAlchemy and FastCrud.
SQLAlchemy 2.0.23 and FastCrud 0.13.0

Is there any specific reason why FastCRUD depends on FastAPI <0.11.0 and is it easy to fix? The FastAPI changelog doesn't outline any breaking changes except for dependencies with "yield" and "except".

Thanks!

There are a few sql things that are relevant and are currently missing from FastCRUD, I'll list some of them and you guys may add what I forget

• Add possibility of distinct clause
• Add group by
• like, ilike, between operators
• is_ and isnot_ operators

Describe the bug or question
In the docs, we have something like

router = crud_router(
    ...
    read_deps=[get_current_user],
    update_deps=[get_current_user],
    ...
)

When actually the correct way would be:

from fastapi import Depends

router = crud_router(
    ...
    read_deps=[Depends(get_current_user)],
    update_deps=[Depends(get_current_user)],
    ...
)

I'd like some opinion if possible on whether we should fix the docs or change the way it works. The latter is more explicit, but I think the former might be simpler for the end user.

If the former is used, stuff like getting non-callable objects or dependencies that are already wrapped must be handled.

Is your feature request related to a problem? Please describe.
First of all, thanks for your work and sorry for opening a million issues. This one is more of a debate I assume, as there is no "right" way of doing it. I do have the feeling that the current standard set of endpoints I suboptimal. Basically using the example:

router = crud_router(
    session=get_db,
    model=Ability,
    create_schema=AbilityCreateSchema,
    update_schema=AbilityUpdateSchema,
    path="/abilities",
    tags=["abilities"],
)

Yields these endpoints:

One example is: DELETE /api/v1/abilities/delete/{id} - we already know its deleting because of the HTTP verb - why does it need to be in the path again? The same applies to create (POST), update (PATCH) and get (GET) as well.

Describe the solution you'd like
I'd love to be able to configure something "cleaner" (although I'm fully aware that's very opinionated) like this:

The HTTP Verb here together with the endpoint is quite descriptive in itself (and obviously I should change the default endpoint description, but its just for the demo).

Its not possible to achieve this with customised endpoint names either, as it would yield a double slash even if setting the endpoint name for delete to an empty string.

sqlalchemy.exc.DBAPIError: (sqlalchemy.dialects.postgresql.asyncpg.Error) <class 'asyncpg.exceptions.DataError'>: invalid input for query argument $3: datetime.datetime(2024, 5, 22, 8, 11, 40... (can't subtract offset-naive and offset-aware datetimes)
[SQL: UPDATE platform SET is_active=$1::BOOLEAN, is_live=$2::BOOLEAN, updated_at=$3::TIMESTAMP WITHOUT TIME ZONE WHERE platform.id = $4::INTEGER]
[parameters: (True, True, datetime.datetime(2024, 5, 22, 8, 11, 40, 307132, tzinfo=datetime.timezone.utc), 2)]

showing this error when i try to update my table.
The issue i think is in my tables the field updated_at didn't require timezone but the update code in fastcrud adding timezone in it so how to avoid that?

I try to create api on sqlmodel with relationships (In my case, I have table Card and Article with link to Card (key: card_id))
one-to-many

And now I try to understand how it works (joined isn't my solving, I need record which contain Card model with list of Article models inside)

code of getting record

return await card_crud.get(
                db=db,
                id=card_id,
                schema_to_select=Card_schema,
            )

result of this response

Why "true" ....?
How did it appeared ....?

how can I implement this?

First of all: I love FastCrud

Is your feature request related to a problem? Please describe.
Using the automatic crud_router functionality I'm missing the option to filter (all) routes on a specific column.
For example the automatic crud router works great when using a Todo model with

id: int
name: str
description: str
completed: bool

However, when I add a user_id to the todo model I cannot find any (advanced) option to filter the automatically generated routes on a value (like the user_id of the currently logged-in user). Currently, I'm manually creating e.g. a fastcrud.get route that manually adds user_id kwarg.

Describe the solution you'd like
I'm not sure what the best option would be but maybe the following crud_router arguments: read_filter, create_filter, update_filter, delete_filter can be added.

Example:

todo_router = crud_router(
       ...
       read_filter={ 'user_id': lambda: request.state.user_id }
       ...
)

Would do something like this under the hood:

todo_crud.get(db, schema_to_select=TodoRead, return_as_model=False, id=id, **read_filter)

Describe alternatives you've considered
Currently manually creating each router with a filter variables. So while this is still very doable it still feels like a lot of repetitive code of you have to add this filter for all CRUD routes. For example:

@todos_fastcrud.get("/{id}", response_model=TodoRead)
async def get_todo_fastcrud(id: int, request: Request, db: AsyncSession = Depends(get_session)):
    user_id = request.state.user_id
    return await todo_crud.get(db, schema_to_select=TodoRead, return_as_model=False, id=id, user_id=user_id)

Additional context
Maybe I'm missing something and this is already implemented?
I see a request for filters in the multi get, but this request is still different I think:
#15

Is your feature request related to a problem? Please describe.
I'm wonderring if it's currently possible to have multiple layers of nesting in joined objects.

Describe the solution you'd like
Ability to return multi-nested objects like

{
    'id': 1,
    'name': 'Jakub',
    'item': {
        'id': 1,
        'price': 20,
        'shop': {
            'id': 1,
            'name': 'Adidas'
        }
    }
}

Describe the bug or question
Attributes from a joined model are not nested if no join_prefix is set

To Reproduce

Consider two simple models, Hero and Ability

class Ability(Base, UUIDMixin, TimestampMixin, SoftDeleteMixin):
    __tablename__ = "abilities"

    name: Mapped[str] = mapped_column(nullable=False)
    strength: Mapped[int] = mapped_column(nullable=False)
    heroes: Mapped[list["Hero"]] = relationship(back_populates="ability")

class Hero(Base, UUIDMixin, TimestampMixin, SoftDeleteMixin):
    __tablename__ = "heroes"

    name: Mapped[str] = mapped_column(nullable=False)
    ability_id: Mapped[int] = mapped_column(ForeignKey("abilities.id"))
    ability: Mapped["Ability"] = relationship(back_populates="heroes")

Then, this code does not work as expected:

heroes = await crud_hero.get_multi_joined(db, join_model=Ability, nest_joins=True)

Returns this (unrelated columns are removed):

{
    "data": [
        {
            "name": "Diana",
            "ability_id": UUID("6e52176e-8a92-4a8d-b0b3-1fcd55acc666"),
            "id": UUID("8212bccb-ce20-489a-a675-45772ad60eb8"),
            "name_1": "Superstrength",
            "strength": 10,
            "id_1": UUID("6e52176e-8a92-4a8d-b0b3-1fcd55acc666"),
        },
    ],
    "total_count": 2,
}

When adding a prefix, it (kinda) works:

heroes = await crud_hero.get_multi_joined(db, join_model=Ability, join_prefix="ability_", nest_joins=True)

Result is looking better

{
    "data": [
        {
            "name": "Diana",
            "ability": {
                "id": UUID("6e52176e-8a92-4a8d-b0b3-1fcd55acc666"),
                "name": "Superstrength",
                "strength": 10,
                "id_1": UUID("6e52176e-8a92-4a8d-b0b3-1fcd55acc666"),
            },
            "id": UUID("8212bccb-ce20-489a-a675-45772ad60eb8"),
        },
    ],
    "total_count": 2,
}

Describe the bug or question
As far as I can tell, the join documentation is out of date

For example, it shows:

user_tier = await user_crud.get_joined(
    db=db,
    model=Tier,
    join_on=User.tier_id == Tier.id,
    join_type="left",
    join_prefix="tier_",,
    id=1
)

While the code suggests it should be join_model instead of model:

Error initializing database
Here is a piece of code that relates to the error

async def init_db(engine = engine):
    async with async_session() as session:
        Base.metadata.create_all(engine)

AttributeError: 'AsyncEngine' object has no attribute '_run_ddl_visitor'

How can I resolve this error?

Okay, well, I went through all of the docs/, fastcrud/, and tests/ directories, and compiled all of the tables and schemas that were referenced (sometimes in multiple places, but I just noted where I first saw them) and/or defined. Sometimes there are not-quite-duplicates, like UserCreateSchema and CreateUserSchema, which I noted down separately but should probably be unified somehow.

Item

• Item - table - defined in docs/index.md 🎉
• ItemSchema - schema - defined in docs/index.md 🎉
• ItemCreateSchema - schema - defined in docs/index.md 🎉
• ItemUpdateSchema - schema - defined in docs/index.md 🎉

Order

• Order - table - ref'd in fastcrud/crud/fast_crud.py - Undefined ❗
• OrderModel - table - ref'd in fastcrud/endpoint/crud_router.py - Undefined ❗
• OrderSchema - schema - ref'd in docs/usage/crud.md - Undefined ❗
• OrderCreateSchema - schema - ref'd in fastcrud/crud/fast_crud.py - Undefined ❗
• OrderReadSchema - schema - ref'd in fastcrud/crud/fast_crud.py - Undefined ❗
• CreateOrderSchema - schema - ref'd in fastcrud/endpoint/crud_router.py - Undefined ❗
• UpdateOrderSchema - schema - ref'd in fastcrud/endpoint/crud_router.py - Undefined ❗

Customer

• Customer - table - ref'd in docs/usage/crud.md - Undefined ❗
• CustomerSchema - schema - ref'd in docs/usage/crud.md - Undefined ❗

Project

• Project - table - defined in tests/*/conftest.py 🎉
• ProjectSchema - schema - ref'd in docs/advanced/crud.md - Undefined ❗

Participant

• Participant - table - defined in tests/*/conftest.py 🎉

User

• User - table - incompletely defined in docs/advanced/joins.md ⁉️
• UserModel - table - ref'd in docs/advanced/crud.md - Undefined ❗
• UserSchema - schema - ref'd in docs/advanced/crud.md - Undefined ❗
• UserCreateSchema - schema - ref'd in fastcrud/crud/fast_crud.py - Undefined ❗
• UserUpdateSchema - schema - ref'd in fastcrud/crud/fast_crud.py - Undefined ❗
• UserReadSchema - schema - ref'd in fastcrud/crud/fast_crud.py - Undefined ❗
• CreateUserSchema - schema - ref'd in fastcrud/endpoint/crud_router.py - Undefined ❗
• UpdateUserSchema - schema - ref'd in fastcrud/endpoint/crud_router.py - Undefined ❗

Tier

• Tier - table - defined in docs/advanced/joins.md ⁉️
• TierModel - table - defined in tests/*/conftest.py 🎉
• TierSchema - schema - ref'd in docs/advanced/crud.md - Undefined ❗

Tier and TierModel are nearly identical, except that TierModel has a field relating it to the ModelTest table; are they supposed to be the same? There's also a TierSchemaTest in the conftest.py files; should TierSchema be renamed to this?

Department

• Department - table - ref'd in docs/advanced/crud.md - Undefined ❗
• DepartmentSchema - schema - ref'd in docs/advanced/crud.md - Undefined ❗

Task

• Task - table - ref'd in docs/advanced/crud.md - Undefined ❗
• TaskModel - table - ref'd in docs/advanced/crud.md - Undefined ❗
• TaskSchema - schema - ref'd in docs/advanced/crud.md - Undefined ❗
• TaskCreateSchema - schema - ref'd in fastcrud/crud/fast_crud.py - Undefined ❗
• CreateTaskSchema - schema - ref'd in fastcrud/endpoint/crud_router.py - Undefined ❗
• UpdateTaskSchema - schema - ref'd in fastcrud/endpoint/crud_router.py - Undefined ❗

MyModel

• MyModel - table - defined in docs/advanced/endpoint.md 🎉
• CreateMyModel - schema - ref'd in docs/advanced/endpoint.md - Undefined ❗
• UpdateMyModel - schema - ref'd in docs/advanced/endpoint.md - Undefined ❗
• CreateMyModelSchema - schema - ref'd in docs/advanced/endpoint.md - Undefined ❗
• UpdateMyModelSchema - schema - ref'd in docs/advanced/endpoint.md - Undefined ❗
• DeleteMyModelSchema - schema - ref'd in docs/advanced/endpoint.md - Undefined ❗

Role

• Role - table - ref'd in docs/advanced/joins.md - Undefined ❗

Product

• Product - table - ref'd in fastcrud/crud/fast_crud.py - Undefined ❗
• ProductModel - table - ref'd in fastcrud/endpoint/crud_router.py - Undefined ❗
• ProductCreateSchema - schema - ref'd in fastcrud/crud/fast_crud.py - Undefined ❗
• ProductReadSchema - schema - ref'd in fastcrud/crud/fast_crud.py - Undefined ❗
• CreateProductSchema - schema - ref'd in fastcrud/endpoint/crud_router.py - Undefined ❗
• UpdateProductSchema - schema - ref'd in fastcrud/endpoint/crud_router.py - Undefined ❗
• DeleteProductSchema - schema - ref'd in fastcrud/endpoint/crud_router.py - Undefined ❗

Comment

• Comment - table - ref'd in fastcrud/crud/fast_crud.py - Undefined ❗
• CommentCreateSchema - schema - ref'd in fastcrud/crud/fast_crud.py - Undefined ❗

ModelTest

• ModelTest - table - defined in tests/*/conftest.py 🎉

Booking

• BookingModel - table - defined in tests/*/conftest.py 🎉
• BookingSchema - schema - defined in tests/*/conftest.py 🎉

Customer

• CustomerModel - table - ref'd in fastcrud/endpoint/crud_router.py - Undefined ❗
• CreateCustomerSchema - schema - ref'd in fastcrud/endpoint/crud_router.py - Undefined ❗
• UpdateCustomerSchema - schema - ref'd in fastcrud/endpoint/crud_router.py - Undefined ❗

OtherModel

• OtherModel - table - ref'd in fastcrud/endpoint/endpoint_creator.py - Undefined ❗
• CreateOtherModel - schema - ref'd in fastcrud/endpoint/endpoint_creator.py - Undefined ❗
• UpdateOtherModel - schema - ref'd in fastcrud/endpoint/endpoint_creator.py - Undefined ❗

Originally posted by @slaarti in #68 (comment)

Thanks for this awesome package.

Is your feature request related to a problem? Please describe.
It should be possible to filter records using the IN and NOT IN operators in the get and get_multi functions.

Describe the solution you'd like

db_asset = await crud_users.get(
        db=db, 
        schema_to_select=User, 
        return_as_model=True, 
        id=id, 
        filter=User.id.not_in(ids),
        is_deleted=False,
)

It would be great to utilize logical operators such as and_ and or_.

Describe alternatives you've considered
Currently, one must rely on SQLAlchemy methods to execute such filtering operations.

smt = select(User).where(User.reference_id == ref_id).filter(User.id.not_in(ids))

First mentioned in
#87 by @PaleNeutron

"""
filter user by user.company.name == 'A' ? Or filter company by user's name, company.users.any(User.name == 'B')
"""

This was mentioned in #90

async def get_card(self, card_id: uuid.UUID):
        async with async_session_maker() as db:
            return await card_crud.get_joined(
                db=db,
                id=card_id,
                nest_joins=True,
                joins_config=[
                    JoinConfig(
                        model=Article,
                        join_on=Article.card_id == Card.id,
                        join_type="left",
                        join_prefix="articles_",
                        schema_to_select=Article_schema,
                    )
                ]
            )

Assuming a card has multiple articles, articles in joined response should be a list of articles.

It would be convenient to make this field redefined to self.is_deleted_column and optional, because in some cases, one deleted_at_column field is enough to understand that the record has been deleted.

As you can see on the swagger definition attached to this description, The crud_router create automatically routes with suffix get, create or get_multi...

I would like to implement a more "clean" and standard way of route suffix creation like

• create => POST /api/todos/
• get -> GET /api/todos/{id}
• get_multi -> GET /api/todos/

etc...

But I don't know if there is a way to specify it easily

Anyway, if it's not the case I think it could be a good feature and I will be happy to help on it

Describe the bug or question
I have 2 models, A and B, in M2M relationship linked via an ABAssociation table. The documentation explains a way to perform left and outer joins using JoinConfig. It lacks details on how to proceed with M2M relationships. Is that possible?

To Reproduce
Please provide a self-contained, minimal, and reproducible example of your use case

class A(SQLModel, table=True):
    id: int | None = Field(primary_key=True, index=True)

class B(SQLModel, table=True):
    id: int | None = Field(primary_key=True, index=True)

class VideoSearchAssociation(SQLModel, table=True):
    a_id: int | None = Field(primary_key=True, foreign_key="a.id")
    b_id: int | None = Field(primary_key=True, foreign_key="b.id")

Description

Screenshots

Additional context

Describe the bug or question

The FastCRUD class expects that the model that's passed to it is a subclass of SQLAlchemy's DeclarativeBase. SQLModel, meanwhile, derives a metaclass from SQLAlchemy's DeclarativeMeta and uses that as its base. It therefore isn't a subclass of DeclarativeBase and passing a class derived from SQLModel to FastCRUD fails type checking:

$ pyright mve.py
.../mve.py
  .../mve.py:12:22 - error: Argument of type "type[Store]" cannot be assigned to parameter "model" of type "type[ModelType@FastCRUD]" in function "__init__"
    Type "Store" is incompatible with type "DeclarativeBase"
      "Store" is incompatible with "DeclarativeBase" (reportArgumentType)
1 error, 0 warnings, 0 informations

To Reproduce

The MVE used to produce the above error message. The documentation says this should work, and it probably does actually run, but again, it fails type checking (using "standard" typeCheckingMode):

from typing import Annotated

from fastcrud import FastCRUD
from sqlmodel import Field, SQLModel


class Store(SQLModel, table=True):
    id: Annotated[str, Field(primary_key=True)]
    name: Annotated[str, Field(unique=True)]


StoreCRUD = FastCRUD(Store)

Is your feature request related to a problem? Please describe.

When performing an update, I am missing the option to retrieve the updated model. I explicitly have to get the updated model from the DB.

Describe the solution you'd like

I would like to leverage the sqlalchemy Update returning clause which allows returning the data after the update has been performed.

Is your feature request related to a problem? Please describe.
I can't find a way to annotate number of related objects using the fastcrud.get_multi(). This is a raw sqlalchemy query I have instead: [note m2m relationship in this specific case]

    searches = await db.execute(
        select(Search, func.count(Video.id))
        .outerjoin(VideoSearchAssociation)
        .outerjoin(Video, VideoSearchAssociation.video_id == Video.id)
        .group_by(Search.id)
    )

Describe the solution you'd like
I'd like to be able to retrieve videos_count from search_crud.get_multi() method and potentialy others. Extending to_select with func.count(Video.id) seems relatively easy, but whole query must be wrapped in a GROUP_BY clause, which seems to be a bit harder to implement in an elegant way. Maybe some CountConfig resembling JoinConfig would be a way to go?

events = await crud_events.get_multi(
db,
offset=compute_offset(page, items_per_page),
return_as_model=True,
limit=items_per_page,
sort_columns="id",
sort_orders="desc",
schema_to_select=EventRead,
**conditions,
) I want to filter data that are starting user_id

Is your feature request related to a problem? Please describe.

A very common usecase is that we want to update both a model and it's relationships (change both user name and tags in one form) in one request especially m2m relationship. Consider models below:

from sqlalchemy import create_engine, Column, Integer, String, Table, ForeignKey
from sqlalchemy.orm import relationship, declarative_base, sessionmaker

Base = declarative_base()


user_tag_association = Table('user_tag_association', Base.metadata,
    Column('user_id', Integer, ForeignKey('users.id')),
    Column('tag_id', Integer, ForeignKey('tags.id'))
)


class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String)

    tags = relationship('Tag', secondary=user_tag_association, back_populates='users')


class Tag(Base):
    __tablename__ = 'tags'
    id = Column(Integer, primary_key=True)
    name = Column(String)

    users = relationship('User', secondary=user_tag_association, back_populates='tags')

Describe the solution you'd like

If we can update user by passing :

{
"id": 3,
"name": "new name" 
"tags": [1,2,3]
}

That would be great

Describe alternatives you've considered

Use native sqlalchemy ORM update feature:

u = User.query.get(1)
posts_ids = [1, 2, 3]
posts = Post.query.filter(Post.id.in_(posts_ids)).all()
u.posts = posts

Additional context
Add any other context or screenshots about the feature request here.

When deleting an object, the following occurs: can't subtract offset-naive and offset-aware datetimes.
Below I wrote the model and parameters that are passed in the SQL query.

Here is model with fields for soft delete

class UserModel(Base):
...
    created_at = Column(DateTime(), default=datetime.now())
    updated_at = Column(
        DateTime(),
        default=datetime.now(),
        onupdate=datetime.now(),
    )
    deleted_at = Column(DateTime())
    is_deleted = Column(Boolean, default=False)

user_crud = FastCRUD(UserModel)
user_router = crud_router(
    session=get_session,
    model=UserModel,
    crud=user_crud,
    create_schema=UserSchema.UserCreate,
    update_schema=UserSchema.UserUpdate,
    path="/users",
    tags=["Users"]
)

parameters:

[SQL: UPDATE users SET updated_at=$1::TIMESTAMP WITHOUT TIME ZONE, deleted_at=$2::TIMESTAMP WITHOUT TIME ZONE, is_deleted=$3::BOOLEAN WHERE parking.id = $4::INTEGER]
[parameters: (datetime.datetime(2024, 2, 22, 2, 54, 42, 666156), datetime.datetime(2024, 2, 22, 2, 55, 1, 607450, tzinfo=datetime.timezone.utc), True, 1)]

I don't quite understand what the problem could be. Could you help me?

Describe the bug or question

According to the documentation, the FastCRUD class takes optional create, update, and delete schemas as arguments, but this doesn't make sense according to the calling signature for FastCRUD.__init__() and indeed it doesn't seem to work in practice.

(As a secondary but related matter, the documentation references a bunch of example model/schema classes that are never fully defined anywhere, including the code and tests, which made figuring out how to articulate this fairly tricky. If what the docs say is supposed to work, it's something that probably needs some actual tests written for to confirm.)

To Reproduce

I had to do a little fudging using the Item model/schema classes to get a set that'd work for the User model referenced in the docs, but it seems to me like this:

from fastcrud import FastCRUD
from pydantic import BaseModel
from sqlalchemy import Boolean, Column, DateTime, Integer, String
from sqlalchemy.orm import DeclarativeBase

class Base(DeclarativeBase):
    pass

class User(Base):
    __tablename__ = "user"
    id = Column(Integer, primary_key=True)
    name = Column(String)
    archived = Column(Boolean, default=False)
    archived_at = Column(DateTime)

class UserCreateSchema(BaseModel):
    name: str

class UserUpdateSchema(BaseModel):
    name: str

should work for this (Example 6 from the page listed above, in turn taken from the FastCRUD class doc string), and yet:

>>> custom_user_crud = FastCRUD(User, UserCreateSchema, UserUpdateSchema, is_deleted_column="archived", deleted_at_column="archived_at")
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
TypeError: FastCRUD.__init__() got multiple values for argument 'is_deleted_column'

For that matter, Example 1 seems like it works, but the case of it "working" is actually a typing violation:

>>> user_crud = FastCRUD(User, UserCreateSchema, UserUpdateSchema)
>>> user_crud.is_deleted_column
<class '__main__.UserCreateSchema'>
>>> user_crud.deleted_at_column
<class '__main__.UserUpdateSchema'>

Does FastCRUD work with relationship in SQLAlchemy?

class Car(Base):
    brand_id = Column(Integer, ForeignKey("car_brands.id"), nullable=False)
    body_type_id = Column(Integer, ForeignKey("car_body_types.id"), nullable=False)
    body_type = relationship("CarBodyType", backref="cars")
    brand = relationship("CarBrand", backref="cars")

Description
If you use get_multi, then a list is displayed without support for models that belong to this model

Additional context
The expectation was that if you add a relationship to the SQLalchemy model, then this relationship will also appear in the list after get_multi

Get Joined methods (get_joined and get_multi_joined) should support multiple models to be joined.

Additional context
First mentioned in #24:

Hello,

car_models = await car_crud.get_multi_joined(
    db,
    join_model=models.CarBrand,
    join_prefix="brand_",
    user_id=credentials["id"],
)

is working well. but what i need to do if i have 2 joined models ?
not only models.CarBrand, but also models.CarBodyType

can you give a short code example?

Thank you!

Hello,

I'm currently utilizing FastCRUD with the FastAPI boilerplate, but I'm unsure how to implement a filter to retrieve a string from a field similar to the SQL 'LIKE' query, as shown below:

count_projects = await crud_projects.count(db=db, project_code=f"{short_code}%")

I need to count the projects whose project_code begins with a specific short_code (for example, "ABC"). Could you assist me in resolving this issue?

Thank you!

Describe the bug or question
Image is white and transparent, doesn't work on pypi properly

To Reproduce
Just access https://pypi.org/project/fastcrud/

@igorbenav I have more a question about get multi_joined. When i joined 2 table many to one. I can't not create response object in object. Example: I only create like that

"data": [
    {
      "id": 0,
      "title": "string",
      "template_id": 0,
      "description": "string",
      "created_at": "2024-04-14T16:28:01.403Z",
      "created_by": "string",
      "template_title": "Example text for encryption",
      "template_description": "Example description for encryption template"
    }
  ]

This is response that i want

"data": [
    {
      "id": 0,
      "title": "string",
      "template_id": 0,
      "description": "string",
      "created_at": "2024-04-14T16:28:01.403Z",
      "created_by": "string",
      "created_at": "2024-04-14T16:28:01.403Z",
      "created_by": "string",
      "template":{
              "title": "Example text for encryption",
              "description": "Example description for encryption template"
       }
    }
  ]

Please help me :(

Describe the bug or question
As far as I can tell, the soft delete columns are not taken into account when returning entries from the database using any of the get methods.

Description
When using the soft delete columns, they are set correctly when using the FastCRUD.delete() method. When retrieving objects however, it is not taken into account whether the object was soft deleted or not. Maybe this is an intentional design choice and wrong expectations on my side, but I was assuming that by default soft deleted objects would not be returned, potentially with an option to return them by using a parameter.