Hi Alex,
Thanks a lot for your help! This is exactly what I needed. I almost
gave it up before and started to write plain SQL to get the desired
results ;)
Works like a charm now. In case anyone interested, I am pasting the
results below (BTW it's an interface to a (custom) table from Roundup
issue tracker)
Ksenia.
class Ticket(RoundupModel):
@classmethod
def _get_base_query(cls):
assigned_alias = aliased(User)
creator_alias = aliased(User)
actor_alias = aliased(User)
result = session.query(Ticket).join(
Ticket.project,
Project.customer,
Ticket.priority,
Ticket.status, (creator_alias,
Ticket.creator), (actor_alias, Ticket.actor)
).outerjoin(
Ticket.assignedtodepartment,
(assigned_alias, Ticket.assignedto)
).options(
contains_eager(Ticket.project),
contains_eager(Ticket.project, Project.customer),
contains_eager(Ticket.priority),
contains_eager(Ticket.status),
contains_eager(Ticket.assignedtodepartment),
contains_eager(Ticket.assignedto,
alias=assigned_alias),
contains_eager(Ticket.creator,
alias=creator_alias),
contains_eager(Ticket.actor, alias=actor_alias),
)
return result
And the relevant part of the mapper:
mapper(Ticket, tickets,
properties={
'status':relation(TicketStatus),
'priority':relation(TicketPriority),
'actor':relation(User, primaryjoin=users.c.id ==
tickets.c._actor),
'creator':relation(User, primaryjoin=users.c.id ==
tickets.c._creator),
'project':relation(Project),
'assignedto':relation(User, primaryjoin=users.c.id
== tickets.c._assignedto),
'assignedtodepartment':relation(Department,
primaryjoin=departments.c.id == tickets.c._assignedtodepartment),
}
)
On Sep 29, 11:19 am, Alex K <[EMAIL PROTECTED]> wrote:
> Hello, Ksenia,
>
> This may help:
>
> http://www.sqlalchemy.org/docs/05/mappers.html#advdatamapping_relatio...
>
> On Sep 29, 2:38 am, Ksenia <[EMAIL PROTECTED]> wrote:
>
> > Hi list,
>
> > I can't figure out a very simple thing. All I want is to do this
> > query:
>
> > select table1.A, table2.B, table3.C from table1, table2, table3 where
> > table1.foreignkey2 = table2.primarykey and table1.foreignkey3 =
> > table3.primarykey;
>
> > When rendering results, I want to be able to refer to the properties
> > of the joined tables:
>
> > <td>${row.A}</td>
> > <td>${row.table2.B}</td>
> > <td>${row.table3.C}</td>
>
> > My mapper looks like:
>
> > mapper(Table1, table1,
> > properties={
> > 'table2':relation(Table2, lazy=False,
> > uselist=False),
> > 'table3':relation(Table3, lazy=False,
> > uselist=False)
> > }
> > )
>
> > When I run it it creates a query with tables joined via LEFT OUER
> > JOIN.
>
> > How can I non-lazy (with minimal number of queries) join tables,
> > without LEFT OUTER JOIN? But still be able to refer to the properties
> > of joined tables?
>
> > Appreciate a hint.
>
> > Thanks
> > Ksenia.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
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
-~----------~----~----~----~------~----~------~--~---
