Self-Referential scalar query as column_property

[Adam Tauno Williams]

I have a table named "project" [class Project] which relates to records
in table "job" [class Task]. I can add a column_property to Project so
that I get a count of the tasks when I instatiante a Project object.


class Project(Base)
...
task_count = column_property(
select([func.count(Task.object_id), ]).
where(
Task.project_id == object_id
).as_scalar())
...

- works great, still happens in one query with count gathered as a
subselect in a column.

Projects can also be parents of other projects and I need a similair
child project count. But I cannot add the relation in the same manner
as the Project class [self-referentially] has not been defined yet.

I can add a column_property after the class has been defined:

Project.project_count = column_property(
select([func.count(Project.object_id), ]).\
where(Project.parent_id==Project.object_id).as_scalar())

- but that produces [as I expected] -

(SELECT count(project.project_id) AS count_4
FROM project
WHERE project.parent_project_id = project.project_id) AS anon_4,

which is clearly incorrect.

I think there is something like "extend_existing=True" for adding to a
class mapper... but I can't find an example of the syntax that works.

--
Adam Tauno Williams <mailto:awil...@whitemice.org> GPG D95ED383
Systems Administrator, Python Developer, LPI / NCLA

[Michael Bayer]

the project_count property would be made in terms of an alias() of the table. I'd probably use Project.__table__.alias() as the target. you'd still be tacking it on as "Project.project_count = prop.." so that __table__ is ready to go.

[Adam Tauno Williams]

Ok, I have something working; but it seems like there should be a more
elegant way. I am declaring an alias of the Project mapper class in
order to make it work - that seems awkward. Is there anyway to embed
the appropriate aliasing in the single statement? I cannot figure out
how to reference a instance of the __table__.alias() in the same
statement.

from sqlalchemy.orm import aliased

project_count_alias = aliased(Project)

Project.project_count = column_property(
select([func.count(project_count_alias.object_id), ]).\
where(project_count_alias.parent_id==Project.object_id).
as_scalar(), )

[Michael Bayer]

On Oct 1, 2013, at 8:30 PM, Adam Tauno Williams <awil...@whitemice.org> wrote:

>
> Ok, I have something working; but it seems like there should be a more
> elegant way. I am declaring an alias of the Project mapper class in
> order to make it work - that seems awkward.

it looks fine to me? if you want to query self-referentially in SQL, you need an alias. that SQLAlchemy lets you use the alias here just as you would in SQL, lets you add it to the mapping and all with no extra hacks, seems natural to me...guess it has to do with where you're coming form.

> Is there anyway to embed
> the appropriate aliasing in the single statement? I cannot figure out
> how to reference a instance of the __table__.alias() in the same
> statement.

you can refer to __table__.alias() in much the same way, just use the .c. attribute to get at the columns, not sure what aspect you're getting at here, is it that you want the column property declared inside the class declaration? you can use __declare_last__ as one option, here is that:

class Project(Base):
__tablename__ = 'project'

object_id = Column(Integer, primary_key=True)
parent_id = Column(Integer, ForeignKey('project.object_id'))

@classmethod
def __declare_last__(cls):
p_alias = cls.__table__.alias()
cls.project_count = column_property(
select([func.count(p_alias.c.object_id)]).\
where(p_alias.c.parent_id == cls.object_id).
as_scalar()
)

>
> from sqlalchemy.orm import aliased
>
> project_count_alias = aliased(Project)
>
> Project.project_count = column_property(
> select([func.count(project_count_alias.object_id), ]).\
> where(project_count_alias.parent_id==Project.object_id).
> as_scalar(), )
>
> --
> Adam Tauno Williams <mailto:awil...@whitemice.org> GPG D95ED383
> Systems Administrator, Python Developer, LPI / NCLA
>

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

[Adam Tauno Williams]

On Tue, 2013-10-01 at 20:53 -0400, Michael Bayer wrote:
> is it that you want the column property declared inside the class
> declaration? you can use __declare_last__ as one option, here is
> that:
> class Project(Base):

...

> @classmethod
> def __declare_last__(cls):
> p_alias = cls.__table__.alias()
> cls.project_count = column_property(
> select([func.count(p_alias.c.object_id)]).\
> where(p_alias.c.parent_id == cls.object_id).
> as_scalar()
> )

Yes, this is exactly what I was looking for; otherwise the definition of
the model definition [it is a complex model] gets sort of smeared. This
works perfectly.