Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Can do filter with startswith filter? #83

Closed
mithun2003 opened this issue May 13, 2024 · 6 comments
Closed

Can do filter with startswith filter? #83

mithun2003 opened this issue May 13, 2024 · 6 comments
Labels
enhancement New feature or request FastCRUD Methods Related to FastCRUD methods

Comments

@mithun2003
Copy link

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 mithun2003 added the enhancement New feature or request label May 13, 2024
@mithun2003
Copy link
Author

mithun2003 commented May 13, 2024

I think we can achieve that by adding
`

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

`

@JakNowy
Copy link
Contributor

JakNowy commented May 13, 2024

This is going to be coverred by #79

@mithun2003
Copy link
Author

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)

@igorbenav igorbenav added the FastCRUD Methods Related to FastCRUD methods label May 16, 2024
@mithun2003
Copy link
Author

"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
Copy link
Owner

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.

@igorbenav
Copy link
Owner

Closed by #85

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
enhancement New feature or request FastCRUD Methods Related to FastCRUD methods
Projects
None yet
Development

No branches or pull requests

3 participants