Need sqlobject help with 3-table inner join

[Matt Wilson]

Table A joins to multiple rows in B, and B multiple joins to table C.

In my model.py, I want to add a method on class A that returns all the
rows from table C.

This is the ugly solution I've got so far:

def f(a):
rows = []
for x in a.rows_in_b:
rows += x.rows_in_c

return rows

I could do this very easily with a triple join in SQL. However, doing
a triple join in SQL wouldn't be able to grab all rows in D if I had an
A-B-C-D relationship instead.

What's the better way?

TIA

Matt

[Paul Johnston]

Hi,

You could create an SQL view, something like

create view a_c as
select c.*, a.id
from a, b, c
where b.a_id = a.id and c.b_id = b.id;

A model class something like (note: derived from the class for C)

class A_C(C):
aID = ForeignKey('a')

Then it's just a case of including a MultipleJoin column in A.

I hope this explains it properly, if anything isn't clear just drop me a
line.

Paul

[Matt Wilson]

But what if tomorrow, I create a table D, or even tables D, E, and F,
and ultimately I need to get all rows in F that descend from a row in
A?

I was hoping there was some SQLObject cleverness for this, that would
handle any arbitrarilly long joins.

Thanks for the input.

Matt

[Joost Moesker]

OrderLine.select(AND( OrderLine.q.orderID==Order.q.id,
Order.q.customerID==Customer.q.id,
Custommer.q.name=="john"))

[Diez B. Roggisch]

On Monday 11 December 2006 01:01, Matt Wilson wrote:
> Table A joins to multiple rows in B, and B multiple joins to table C.
>
> In my model.py, I want to add a method on class A that returns all the
> rows from table C.
>
> This is the ugly solution I've got so far:
>
> def f(a):
> rows = []
> for x in a.rows_in_b:
> rows += x.rows_in_c
>
> return rows

Something like this (untested):

class A:

def get_all_c(self):
return C.select(AND(C.q.parent_id == B.q.id, B.q.parent_id ==
self.id))

If there are n:m-relations, you need to define an alias for the m:n-table.
This is an example of my code that works with the standard identity model:

user_group = Alias('user_group', 'user_group')
groupClauses = [AND(m.User.q.id == user_group.q.user_id, user_group.q.group_id
== groupID) for groupID in self['groups']]

HTH,

Diez