You're right, that should work. I wonder if there would be a significant
performance hit running a LEFT JOIN in place of an INNER JOIN. I'll have to
keep an eye on that to see if it makes much difference, but in my particular
case (less than 100 records) I think I'll be fine.
Thanks for the quick response,
Mike
On Tue, Dec 21, 2010 at 5:22 PM, mdipierro <mdipie...@cs.depaul.edu> wrote:
> yes alias only works with left joins. But you can mimic one with the
> other to I tried this which I think is equivalent to your query:
>
> db=DAL()
> District =
>
> db.define_table('district',Field('description'),Field('district_id','reference
> district'))
>
> SchoolDistrict = District.with_alias('school_district')
> rows= db(District.district_id!
>
> =None).select(District.description,SchoolDistrict.description,left=SchoolDistrict.on(SchoolDistrict.
> \
> id==District.district_id))
> print rows
>
> Makes this sql:
>
> SELECT district.description, school_district.description FROM
> district LEFT JOIN district AS school_district ON (school_district.id
> = district.district_id) WHERE (district.district_id IS NOT NULL);
>
>
>
>
>
> On Dec 21, 4:09 pm, Michael Wolfe <michael.joseph.wo...@gmail.com>
> wrote:
> > I'm actually running off of trunk already (latest changeset is 1410
> > (feb4ddb2d659), committed 14 hours ago).
> >
> > The book example only demonstrates using with_alias in LEFT JOINs. Is
> this
> > a limitation?
> >
> > If it makes any difference, I am running dal.py as a standalone module
> for a
> > non-web2py project. (Speaking of using it as a standalone module, I
> almost
> > wet myself when I saw how easy it was to use it that way. Nice work on
> the
> > re-write.)
> >
> > Thanks,
> > Mike
> >
> >
> >
> >
> >
> > On Tue, Dec 21, 2010 at 5:00 PM, mdipierro <mdipie...@cs.depaul.edu>
> wrote:
> > > there is a bug in 1.90.6 about with_alias. I think it is fixed in
> > > trunk. give it a try.
> >
> > > On Dec 21, 3:46 pm, mwolfe02 <michael.joseph.wo...@gmail.com> wrote:
> > > > I have a legacy table with a three-character primary key and a three-
> > > > character self-referencing field. I'm trying to implement the
> > > > following sql using the DAL:
> >
> > > > SELECT District.Description, SchoolDistrict.Description
> > > > FROM District.DistrictID
> > > > INNER JOIN District AS SchoolDistrict
> > > > ON District.SchoolDistrictID = SchoolDistrict.DistrictID
> >
> > > > I thought I could use with_alias to do this but it does not seem to
> be
> > > > working.
> >
> > > > Thanks,
> > > > Mike
> >
> >
>
