Can do filter with startswith filter?

[mithun2003]

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

[mithun2003]

I think we can achieve that by adding
`

           elif op == "startswith:
                  filters.appen(column.startswith(value))

`

[JakNowy]

This is going to be coverred by #79

[mithun2003]

for get the total count in get_multi_joined instead of using this
total_count: int = await self.count(db=db, joins_config=join_config, **kwargs) use
total_count: int = await self.count(db=db, joins_config=join_definitions, **kwargs)

[mithun2003]

"I have two tables, 'user' and 'request'. I want to retrieve the 'name', 'email', and 'score' from the 'user' table, and 'id', 'user_id', and 'created_at' from the 'request' table where 'user.user_id' equals 'request.user_id', 'user.platform_id' equals 'request.platform_id', and 'user.platform_id' equals 2. The main requirement is that if there are multiple users with the same 'user_id', only the data from the user who was created last should be shown. How can I structure the query to achieve this?"

[igorbenav]

I didn't test and don't know if this will work, but maybe something like

from sqlalchemy import func, and_
from sqlalchemy.orm import aliased
from sqlalchemy.sql.expression import select, text
from fastcrud import FastCRUD, JoinConfig

# Define a subquery for the latest users by user_id
latest_user_subquery = (
    select([
        User.user_id,
        func.row_number().over(
            partition_by=User.user_id, 
            order_by=User.created_at.desc()
        ).label('rn')
    ]).where(User.platform_id == 2).alias('latest_users')
)

user_crud = FastCRUD(User)

# Join the latest user details with the request table, filtering to get only the latest users
join_config = JoinConfig(
    model=Request,
    join_on=and_(
        User.user_id == Request.user_id, 
        latest_user_subquery.c.user_id == User.user_id, 
        latest_user_subquery.c.rn == 1
    ),
    join_type="inner",
    schema_to_select=RequestReadSchema
)

# Execute the joined query
result = await user_crud.get_multi_joined(
    db=db_session,
    schema_to_select=UserReadSchema,
    joins_config=[join_config],
    platform_id=2
)

But at this point you might just use raw sqlalchemy:

from sqlalchemy import select, func
from sqlalchemy.sql import table, column
from sqlalchemy.orm import aliased


# Subquery to get the latest user entries by user_id for platform_id 2
user_subq = (
    select(
        User.user_id,
        User.name,
        User.email,
        User.score,
        func.row_number().over(
            partition_by=User.user_id,
            order_by=User.created_at.desc()
        ).label('rn')
    )
    .where(User.platform_id == 2)
    .alias('latest_users')
)

# Main query that joins the subquery with the request table
result_query = (
    select(
        user_subq.c.name,
        user_subq.c.email,
        user_subq.c.score,
        Request.id.label('request_id'),
        Request.user_id.label('request_user_id'),
        Request.created_at.label('request_created_at')
    )
    .join(
        Request,
        (user_subq.c.user_id == Request.user_id) &
        (user_subq.c.rn == 1) &
        (Request.platform_id == 2)
    )
)

result = session.execute(result_query).fetchall()

Again, not tested.