should query.with_entities() be considered just for the columns clause and not the FROM clause?

[Hugo Branquinho]

I'm doing some pagination and trying to count the number of results

query = session.query(Group.id)
print query  # SELECT groups.id AS groups_id FROM groups
print query.with_entities(func.count(1))  # SELECT count(:param_1) AS count_1

The count query don't have any FROM, i understand why this happen, but there is no way to preserve query froms?

I can fix this adding all entities

query = session.query(Group.id)
print query  # SELECT groups.id AS groups_id FROM groups
print query.with_entities(func.count(1), *[e.expr for e in query._entities])  # SELECT count(:param_1) AS count_1, iduc_groups.id AS iduc_groups_id FROM iduc_groups

But i don't know if it works for every defined entities

[Mike Bayer]

not really sure I want to do anything here, this is very simple if you use select_from:

print query.with_entities(func.count(1)).select_from(Group)

with_entities() replaces the things you are selecting from. It wouldn't really work if it held onto things, because:

session.query(A, B).with_entities(A)

should select FROM A and B or just A ?

[Hugo Branquinho]

I think the logic behind "with_entities" should be the same. If we have a empty query (no filters, no relations, only selected columns) and try to update the entities, the query should be different. But in my case, i want to keep the query.

In reality if we do "session.query(Group.id)" the printed query is "SELECT groups.id AS groups_id FROM groups" here we already have the FROM table. Maybe adding a key argument to "with_entities" (ex: keep_options), or adding a new method to Query (ex: with_columns) to keep the created FROM, and updating the selected columns.

I have this:

class Pagination(list):
    def __init__(self, query, page=1, limit_per_page=20):
        super(Pagination, self).__init__()

        self.page = int(page)
        if not self.page or self.page < 1:
            self.page = 1

        self.limit_per_page = int(limit_per_page)
        if not self.limit_per_page or self.limit_per_page < 1:
            self.limit_per_page = 20

        if query is None:
            self.number_of_results = 1
            self.last_page = 1
        else:
            self.number_of_results = (
                query
                .with_entities(func.count(1))
                .first()[0])
            self.last_page = int(ceil(
                self.number_of_results / float(self.limit_per_page))) or 1

        if self.page > self.last_page:
            self.page = self.last_page
        if query is not None:
            end_slice = self.page * self.limit_per_page
            start_slice = end_slice - self.limit_per_page
            self.extend(query.slice(start_slice, end_slice).all())

# Somewhere i have this
columns = options.get(attributes)
query = self.query(*columns)
# Query relations...
return Pagination(query, page, limit_per_page)

I can sent an column to use in count and do ".with_entities(func.count(COLUMN))" or use Query.count() But if i can do this without the COLUMN, would be nicer :)

[Mike Bayer]

trying to get with_entities() to make a guess would definitely be a losing battle. a new method is more feasible but then we already have a lot of methods - why can't you just say select_from() ?

[Mike Bayer]

there's also methods that are built on top of "with_entities" like values() and value(), which sort of got in there early on. we have a lot of methods based on this concept working in a certain way. it almost seems like if the given expressions have no entities at all, then we'd keep the entities we have. this is nothing I can change anytime soon, 1.0 is already stuffed with changes. would have to be 1.1.

[Hugo Branquinho]

I can't use select_from() because i can have some filters defined. I get this "InvalidRequestError: Query.select_from() being called on a Query with existing criterion."

My Pagination class is used for all my apps, and in a older version i have a primary_key in arguments, to use as "func.count(PRIMARY)". However i found that i can use a func.count without a column, sending only 1. To keep the code clean, i remove this primary key, and make this Pagination class a little more flexible. I just sent the query, and the class give me the results with page, limit per page, etc. I have no problems, until a "empty" query appear.

"it almost seems like if the given expressions have no entities at all, then we'd keep the entities we have". I think this could solve the non-problem :)

[Mike Bayer]

well I can show you an implementation for that, and I can see some things that we can add to Query to make it easier, for the moment:

def col_expr(query, *expr):
    entities = set(e.entity_zero for e in query._entities if e.entity_zero)
    return query.with_entities(*expr).select_from(*entities)

print col_expr(query, func.count(1))

after I make the next commit, it will be:

def col_expr(query, *expr):
    entities = set(d['entity'] for d in query.column_descriptions)
    return query.with_entities(*expr).select_from(*entities)

[Mike Bayer]

• Added a new entry "entity" to the dictionaries returned by :attr:.Query.column_descriptions. This refers to the primary ORM mapped class or aliased class that is referred to by the expression. Compared to the existing entry for "type", it will always be a mapped entity, even if extracted from a column expression, or None if the given expression is a pure core expression. references #3320

→ <<cset b815e9483319>>

[Mike Bayer]

• Added a new entry "entity" to the dictionaries returned by :attr:.Query.column_descriptions. This refers to the primary ORM mapped class or aliased class that is referred to by the expression. Compared to the existing entry for "type", it will always be a mapped entity, even if extracted from a column expression, or None if the given expression is a pure core expression. references #3320

(cherry picked from commit b815e9483319b93f98bef11c7d47378441f78d21)

→ <<cset 285ed82f1324>>

[Hugo Branquinho]

That solve my problem, for this version and later :) Thanks for your time and help!

[Mike Bayer]

going to leave this for now, I think attempting to guess which entities to keep and not might be challenging and/or surprising.

[Mike Bayer]

• Fixed regression from as yet unreleased 0.9.10 where the new addition of entity to the :attr:.Query.column_descriptions accessor would fail if the target entity was produced from a core selectable such as a :class:.Table or :class:.CTE object. fixes #3403 references #3320

→ <<cset 20e3df602846>>

[Mike Bayer]

• Fixed regression from as yet unreleased 0.9.10 where the new addition of entity to the :attr:.Query.column_descriptions accessor would fail if the target entity was produced from a core selectable such as a :class:.Table or :class:.CTE object. fixes #3403 references #3320

(cherry picked from commit 20e3df602846bb1d8940b5138f21ef203c99bade)

→ <<cset de8a9fd81408>>