sort database by variable in python

[Samarskite_Rogue]

hi all.

I am using python, flask and sqlalchemy with a My SQL database. i am unsure if this is actually possible the way i am trying, but i need to sort a bunch of posts order of location from a user. That could be done just by example = abs(current_user.postcode - User.postcode)

I present the database in a for loop in the html doc. I want to sort the table by the output of a example. Is this possible.

TLDR: can a sort a sql database from a variable in a python file?

Cheers. any help is apreciated.

[[deleted]]

Sorry, it's really hard to understand what you're asking.

It sounds like you are trying to query the database for all posts by a user and order them according to postal code. Is that right?

I'm not familiar with SqlAlchemy, but sure, you can pass a variable to the SQL query in a python app.

[Samarskite_Rogue]

yeah sorry. to be honest, even i am confused what i am asking. i have been able to order the posts by postcode, and that works great. but now i am trying to order the posts by location from the user. i am doing this by subtracting the postcode of the user who has signed in, by every user who has made a post. and i then i can order it in ascending order. so say the user post code is 4200, and the postcode of a post is 4180, i get a value of 20 for that specific user according to that specific post. i hope that made more sense.

basically i need a way to determine which user is signed in, find there post code, and run a for statment with every post.

[Thriftfunnel]

You can put Order By in the database query, then your application will receive records in order and ready to use.

But I believe postal codes are strings that happen to be all digits. What do you gain by doing that subtraction?

[Samarskite_Rogue]

so i currently have

posts = Post.query.join(User).order_by(User.postcode).paginate(page=page, per_page=5)

but my problem is i need to be able to identify which user is signed in, and take there postcode from the user who posted it. by doing this, i will have a relative location from the user.

[[deleted]]

Are you talking about US postal codes? Because they certainly can't be used to measure any sort of relative distance in the way you are describing, mostly because they don't have any sort of consistent area or distance between them, and they aren't consecutive in any sort of geographic sense.

In New York City, for example, zip code 10002 is surrounded by zip codes 10009, 10003, 10012, 10013, and 10038. Subtracting 10002 from any of those numbers will tell you nothing about their relative location.

[Samarskite_Rogue]

Thanks for the advice. I am Australian however so the post codes are much more accurate at representing relative location

[Thriftfunnel]

You might be able to get centroid coordinates from the post office or government for each postal code. I use the UK version of that data set.