Defining a "First Child" relationship?

[vmalloc]

Hi,

I'm trying to write a piece of code that would fetch the first event belonging to an entity, when there might be many events associated:

class Entity(Model):

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

    events = db.relationship('Event', backref=backref('entity'))

class Event(Model):

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

    timestamp = db.Column(db.Float, primary_key=True)

    entity_id = db.Column(db.ForeignKey(Entity.id))

I'm basically looking to implement something along the lines of this query:

SELECT  entity.*,

        event.id

FROM    entity

OUTER JOIN event

ON      event.id =

        (

        SELECT  TOP 1 id

        FROM    event e

        WHERE   e.entity_id = entity.id

        ORDER BY timestamp

        )

But I have no idea how to achieve this with SQLAlchemy's ORM. I tried playing with column_property for a bit, but couldn't wrap my head around it.

If anybody can provide any leads or explanations on how to do this I would be most grateful.

Thanks in advance

[Mike Bayer]

On 11/21/2015 09:42 AM, vmalloc wrote:
> Hi,
>
> I'm trying to write a piece of code that would fetch the first event
> belonging to an entity, when there might be many events associated:
>
> |
> classEntity(Model):
> |
>
> id = db.Column(db.Integer, primary_key=True)
>
> events = db.relationship('Event', backref=backref('entity'))
>
>
> class Event(Model):
>
> id = db.Column(db.Integer, primary_key=True)
> timestamp = db.Column(db.Float, primary_key=True)
>
> entity_id = db.Column(db.ForeignKey(Entity.id))
>
>
> I'm basically looking to implement something along the lines of this query:
> |
> SELECT entity.*,
> event.id
> FROM entity
> OUTER JOIN event
> ON event.id =
> (
> SELECT TOP 1 id
> FROM event e
> WHERE e.entity_id = entity.id
> ORDER BY timestamp
> )

the basic form of this kind of query at the relationship level, that is
relationship to correlated subquery, is illustrated at the recipe in
https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/RelationshipToLatest.

>
> |
>
> But I have no idea how to achieve this with SQLAlchemy's ORM. I tried
> playing with column_property for a bit, but couldn't wrap my head around it.
>
> If anybody can provide any leads or explanations on how to do this I
> would be most grateful.
>
> Thanks in advance
>

> --
> 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 http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[Rotem Yaari]

Wow! no matter how much I think I go over the docs, there still seems to be yet another documented use case perfectly matching my own.

Thanks a lot for the help!

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/3jK-qu-KVXk/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.