Ben Sizer <kylotan <at> gmail.com> writes:
> So, how would I implement queries like the following?
>
> # All User 1's skills where competence > 3
> SELECT * from User INNER JOIN Skill USING (user_id) INNER JOIN
> Skilltype USING (stype_id) WHERE user_id = 1 AND Skilltype.competence
> > 3
Well, note that SQLObject won't know what to do with joined results (all columns
from User AND all columns from Skill), so in one query you're only going to get
instances of one of your SQLObject classes. In this case that's sort of trivial
since you're already only talking about a specific user, so I'll assume you just
want Skill or SkillType instances from a given User instance.
Define the SQLRelatedJoin to SkillType in User, explicitly specifying your
intermediateTable:
skillTypes = SQLRelatedJoin('SkillType', intermediateTable='Skill',
otherColumn='stypeID', ...)
user1 = User.get(1)
for x in user1.skillTypes.filter(Skill.q.competence>3):
print x.name
As noted previously on the list, this only really becomes awkward when you then
want to still display/use the intermediate table's competence value - you're
probably better off in that case filtering on .skills and dereferencing the
RelatedJoin fkey yourself:
for x in user1.skills.filter(Skill.q.competence>3):
print x.stype.name, x.competence
rather than joining through to the SkillType instances and then trying to come
back to the Skill intersection. If necessary you can make the above style more
efficient by pre-querying the full join (to get the necessary Skill objects into
the instance cache) and then the x.stype references shouldn't need to go back to
the database as the stypeID will be found in the cache (assuming
connection.cache and cacheValues are True):
q = Skill.q.competence>3
list(user1.skillTypes.filter(q)) #One db query
for x in user1.skills.filter(q): # Another db query
print x.stype.name # No db query
> Or...
>
> # All users who have the Skill "xyz"
> SELECT * from User INNER JOIN Skill USING (user_id) INNER JOIN
> Skilltype USING (stype_id) WHERE Skilltype = "xyz" GROUP BY User
>
Hmm. My brain is too foggy this early to see why you're using the GROUP BY,
which usually does not map to SQLObject selects in a meaningful way (again,
because we're just going to return instances of User). But there's a couple
options I think for the intent here.
As above, you can define a RelatedJoin in addition to the MultipleJoin, so if
you put a "users" join on SkillType with your custom intermediateTable etc then
it's automatic:
# Assuming skilltype.name can be an alternateID
s_xyz = SkillType.byName('xyz')
# Or however else you can get an actual instance of your 'xyz' skill
for user in s_xyz.users:
...
If skilltype.name isn't a ckey I think you'll have to manually join:
for user in User.select(AND(User.q.id==Skill.q.userID,
Skill.q.stypeID==SkillType.q.id, SkillType.q.name=='xyz')):
...
But if that's the case why are you normalizing out to a SkillType table? :)
Hopefully you can extrapolate from here, for instance all users with a
competence > 3 in skill 'xyz':
s_xyz.users.filter(Skill.q.competence>3)
(I haven't tested any of this specifically, but I believe if any of the above
doesn't work it's probably a bug and I'll be happy to look into it.)
If you really want all the joined columns in your result objects, about your
only option is to define a view and have a SQLObject class map to that, but my
guess is you'll get along fine without that.
- Luke
-------------------------------------------------------------------------
Take Surveys. Earn Cash. Influence the Future of IT
Join SourceForge.net's Techsay panel and you'll get the chance to share your
opinions on IT & business topics through brief surveys - and earn cash
http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
_______________________________________________
sqlobject-discuss mailing list
sqlobject-discuss@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
