I have the following query:
SET @lat = 40.81518;
SET @lon = -73.0455;
SELECT
3963.0 * acos(sin(@lat/57.2958) * sin(z.latitude/57.2958) + cos(@lat/
57.2958) * cos(z.latitude/57.2958) * cos(z.longitude/57.2958 - @lon/
57.2958))
AS distance, fa.advisor_id, c.city_name, s.iso_state_name
FROM financial_advisor fa
LEFT JOIN us_zipcode z ON fa.city_id = z.city_id
INNER JOIN us_city c ON fa.city_id = c.city_id
INNER JOIN us_state s ON c.state_id = s.state_id
GROUP BY fa.advisor_id
HAVING distance <= 20
ORDER BY distance ASC;
I'd like to map this to a object that is already mapped to a table:
mapper(FinancialAdvisor, financial_advisor_table, properties={
'city':relation(USCity, backref=backref('city'))
})
I obviously don't want to get the distance every query. I want to only
get the distance on some queries. I would like an instance of
FinancialAdvisors for results, with the following properties:
FinancialAdvisor.city -> USCity instance
FinancialAdvisor.city.state (or FinancialAdvisor.state) -> USState
instance
FinancialAdvisor.distance -> from above query
And then the standard column properties for FinancialAdvisor from the
mapped table.
I've never done this before and it seems quite complex. Please let me
know the best practice approach.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
