Select the hybrid method result on the query

[Leonardo Mata]

Hello, My applications does some ordering using the distance from latitude and longitude haversine distance, i was able to calculate this using @hybrid.method and @.*expression, but i can't output the calculated distance:

class PartnerAddress(db.Model, WithTimestampsModel, SerializeMixin):

    

    # Columns

    id = db.Column(db.Integer(), primary_key=True, nullable=False)

    partner_id = db.Column(

        db.Integer(),

        db.ForeignKey('partner.id'),

        nullable=False

    )

    latitude = db.Column(db.Numeric(precision=9, scale=7), nullable=False)

    longitude = db.Column(db.Numeric(precision=10, scale=7), nullable=False)

   

    @hybrid_method

    def distance(self, lat, lng):

        

        return math.acos(math.cos(math.radians(self.latitude)) * math.cos(math.radians(lat)) *

               math.cos(math.radians(self.longitude) - math.radians(lng)) +

               math.sin(math.radians(self.latitude)) * math.sin(math.radians(lat))) * 6371

    @distance.expression

    def distance(cls, lat, lng):

        return func.acos(func.cos(func.radians(cls.latitude)) * func.cos(func.radians(lat)) *

                         func.cos(func.radians(lng) - func.radians(cls.longitude)) +

                         func.sin(func.radians(cls.latitude)) * func.sin(func.radians(lat))) * 6371

class PartnerV3(db.Model, WithTimestampsModel,

                SoftDeletableModel, SerializeMixin):

    

    name = db.Column(db.String(128), nullable=False)

    

    address = db.relationship(

        'mustafar.partner.v3.models.address.PartnerAddress',

        backref='partner',

        uselist=False

    )

when querying like this:

query = PartnerV3.query.join(PartnerAddress).order_by(PartnerAddress.distance(lat, lng)).paginate(1,10, False)

I can paginate the result, but it returns only the PartnerV3 object, i can't access the distance.

How do I access this distance property? 

  

[mike bayer]

On 03/06/2017 04:16 PM, Leonardo Mata wrote:
> Hello, My applications does some ordering using the distance from
> latitude and longitude haversine distance, i was able to calculate this
> using @hybrid.method and @.*expression, but i can't output the
> calculated distance:
>

> /class PartnerAddress(db.Model, WithTimestampsModel, SerializeMixin):/
> / /
> / # Columns/
> / id = db.Column(db.Integer(), primary_key=True, nullable=False)/
> /
> /
> / partner_id = db.Column(/
> / db.Integer(),/
> / db.ForeignKey('partner.id'),/
> / nullable=False/
> / )/
> /
> /
> / latitude = db.Column(db.Numeric(precision=9, scale=7), nullable=False)/
> / longitude = db.Column(db.Numeric(precision=10, scale=7),
> nullable=False)/
> / /
> /
> /
> / @hybrid_method/
> / def distance(self, lat, lng):/
> / /
> / return math.acos(math.cos(math.radians(self.latitude)) *
> math.cos(math.radians(lat)) */
> / math.cos(math.radians(self.longitude) -
> math.radians(lng)) +/
> / math.sin(math.radians(self.latitude)) *
> math.sin(math.radians(lat))) * 6371/
> /
> /
> / @distance.expression/
> / def distance(cls, lat, lng):/
> / return func.acos(func.cos(func.radians(cls.latitude)) *
> func.cos(func.radians(lat)) */
> / func.cos(func.radians(lng) -
> func.radians(cls.longitude)) +/
> / func.sin(func.radians(cls.latitude)) *
> func.sin(func.radians(lat))) * 6371/
> /
> /
> /
> /
> /class PartnerV3(db.Model, WithTimestampsModel,/
> / SoftDeletableModel, SerializeMixin):/
> / /
> /
> /
> / name = db.Column(db.String(128), nullable=False)/
> / /
> /
> /
> / address = db.relationship(/
> / 'mustafar.partner.v3.models.address.PartnerAddress',/
> / backref='partner',/
> / uselist=False/
> / )/

>
>
> when querying like this:
>
> query =
> PartnerV3.query.join(PartnerAddress).order_by(PartnerAddress.distance(lat,
> lng)).paginate(1,10, False)
>
> I can paginate the result, but it returns only the PartnerV3 object, i
> can't access the distance.
>
> How do I access this distance property?

you have a PartnerV3 there so the PartnerAddress is on the .address
property:

query =
PartnerV3.query.join(PartnerAddress).order_by(PartnerAddress.distance(lat,
lng))


some_partner = query.first()

partner_address = some_partner.address
distance = partner_address.distance(lat, lng)





>
>
>
>
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google
> Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to sqlalchemy+...@googlegroups.com
> <mailto:sqlalchemy+...@googlegroups.com>.
> To post to this group, send email to sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[Leonardo L. P. da Mata]

Hello, thanks for the help.

This makes sense but it looks like that the value is calculated twice, one in the query and the other accessing the property.

My idea is to have the result on the query return itself.

an email to sqlalchemy+unsubscribe@googlegroups.com
<mailto:sqlalchemy+unsubscribe@googlegroups.com>.

To post to this group, send email to sqlal...@googlegroups.com

<mailto:sqlalchemy@googlegroups.com>.

Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

--
SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example.  See  http://stackoverflow.com/help/mcve for a full description.

--- You received this message because you are subscribed to a topic in the Google Groups "sqlalchemy" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/4JKV8vS4jis/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sqlalchemy+unsubscribe@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.

Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

--

Leonardo Luiz Padovani da Mata
bar...@gmail.com

"May the force be with you, always"
"Nerd Pride... eu tenho. Voce tem?"

[mike bayer]

On 03/06/2017 04:47 PM, Leonardo L. P. da Mata wrote:
> Hello, thanks for the help.
>
> This makes sense but it looks like that the value is calculated twice,
> one in the query and the other accessing the property.
>
> My idea is to have the result on the query return itself.
>

"lat" and "lng" here are variables that are in your program and are not
related to the model you have. So you're asking that a particular
object be returned with additional values stuck onto it.

The Session uses an identity map that ensures that an object of a
particular primary key is only present once; if you make two queries
which both return the same primary key, you get the same object back.

If you did two queries with different values of "lat" / "lng" that both
ultimately return the same PartnerAddress object, would the old values
be erased? It's not clear.

Instead, this "distance" you're getting is really a value associated not
just with the object but this specific query. You can get it back by
just adding it as a column:

query = session.query(PartnerV3, PartnerAddress.distance(lat,
lng)).join(PartnerAddress).order_by(PartnerAddress.distance(lat, lng))


for partnerv3, distance in query:
print distance

>
>
> 2017-03-06 18:31 GMT-03:00 mike bayer <mik...@zzzcomputing.com

> <mailto:mik...@zzzcomputing.com>>:

>
>
>
> On 03/06/2017 04:16 PM, Leonardo Mata wrote:
>
> Hello, My applications does some ordering using the distance from
> latitude and longitude haversine distance, i was able to
> calculate this
> using @hybrid.method and @.*expression, but i can't output the
> calculated distance:
>
> /class PartnerAddress(db.Model, WithTimestampsModel,
> SerializeMixin):/
> / /
> / # Columns/
> / id = db.Column(db.Integer(), primary_key=True, nullable=False)/
> /
> /
> / partner_id = db.Column(/
> / db.Integer(),/

> / db.ForeignKey('partner.id <http://partner.id>'),/

> / nullable=False/
> / )/
> /
> /
> / latitude = db.Column(db.Numeric(precision=9, scale=7),
> nullable=False)/
> / longitude = db.Column(db.Numeric(precision=10, scale=7),
> nullable=False)/
> / /
> /
> /
> / @hybrid_method/
> / def distance(self, lat, lng):/
> / /
> / return math.acos(math.cos(math.radians(self.latitude)) *
> math.cos(math.radians(lat)) */
> / math.cos(math.radians(self.longitude) -
> math.radians(lng)) +/
> / math.sin(math.radians(self.la

> <http://self.la>titude)) *

> <http://mustafar.partner.v3.models.ad>dress.PartnerAddress',/

> an email to sqlalchemy+...@googlegroups.com
> <mailto:sqlalchemy%2Bunsu...@googlegroups.com>
> <mailto:sqlalchemy+...@googlegroups.com
> <mailto:sqlalchemy%2Bunsu...@googlegroups.com>>.

> To post to this group, send email to sqlal...@googlegroups.com

> <mailto:sqlal...@googlegroups.com>
> <mailto:sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>>.

> Visit this group at https://groups.google.com/group/sqlalchemy

> <https://groups.google.com/group/sqlalchemy>.

> For more options, visit https://groups.google.com/d/optout

> <https://groups.google.com/d/optout>.

>
>
> --
> SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve

> <http://stackoverflow.com/help/mcve> for a full description.
> --- You received this message because you are subscribed to a topic
> in the Google Groups "sqlalchemy" group.
> To unsubscribe from this topic, visit
> https://groups.google.com/d/topic/sqlalchemy/4JKV8vS4jis/unsubscribe

> <https://groups.google.com/d/topic/sqlalchemy/4JKV8vS4jis/unsubscribe>.

> To unsubscribe from this group and all its topics, send an email to

> sqlalchemy+...@googlegroups.com
> <mailto:sqlalchemy%2Bunsu...@googlegroups.com>.

> To post to this group, send email to sqlal...@googlegroups.com

> <mailto:sqlal...@googlegroups.com>.

> Visit this group at https://groups.google.com/group/sqlalchemy

> <https://groups.google.com/group/sqlalchemy>.

> For more options, visit https://groups.google.com/d/optout

> <https://groups.google.com/d/optout>.

>
>
>
>
> --
> Leonardo Luiz Padovani da Mata

> bar...@gmail.com <mailto:bar...@gmail.com>

>
> "May the force be with you, always"
> "Nerd Pride... eu tenho. Voce tem?"
>

> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google
> Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send

> an email to sqlalchemy+...@googlegroups.com
> <mailto:sqlalchemy+...@googlegroups.com>.

> To post to this group, send email to sqlal...@googlegroups.com

> <mailto:sqlal...@googlegroups.com>.

[Leonardo L. P. da Mata]

Thanks mike, that was a helpful explanation.

2017-03-06 18:59 GMT-03:00 mike bayer <mik...@zzzcomputing.com>:

On 03/06/2017 04:47 PM, Leonardo  L. P. da Mata wrote:

Hello, thanks for the help.

This makes sense but it looks like that the value is calculated twice,
one in the query and the other accessing the property.

My idea is to have the result on the query return itself.

"lat" and "lng" here are variables that are in your program and are not related to the model you have.   So you're asking that a particular object be returned with additional values stuck onto it.

The Session uses an identity map that ensures that an object of a particular primary key is only present once; if you make two queries which both return the same primary key, you get the same object back.

If you did two queries with different values of "lat" / "lng" that both ultimately return the same PartnerAddress object, would the old values be erased?  It's not clear.

Instead, this "distance" you're getting is really a value associated not just with the object but this specific query.   You can get it back by just adding it as a column:

 query = session.query(PartnerV3, PartnerAddress.distance(lat, lng)).join(PartnerAddress).order_by(PartnerAddress.distance(lat, lng))


 for partnerv3, distance in query:
     print distance

2017-03-06 18:31 GMT-03:00 mike bayer <mik...@zzzcomputing.com

<mailto:mike_mp@zzzcomputing.com>>:

        an email to sqlalchemy+unsubscribe@googlegroups.com

        <mailto:sqlalchemy%2Bunsubscrib...@googlegroups.com>
        <mailto:sqlalchemy+unsubscribe@googlegroups.com
        <mailto:sqlalchemy%2Bunsubscrib...@googlegroups.com>>.

        To post to this group, send email to sqlal...@googlegroups.com

        <mailto:sqlalchemy@googlegroups.com>
        <mailto:sqlalchemy@googlegroups.com
        <mailto:sqlalchemy@googlegroups.com>>.

        Visit this group at https://groups.google.com/group/sqlalchemy

        <https://groups.google.com/group/sqlalchemy>.
        For more options, visit https://groups.google.com/d/optout
        <https://groups.google.com/d/optout>.


    --
    SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper

    http://www.sqlalchemy.org/

    To post example code, please provide an MCVE: Minimal, Complete, and
    Verifiable Example.  See  http://stackoverflow.com/help/mcve
    <http://stackoverflow.com/help/mcve> for a full description.
    --- You received this message because you are subscribed to a topic
    in the Google Groups "sqlalchemy" group.
    To unsubscribe from this topic, visit
    https://groups.google.com/d/topic/sqlalchemy/4JKV8vS4jis/unsubscribe
    <https://groups.google.com/d/topic/sqlalchemy/4JKV8vS4jis/unsubscribe>.

    To unsubscribe from this group and all its topics, send an email to

    sqlalchemy+unsubscribe@googlegroups.com
    <mailto:sqlalchemy%2Bunsubscrib...@googlegroups.com>.

    To post to this group, send email to sqlal...@googlegroups.com

    <mailto:sqlalchemy@googlegroups.com>.

    Visit this group at https://groups.google.com/group/sqlalchemy

    <https://groups.google.com/group/sqlalchemy>.
    For more options, visit https://groups.google.com/d/optout
    <https://groups.google.com/d/optout>.




--
Leonardo Luiz Padovani da Mata
bar...@gmail.com <mailto:bar...@gmail.com>

"May the force be with you, always"
"Nerd Pride... eu tenho. Voce tem?"

--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and
Verifiable Example. See http://stackoverflow.com/help/mcve for a full
description.
---
You received this message because you are subscribed to the Google
Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send

an email to sqlalchemy+unsubscribe@googlegroups.com
<mailto:sqlalchemy+unsubscribe@googlegroups.com>.

To post to this group, send email to sqlal...@googlegroups.com

<mailto:sqlalchemy@googlegroups.com>.

Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

--
SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example.  See  http://stackoverflow.com/help/mcve for a full description.

--- You received this message because you are subscribed to a topic in the Google Groups "sqlalchemy" group.

To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/4JKV8vS4jis/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sqlalchemy+unsubscribe@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.

Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

--

Leonardo Luiz Padovani da Mata
bar...@gmail.com