Query last relation created and attributes

[Scheck David]

Hi,

I have to perform a query who filter in the last relations created an attribute, but I don't know how to do. I checked the doc and I don't get it

class Statustype(Base):

    __tablename__ = 'statustypes'

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

    naam = Column(String(50), nullable=False)

class Status(Base):

    __tablename__ = 'statussen'

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

    statustype_id = Column(Integer, ForeignKey('statustypes.id'), nullable=False)

    datum = Column(DateTime(timezone=True), nullable=False, default=func.now())

    status = relationship('Statustype')

class Object(Base):

    __tablename__ = 'aanduidingsobjecten'

    naam = Column(String(255), nullable=False)

    type_id = Column(Integer, ForeignKey('aanduidingsobjecttypes.id'), nullable=False)

    statussen = relationship(

        "AanduidingsobjectStatus",

        order_by='desc(AanduidingsobjectStatus.status_datum)',

        backref='aanduidingsobject',

        cascade='all, delete, delete-orphan',

        lazy='subquery'

    )

here is the query I got so far : 

session.query(Object)\

                .join(Object.statussen)\

                .filter(Statustype.id > 50).all()

But I don't see how to perform the check on the last status and all in only one query. Do you have an idea?

[Scheck David]

I did this :

1. self.session.query(Object)\
2. .join(Object.statussen)\
3. .filter(Status.id == self.session.query(Status).order_by(desc(Status.datum)).first().id)\
4. .filter(Statustype.id > 50).all()

but I still don"t get it

[Scheck David]

I think I'm near but I can't finish : 

for all the structure : https://dpaste.de/fek5#L

and here my query : 

self.session.query(Object).outerjoin(ObjectStatus).outerjoin(Status)\

            .group_by(Object).with_entities(Object, func.max(Status.datum).label("status_datum")).subquery()

self.session.query(Object).join((sub, sub.c.id == Status.id))\

        .filter(sub.c.statustype_id > 50)

but status type not reachable.

[Mike Bayer]

Hi -

I was hoping someone could jump in on this.

do you know the SQL that you want to emit? E.g. plain SQL string. I
can help you do that part.

> --
> 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.
> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/fe2da543-2710-4b44-9f88-e68c5e35faea%40googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.

[Scheck David]

the problem is that I can't use SQL for this because this is a mixins that I use for several objects (tables) because they all have this status structure... yes it's quite difficult to do :

right now I'm on this stage, but still don't work

self.session.query(Object).outerjoin(ObjectStatus).outerjoin(Status)\

            .group_by(Object).with_entities(Object, func.max(Status.datum).label("status_datum")).subquery()

self.session.query(Object).join((sub, sub.c.id == Status.id))\

        .filter(sub.c.statustype_id > 50)

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/Cju-PF7mV58/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.
Visit this group at https://groups.google.com/group/sqlalchemy.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CA%2BRjkXF3d_pffLqFLYpSNw80E%3D_2ULhh0pTiqBG-F1dFjA9r1Q%40mail.gmail.com.

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

--

With kindest regards,

David SCHECK

PRESIDENT/DEVELOPER

Phone: +32 4 87 86 70 12
Visit our website ! https://www.sphax.org

[James Fennell]

I think Mike's suggestion was to construct the raw SQL string you want, then reverse engineer to get the correct SQL Alchemy code, which you can then use with your different models. For complicated SQL logic I think this is a good practice in general.

You current question seems like a general SQL question rather than something specific to SQL Alchemy. After you've the SQL, we could discuss the reverse engineering.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CAOPG6scR1rWJTBN%3DEArX2buPqiVsGaPiQq4wYGU58Renqae0kA%40mail.gmail.com.

[Mike Bayer]

On Mon, May 13, 2019 at 10:37 AM Scheck David <da...@sphax.it> wrote:

the problem is that I can't use SQL for this because this is a mixins that I use for several objects (tables) because they all have this status structure... yes it's quite difficult to do :

I don't know what it is you want to do so if you could illustrate the SQL that resembles what you want, I'd have some idea.   If you are trying to build a Query object, then you are working with SQL and you'd need to understand the structure you're looking for.

 

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CAOPG6scR1rWJTBN%3DEArX2buPqiVsGaPiQq4wYGU58Renqae0kA%40mail.gmail.com.

[Scheck David]

Ok, I'll try to build this query in sql. thanks :)

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CALDF6i1aehirZNM3ac60nw8N-A8Hbs2JoALx4p_WEesoqOn%2BZg%40mail.gmail.com.

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

[Scheck David]

I'm near the result of sql:

https://dpaste.de/1XYa#

> To unsubscribe from this group and stop receiving emails from it, send an email to sqlal...@googlegroups.com.

> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/fe2da543-2710-4b44-9f88-e68c5e35faea%40googlegroups.com.
> 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/Cju-PF7mV58/unsubscribe.

To unsubscribe from this group and all its topics, send an email to sqlal...@googlegroups.com.

To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CA%2BRjkXF3d_pffLqFLYpSNw80E%3D_2ULhh0pTiqBG-F1dFjA9r1Q%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.

--

With kindest regards,

David SCHECK

PRESIDENT/DEVELOPER

Phone: +32 4 87 86 70 12
Visit our website ! https://www.sphax.org

--
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 sqlal...@googlegroups.com.

[Jonathan Vanasco]

On Tuesday, May 14, 2019 at 10:29:58 AM UTC-4, Scheck David wrote:

I'm near the result of sql:

https://dpaste.de/1XYa#

Do you want this to be an attribute/relationship on the class or to run this as a separate query? 

[Scheck David]

In fact this I will use as a method on a datamanager use for different objects. In this sql the object is erfgoedobjecten but it will change.

--

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/Cju-PF7mV58/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.
Visit this group at https://groups.google.com/group/sqlalchemy.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/36b8df42-960c-4f75-adee-a4a59cc87636%40googlegroups.com.

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

[Scheck David]

For a full state of what I have 

https://dpaste.de/vV8k

the goal is to convert the sql query to SQLAlchemy.

Thanks in advance for any help

[Scheck David]

I finished by a query like that : 

        last_statuses = aliased(statussen_table_name, self.session.query(getattr(statussen_table_name, object_name),

                                                                        statussen_table_name.status_id).join(Status).order_by(Status.datum.desc()).limit(1).subquery().lateral())

        return self.session.query(self.cls).join(last_statuses).filter(Status.statustype_id > 50).all()

the problem is that there is a problem in the query : 

sqlalchemy.exc.InvalidRequestError: Select statement 'SELECT statussen.datum 

FROM (SELECT personen.id AS personen_id 

FROM statussen, personen JOIN LATERAL (SELECT personen_statussen.persoon_id AS persoon_id, personen_statussen.status_id AS status_id 

FROM personen_statussen JOIN statussen ON statussen.id = personen_statussen.status_id ORDER BY statussen.datum DESC

 LIMIT :param_1) AS anon_2 ON personen.id = anon_2.persoon_id 

WHERE statussen.statustype_id > :statustype_id_1) AS anon_1, personen_statussen 

WHERE statussen.id = personen_statussen.status_id' returned no FROM clauses due to auto-correlation; specify correlate(<tables>) to control correlation manually.

[Scheck David]

I really don't know why this query returns me this .. totally mysterious

[Scheck David]

Ok I just identified the issue. It seems that there is a conflict between 2 subqueries :

in my model.I have this : 

    statussen = relationship(

        "PersoonStatus",

        order_by="desc(PersoonStatus.status_datum)",

        backref='persoon',

        cascade='all, delete, delete-orphan',

        lazy='subquery'

    )

and if I do this query : 

        last_statuses = aliased(

                            statussen_table_name,

                            self.session.query(

                                getattr(statussen_table_name, issue_id_field),

                                 statussen_table_name.status_id)\

                            .join(Status).order_by(Status.datum.desc())\

                            .limit(1).subquery().lateral())

there is a conflict between this 2 subqueries who target the same table.

Is there a workaround to prevent this conflict?

any idea?

[Mike Bayer]

I hope you can forgive that I've been allowing you to figure this out
on your own as the emails here seem to be more of glimpses of an
in-progress situation rather than something those of us who are
outside of your project can easily engage within, if you are stuck on
something is there any chance you can provide a very succinct and
minimal Python script that illustrates the specific issue all at once
in a runnable format? I see you are using things like LATERAL which
are very sophisticated Postgresql features.

> --
> 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.

> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.

> To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/61df1e98-8475-4d3a-a21b-20aff412d4fa%40googlegroups.com.