Trying to apply a custom filter involving the postgis function ST_DWithin on a table search. Should result in building a sql query like location::geography ST_DWithin( '%', 1000). From the filter types there doesn't appear to have a filter that uses the functions available in the table. I am using list_and_count since that supports filters, but just not the one i need. Any ideas on how to do it custom?
#Custom Postgis Filter
radiant anchor
from diving into the code, it may require editing the repo to support custom filters?
pseudo wigeon
I think you can pass a direct query to statement, not sure if this is the ideal way though
radiant anchor
yeah ideally i could implement a custom filter like
lat = obj.Latitude[i] lon = obj.Longitude[i] geog = ST_GeogFromText(f'POINT({lon} {lat})') # Check if location already exists within 1000 meters in database customGeoFilter = CustomFilter("location", f'ST_DWithin({geog}, 1000)') # Perform the query and filter by location and account id location = unique_locations_service.list_and_count(customGeoFilter, account_id=account_id)
but this type of implementation isn't supported
pseudo wigeon
I meant this repo.list_and_count(statement=select(Author).where(Author.name == 'name'))
if you had an author repo, you can pass the direct sqla query there
radiant anchor
ahh so unique_locations_service.list_and_count(statement=select(UniqueLocation).where(UniqueLocation.location == f'ST_DWithin({geog}, 1000)')) should be similar
pseudo wigeon
yes, that should work, but idk if its ideal (not the query, but litestar way)
radiant anchor
yeah, the litestar way prefers filters, but currently no supported for this method it seems
pseudo wigeon
my suggestion is more of a workaround, you can see what others respond to
I think having a custom filter could be a nice enhancement here
what happens if you try unique_locations_service.list_and_count(location=f'ST_DWithin({geog}, 1000)'))?
does that return anything?
forget it, idk what I was thinking 😐
I need to take a break
radiant anchor
haha i thought too "wait, thats a good idea" for a sec
pseudo wigeon
I meant this `repo.list_and_count(statement=select(Author).where(Author.name == ...
you can do that for normal filters, this could be .list_and_count(name='name')
actually do try that on second thoughts
apologies for making you do this, I just wanna know
radiant anchor
i suspect its just gonna try comparing a string literal but i will give it a shot once i have the other stuff working
mystic ether
what happens if you try `unique_locations_service.list_and_count(location=f'ST_D...
I'd verify that you don't end up doing any implicit casting here that might prevent an index usage
you might need to add the postgis type to the type encoders map for SQLA 2.0
radiant anchor
update to this, the following command ended up working locations, total_count = await unique_locations_service.list_and_count(statement=select(UniqueLocation).where(ST_DWithin(UniqueLocation.location, geog, 1000)), account_id=str(account_id))
gonna try the other now
the other causes parsing error
mystic ether
update to this, the following command ended up working ` location...
I'm going to look at allowing you to pass in the attributes as filters so you don't have to use the statement override here. If we can do that, look for that to come out in 2.1 in a couple of weeks.
so you can do something like this:
locations, total_count = await unique_locations_service.list_and_count(ST_DWithin(UniqueLocation.location, geog, 1000), account_id=str(account_id))
that would be cool
mystic ether
Well, I think we'll have this implemented today or tomorrow. 🙂
It was easier than we thought
radiant anchor
thats so great to hear!
pseudo wigeon
this is in PR https://github.com/litestar-org/litestar/pull/2265 if you want to give it a shot
could help iron out any issues before its merged
pseudo wigeon
thats good to hear, yeah sure, in case it fails I can now tell it worked for you :p
jokes aside, if you want to, then go ahead, would be nice to have a confirmation
radiant anchor
haha fair enough
pseudo wigeon
is mypy happy with whatever it is you are passing?
I had some issue and slapped some type hints hoping it satisfies all cases
radiant anchor
i can check
radiant anchor
all my tests are failing because its trying to deploy the tables with the postgis extension in the test database but i haven't added the extension. I can't seem to find the place to install the extension like you do in migrations
radiant anchor
i can check
also i mentioned in github, but my mypy is fine but i think something is wrong on my side
pseudo wigeon
all my tests are failing because its trying to deploy the tables with the postgi...
are you saying you can't find a place to add the DDL?
or are you having trouble running the DDL after you have it in that file?
radiant anchor
i put it in my migrations file at @mystic ether recommendation. but on testing, it doesn't look here and postgis is not installed when it goes to deploy tables with type geography
pseudo wigeon
you have to run the migration
alembic upgrade head
soon there will be a way to do this with the litestar cli
radiant anchor
okay thanks! still trying to resolve this since i ran into other issues
radiant anchor
so it was a little more complicated than that. This did help pop up some other issues that i had to resolve but the fix was actually using a postrgres docker image with postgis already installed since we were using the standard postgres docker image in testing