On Nov 16, 2008, at 2:09 AM, indigophone wrote:
>
> 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.
The "old" way people do this kind of thing is by using a mapper() with
non_primary=True set up. Though the best practice IMHO is to use
query(FinancialAdvisor).select_from(your_distance_statement).all(),
which will associate the columns from your custom statement to the
mapped table of FinancialAdvisor. The requirement is that your
statement expresses the primary key columns of FinancialAdvisor in its
columns clause.
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
