How can I order objects according to some attribute of the child in sqlalchemy?

Here is the situation: I have a parent model say BlogPost. It has many Comments. What I want is the list of BlogPosts ordered by the creation date of its' Comments. I.e. the blog post which has the most newest comment should be on top of the list. Is this possible with SQLAlchemy?


Asked by: Arthur526 | Posted: 06-12-2021






Answer 1

http://www.sqlalchemy.org/docs/05/mappers.html#controlling-ordering

As of version 0.5, the ORM does not generate ordering for any query unless explicitly configured.

The “default” ordering for a collection, which applies to list-based collections, can be configured using the order_by keyword argument on relation():

Answered by: Rubie414 | Posted: 07-01-2022



Answer 2

I had the same question as the parent when using the ORM, and GHZ's link contained the answer on how it's possible. In sqlalchemy, assuming BlogPost.comments is a mapped relation to the Comments table, you can't do:

session.query(BlogPost).order_by(BlogPost.comments.creationDate.desc())

, but you can do:

session.query(BlogPost).join(Comments).order_by(Comments.creationDate.desc())

Answered by: Brad965 | Posted: 07-01-2022



Similar questions

python - Can't set attribute on result objects in SQLAlchemy flask

I encounter a problem with Flask-SQLAlchemy, I can set the attribute of the objects in place_collections, but when I want to set the attribute for objects in places, an error occurred: Traceback (most recent call last): File "/Users/user/PycharmProjects/website/venv/lib/python3.6/site-packages/flask/app.py", line 2309, in __call__ return self.wsgi_app(environ, start_r...


python - Attribute as dict of lists using middleman table with SQLAlchemy

My question is about SQLAlchemy but I'm having troubles explaining it in words so I figured I explain it with a simple example of what I'm trying to achieve: parent = Table('parent', metadata, Column('parent_id', Integer, primary_key=True), Column('name', Unicode), ) parent_child = Table('parent_child', metadata, Column('parent_id', Integer, primary_key=True), Column('child_id', Integer, pri...


python - sqlalchemy get model attribute question

My filters come via get params like so: filterName = 'email', filterValue = 'john@' and I need to build my query based on them. If I try to use rows.filter( getattr( self.model, filterName ).like( filterValue + '%' ) ) I get an error: AttributeError: 'NoneType' object has no attribute 'like' Since both my model and filter params come through GET it's crucial I implement a fix for t...


python - Editing an attribute before it's mapped to an object in SQLAlchemy

I'm storing file paths using sqlalchemy. At the moment these are stored as absolute paths, but I need them to be stored as relative. However I don't want to have to manually join the paths with the base directory every time they're needed, instead this should be done automatically before they are mapped to an object. This way anytime I query the database I'll get back an absolute path, even though they're stored relative. ...


sqlalchemy - get attribute names of class python/ get column names of query

I want to have the column names of my sqlalchemy query but I do not find how I can do this. I tried a few ways first way I have tried was to make a new class, because the names had to be different of the column names class DosierWeergave(): def __init__(self, code_arg, year_registr, period_registr, staynum, status): self.Code_Arg = str(code_arg) self.Year_registr = str(y...


python - Is it possible to declare an optional and unique attribute for a class model in (Flask) SQLAlchemy?

I just need to declare an attribute that is unique and optional. Is it possible in Python Sql Alchemy using Flask and Flask-SQLAlchemy? membership_number = db.Column(db.String(120), unique=True)


python - Creating a set-like mapped attribute in SQLAlchemy

I've done some reading and Googling, but am still not sure if there is a native / built-in way to create a mapped attribute in an SQLAlchemy object, that creates a set-like attribute for a similar scheme: User UserTag +----+-----------------+ +---------+-----------+ | id | email | | user_id | tag | +----+-----------------+ +---------+------...


python - Change object's attribute on session commit - Flask SQLAlchemy

I want to change an object's attribute before it is inserted to the DB using Falsk-SQLAlachemy. I tried using before_models_committed signal, but it seems to be broken, so I'm trying models_commited instead (and re-committing the changes) and I get the following error: InvalidRequestError: This session is in 'committed' state; no further SQL can be emitted within this transaction.


python - SQLAlchemy ORM: proxy attribute pointing to the first element of a relation?

Starting from the many-to-many relationship example from the SQLAlchemy documentation, I want to add an attribute first_child that will return the first child of children defined by the relationship. The first_child attribute needs to be useable in an


python - set table attribute value with other attribute values in sqlalchemy

i have this column in a table: name = Column(String, default='ts-1') how do i set the default value to increment automatically so that the names of objects will be 'ts-1', 'ts-2', 'ts-3'... when created. i tried writing a constructor: def __init__(self): self.name = 'ts-' + str(self.id) but it always returns None. thanks.


python - SqlAlchemy attribute that tracks an assigned attribute

Goal: Create an SQLAlchemy attribute which tracks/follows changes in another object's SQLAlchemy attribute. Given: class ClazzA(): attributeA = Column(JSONDict) class ClazzB(): attributeB = Column(?) objectA = ClazzA() objectA.attributeA = {'foo': 1} objectB = ClazzB() objectB.attributeB = objectA.attributeA objectA.attributeA['foo'] = 2 JSONDict is associate...


python - How can I use UUIDs in SQLAlchemy?

Is there a way to define a column (primary key) as a UUID in SQLAlchemy if using PostgreSQL (Postgres)?


python - Getting random row through SQLAlchemy

How do I select one or more random rows from a table using SQLAlchemy?


python - How to add an automatic filter to a relation with SQLAlchemy?

I'm using SQLAlchemy 0.5rc, and I'd like to add an automatic filter to a relation, so that every time it tries to fetch records for that relation, it ignores the "remote" ones if they're flagged as "logically_deleted" (a boolean field of the child table) For example, if an object "parent" has a "children" relation that has 3 records, but one of them is logically deleted, when I query for "Parent" I'd like SQLA to f...


python - What is the sqlalchemy equivalent column type for 'money' and 'OID' in Postgres?

What is the sqlalchemy equivalent column type for 'money' and 'OID' column types in Postgres?


python - SQLAlchemy and empty columns

When I try to insert a new record into the database using SQLAlchemy and I don't fill out all values, it tries to insert them as "None" (instead of omitting them). It then complains about "can't be null" errors. Is there a way to have it just omit columns from the sql query if I also omitted them when declaring the instance?


python - SQLAlchemy DateTime timezone

SQLAlchemy's DateTime type allows for a timezone=True argument to save a non-naive datetime object to the database, and to return it as such. Is there any way to modify the timezone of the tzinfo that SQLAlchemy passes in so it could be, for instance, UTC? I realize that I could just use default=datetime.datetime.utcnow; however, this is a naive time that would happily ac...


python - How can I get all rows with keys provided in a list using SQLalchemy?

I have sequence of IDs I want to retrieve. It's simple: session.query(Record).filter(Record.id.in_(seq)).all() Is there a better way to do it?


python - SQLAlchemy - INSERT OR REPLACE equivalent

does anybody know what is the equivalent to SQL "INSERT OR REPLACE" clause in SQLAlchemy and its SQL expression language? Many thanks -- honzas


python - Defining a table with sqlalchemy with a mysql unix timestamp

Background, there are several ways to store dates in MySQ. As a string e.g. "09/09/2009". As integer using the function UNIX_TIMESTAMP() this is supposedly the traditional unix time representation (you know seconds since the epoch plus/minus leap seconds). As a MySQL TIMESTAMP, a mysql specific data type not the same than unix timestamps. As a MySQL Date field, another mysql spec...


python - How to generate a file with DDL in the engine's SQL dialect in SQLAlchemy?

Suppose I have an engine pointing at MySQL database: engine = create_engine('mysql://arthurdent:answer42@localhost/dtdb', echo=True) I can populate dtdb with tables, FKs, etc by: metadata.create_all(engine) Is there an easy way to generate the SQL file that contains all the DDL statements instead of actually applying these DDL sta...






Still can't find your answer? Check out these communities...



PySlackers | Full Stack Python | NHS Python | Pythonist Cafe | Hacker Earth | Discord Python



top