Your view should be":
def view():
tcolor=db.color.with_alias('tcolor')
ccolor=db.color.with_alias('ccolor')
accessible_works=db().select(db.work.ALL, tcolor.html_code,
ccolor.html_code,
left=[tcolor.on(tcolor.id==db.work.title_color),
ccolor.on(ccolor.id==db.work.cover_color)])
return dict(works=accessible_works)
you should never have db.<alias>
This worked for me:
>>> db.color.insert(html_code='ffffff')
1
>>> db.work.insert(title_color=1,cover_color=1)
1
>>> rows=db().select(db.work.ALL, t.html_code, c.html_code,
... left=[t.on(t.id==db.work.title_color),
... c.on(c.id==db.work.cover_color)])
>>> print rows
work.id,work.title_color,work.cover_color,t.html_code,c.html_code
1,1,1,ffffff,ffffff
>>> print rows[0]
<Row {'c': <Row {'html_code': 'ffffff'}>, 'work': <Row
{'update_record': <function <lambda> at 0x1010d6578>, 'cover_color':
1, 'id': 1, 'delete_record': <function <lambda> at 0x1010d6500>,
'title_color': 1}>, 't': <Row {'html_code': 'ffffff'}>}>
>>> print rows[0].c
<Row {'html_code': 'ffffff'}>
>>> print rows[0].c.html_code
ffffff
>>> print rows[0].t.html_code
ffffff
On Oct 12, 11:38 pm, Audra Rudys <[email protected]> wrote:
> Thank you, removing db from the alias names in the join part of the
> statement worked. I'm now getting a different error, which is more cryptic
> for me: KeyError: 'ccolor'
>
> I'm assuming the trouble is in the view... the snippet from the view that
> that references the value is:
> {{for work in works:}}
> {{=work.ccolor.html_code}}
> {{pass}}
>
> Note: I changed my aliases from c and t to ccolor and tcolor to be sure what
> the problem was, since KeyError: c was too vague. So the code in the
> controller now is:
> def view():
> tcolor=db.color.with_alias('tcolor')
> ccolor=db.color.with_alias('ccolor')
> accessible_works=db().select(db.work.ALL, db.tcolor.html_code,
> db.ccolor.html_code,
> left=[tcolor.on(tcolor.id==db.work.title_color),
> ccolor.on(ccolor.id==db.work.cover_color)])
> return dict(works=accessible_works)
>
> Am I referencing it properly in my view file?
>
> Should color appear as a keyword in the following line(from the error
> ticket):
> *Function argument list: (self=<Row {'color': <Row {'html_code': 'F0F8FF'}>,
> 'w... 'A story .'}>}>, key='ccolor')*
> *
> *
> I'm assuming it's a reference to my "color" table, but since I aliased it,
> I'm not sure why color would be there, I'd expect it to be substituted with
> ccolor or tcolor.
>
> Audra
> *
> *
>
> On Tue, Oct 12, 2010 at 11:06 PM, mdipierro <[email protected]> wrote:
> > This:
>
> > t=db.color.with_alias('t')
> > c=db.color.with_alias('c')
> > accessible_works=db().select(db.work.ALL, db.t.html_code,
> > db.c.html_code,
> > left=[db.t.on(db.t.id==db.work.title_color),
> > db.c.on(db.c.id==db.work.cover_color)])
>
> > Should be this:
>
> > t=db.color.with_alias('t')
> > c=db.color.with_alias('c')
> > accessible_works=db().select(db.work.ALL, t.html_code,
> > c.html_code,
> > left=[t.on(t.id==db.work.title_color),
> > c.on(c.id==db.work.cover_color)])
>
> > On Oct 12, 10:48 pm, Audra Rudys <[email protected]> wrote:
> > > Please help! I'm trying to join using the same table twice, thus I need
> > to
> > > do an alias. This is the first time I've ever tried an alias, so I don't
> > > know if I'm doing this right.
>
> > > If I were writing SQL code, I would write something like the following:
> > > SELECT db.work.*, db.tcolor.html_code, db.ccolor.html_code
> > > FROM db.work LEFT OUTER JOIN color as tcolor ON db.work.title_color ==
> > > tcolor.id
> > > LEFT OUTER JOIN color as ccolor ON db.work.cover_color == ccolor.id
>
> > > This is what I'm trying, but it's yielding an error: OperationalError:
> > > ambiguous
> > > column name: color.html_code
>
> > > def view():
> > > t=db.color.with_alias('t')
> > > c=db.color.with_alias('c')
> > > accessible_works=db().select(db.work.ALL, db.t.html_code,
> > db.c.html_code,
> > > left=[db.t.on(db.t.id==db.work.title_color),
> > > db.c.on(db.c.id==db.work.cover_color)])
> > > return dict(works=accessible_works)
>
> > > *I'm seeing the following in the variables: *('SELECT work.id,
> > > work.title, work.title_font, wor...lor LEFT JOIN color ON
> > > color.id=work.cover_color;',)
>
> > > *What am I doing wrong? (In case it's useful, I'm running on a PC
> > > *from source code, version 1.87.2, downloaded this morning.)
>
> > > Thanks for your help,
>
> > > Audra
>
>
