On Fri, Jun 22, 2007 at 07:22:05PM +0200, Christoph Haas wrote:
>
> On Fri, Jun 22, 2007 at 04:40:58PM -0000, Michael Bayer wrote:
> > On Jun 22, 12:12 pm, Christoph Haas <[EMAIL PROTECTED]> wrote:
> > > 2007-06-22 18:09:57,852 INFO sqlalchemy.engine.base.Engine.0x..6c SELECT
> > > records_a.id, records_a.domain_id, records_a.dhcpzone_id, records_a.name,
> > > records_a.type, records_a.content, records_a.ttl, records_a.prio,
> > > records_a.change_date, records_a.mac_address, records_a.inet
> > > FROM records AS records_a LEFT OUTER JOIN records AS records_ptr ON
> > > records_a.inet = records_ptr.inet AND records_ptr.type =
> > > %(records_ptr_type)s
> > > WHERE records_a.type = %(records_a_type)s
> > > 2007-06-22 18:09:57,852 INFO sqlalchemy.engine.base.Engine.0x..6c
> > > {'records_a_type': 'A', 'records_ptr_type': 'PTR'}
> > >
> > > In [5]: result.keys()
> > > Out[5]:
> > > ['id',
> > > 'domain_id',
> > > 'dhcpzone_id',
> > > 'name',
> > > 'type',
> > > 'content',
> > > 'ttl',
> > > 'prio',
> > > 'change_date',
> > > 'mac_address',
> > > 'inet']
> > >
> > > As you see the keys are just there once. Although the actual SQL result
> > > contains these columns for both records_a and the joined records_ptr.
> >
> > set use_labels=True in your select() statement. the point of that is
> > to combine table or alias names with column names as labels for all
> > selected columns, so that names are automatically unique. the column-
> > targeting i mentioned also would rely upon this to differentiate
> > between the two tables.
>
> Thank you. It's a tiny bit closer. However that still only gives the
> left side of the join as results:
>
> result=records_a.select(
> records_a.c.type=='A',
> from_obj=[model.outerjoin(records_a, records_ptr,
> ( (records_a.c.inet==records_ptr.c.inet) &
> (records_ptr.c.type=='PTR') ))],
> use_labels=True).execute().fetchone()
>
> In [9]: result
> Out[9]: (108914, 1, None, u'foo.domain.tld', u'A', u'192.168.26.39', 86400,
> 0, None, None, '192.168.26.39')
>
> In [10]: result.keys()
> Out[10]:
> ['records_a_id',
> 'records_a_domain_id',
> 'records_a_dhcpzone_id',
> 'records_a_name',
> 'records_a_type',
> 'records_a_content',
> 'records_a_ttl',
> 'records_a_prio',
> 'records_a_change_date',
> 'records_a_mac_address',
> 'records_a_inet']
Update: I found another way - but with .select instead of .outerjoin:
In [9]: result = model.select([records_a, records_ptr],
(records_a.c.inet==records_ptr.c.inet) & (records_ptr.c.type=='PTR'),
use_labels=True).execute().fetchone()
In [10]: result.keys()
Out[10]:
['records_a_id',
'records_a_domain_id',
'records_a_dhcpzone_id',
'records_a_name',
'records_a_type',
'records_a_content',
'records_a_ttl',
'records_a_prio',
'records_a_change_date',
'records_a_mac_address',
'records_a_inet',
'records_ptr_id',
'records_ptr_domain_id',
'records_ptr_dhcpzone_id',
'records_ptr_name',
'records_ptr_type',
'records_ptr_content',
'records_ptr_ttl',
'records_ptr_prio',
'records_ptr_change_date',
'records_ptr_mac_address',
'records_ptr_inet']
So I finally get both sides of the join. But this appears to be just an
INNER JOIN. There are records_a with no matching records_ptr and vice
versa. So my actual goal is a FULL OUTER JOIN that just fills
non-matching rows with NULL values. If I understoog correctly then full
outer joins are not yet supported due to some complications with Oracle
databases. So I thought I'd try a LEFT OUTER JOIN with a UNION of a
INNER (RIGHT) JOIN as a workaround as described at
http://en.wikipedia.org/wiki/Join_(SQL)#Full_outer_join (last
paragraph).
Since the above query is just an INNER JOIN I'd have to do a LEFT JOIN +
INNER JOIN + RIGHT JOIN.
Phew...
Christoph
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
