On Mon, Jul 05, 2010 at 11:20:31AM +0200, Till Maas wrote:
> Oleg Broytman wrote:
> > MultipleJoin works together with a corresponding ForeignKey. .select()
> > doesn't take them into account. So either you iterate over Packages row by
> > row and use .builds attribute of every row, or construct the query
> > manually
>
> Can you maybe give me a hint how to create the query
I will. See below.
> Or is there some support for using custom
> queries in SQLObject that make SQL-Injections impossible e.g. by using
> prepared statements?
Unfortunately, no.
> >> class PackageUpdate(SQLObject):
> >> title = UnicodeCol(notNone=True, alternateID=True)
> >> builds = RelatedJoin("PackageBuild")#
> >>
> >> class PackageBuild(SQLObject):
> >> nvr = UnicodeCol(notNone=True, alternateID=True)
> >> package = ForeignKey('Package')
> >> updates = RelatedJoin("PackageUpdate")
> >>
> >> class Package(SQLObject):
> >> name = UnicodeCol(alternateID=True, notNone=True)
> >> builds = MultipleJoin('PackageBuild',
> >> joinColumn='package_id')
> >>
> This is the query that does not work unexpectedly, because
> query.append(PackageUpdate.q.builds == builds)
> updates = PackageUpdate.select(AND(*query), orderBy=order).reversed()[:20]
> creates this error:
> AttributeError: PackageUpdate instance has no attribute 'builds'
I will explain how RelatedJoin works and the reason for the error. To
simplify my explanations I will omit class Package.
RelatedJoins are always come in pairs - two tables points to each other.
They look rather simple, but SQLObject does some magic to implement them.
It creates an intermediate table that stores many-to-many references. There
is nothing too magical about the table - it is just an ordinary SQL table.
The only magical thing is that SQLObject creates it itself behind the scene
and builds queries when one asks for attributes build.updates and
update.builds.
SQLObject doesn't promote these attributes to the .q. namespace, hence
you cannot use them in .select(). Really, there is no PackageUpdate.q.builds,
only update.builds (where 'update' is a row from the PackageUpdate table).
Now we have to look under the hood. The intermediate table can be
declared in SQLObject terms. For example, to add additional columns. See
http://sqlobject.org/FAQ.html#how-can-i-define-my-own-intermediate-table-in-my-many-to-many-relationship
In your particular case you need the table to explicitly build a join
(to overcome the limitations in SQLObject that cannot build implicit
may-to-many joins itself). The table declaration is
class PackageBuildPackageUpdate(SQLObject):
package_build = ForeignKey("PackageBuild")
package_update = ForeignKey("PackageUpdate")
(To name the intermediate table SQLObject combines the names of the
related tables in alphabetical order. You can change the name if you use
parameters intermediateTable='...')
When one asks for update.builds SQLObject generates a query like this:
PackageBuild.select(
PackageBuild.q.id==PackageBuildPackageUpdate.q.package_buildID &
PackageBuildPackageUpdate.q.package_updateID==update.id)
All you need now is to extend that very query. SQLObject can slightly
relive the pain of constructing such queries. It has a magic .j namespace
to help to build complex joins. The query above can be rewritten as
PackageBuild.select(PackageBuild.j.updates & PackageUpdate.q.id==update.id)
Try to add your own joins to the query.
Oleg.
--
Oleg Broytman http://phd.pp.ru/ ph...@ph...
Programmers don't die, they just GOSUB without RETURN.
|
