Closed mithun2003 closed 2 weeks ago
I think we can achieve that by adding `
elif op == "startswith:
filters.appen(column.startswith(value))
`
This is going to be coverred by https://github.com/igorbenav/fastcrud/issues/79
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)
"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?"
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.
Closed by #85
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