On Jan 25, 2011, at 1:17 AM, Lenza McElrath wrote: > Hey Royce, > > This sounds like a job for composite columns: > http://www.sqlalchemy.org/docs/orm/mapper_config.html#composite-column-types > > One gotcha that I ran into here is that you cannot have both the component > columns and the composite column mapped at the same time, like you do in your > example. So depending on what you are trying to do, you might need to make a > comparable property instead: > http://www.sqlalchemy.org/docs/orm/mapper_config.html#custom-comparators > > Let me know if you need any more help.
The original example with "course_name = func.CONCAT" was almost correct - its just that declarative needs more of a hint than that. To map a SQL expression you want to use column_property(): http://www.sqlalchemy.org/docs/orm/mapper_config.html#sql-expressions-as-mapped-attributes http://www.sqlalchemy.org/docs/orm/extensions/declarative.html#declarative-sql-expressions composite() is not quite what you want here, though in 0.7 it will no longer conceal the underlying columns it uses. A major reason not to use composite() before 0.7 though is that it will flip the "mutable" flag on the parent object, which pretty much kills performance for large numbers of objects. We're doing away with this method of handling in-place mutability in 0.7. > > -Lenza > > On Mon, Jan 24, 2011 at 7:35 PM, Royce <[email protected]> wrote: > Hi does anyone know if is possible to declaratively concatenate two > columns together which you can later do query's on. > > E.g. if I wanted to compute a new column course_name made up of > CONCAT(course_code,course_name) > > Base = declarative_base() > class Course(Base): > __tablename__ = 'Course' > > course_code = Column(VARCHAR(length=4), nullable=False) > course_num = Column(INTEGER(), nullable=False) > > course_name = func.CONCAT(course_code,course_num) # only an > example, this doesn't actually work > > > So later you could do queries on the Course table like > > course_data = > session.query(Course).filter( Course.course_name.op('regexp') > ('^A.*4') )).first() > print course_data.course_name > > > It is possible to do a query to generate the data outside the Course > class as below, but how can you > make it as a normal mapped column in the Course class ? > > query = session.query( func.CONCAT(Course.course_code, > Course.course_num) ) > query = query.filter( func.CONCAT(Course.course_code, > Course.course_num).op('regexp')('^A.*4') ) > > Cheers > Royce > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to [email protected]. > To unsubscribe from this group, send email to > [email protected]. > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. > > > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to [email protected]. > To unsubscribe from this group, send email to > [email protected]. > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
