What I'm really trying to do is get the following SQL statement to run:
SELECT * FROM contact WHERE contactId IN (SELECT contactId FROM
contactCategory WHERE categoryId IN (1,3))
I think I may have just figured out a way to do it. The list of
categoryId's is coming from a MultiSelect widget in Turbogears. So,
here is what I'm doing:
contactsToIniclude = []
categoryIds = [1,3] # This will actually come from the MultiSelect widget
for categoryId in categoryIds:
categoryContactList = Category.get(categoryId).contacts
for contact in categoryContactList:
contactsToInclude.append(contact.id)
c = Contact.select()
contactList = c.filter(IN(Contact.q.id, contactsToInclude))
Thanks for the help in getting me here. I think my trouble was that I
was trying to do this all in one statement, which seems beyond my means
right now.
-Jim
Rick Flosi wrote:
for c in [Category.get(h).contacts for h in [1,3]]:
print c
c will be a list of SelectResult objects.
ie. [<SelectResults ...>, <SelectResults ...>]
I don't think this is what you want.
What's the SQL you want? What are you trying to get here?
--
Rick
On Wed, 20 Jun 2007, Jim Steil wrote:
Rick: Thanks for the info, the SQLRelatedJoin has gotten me a step
closer.
I've been doing some additional testing but am not confused about some
more
things.
If I do the following:
x = Category.get(1)
for y in x.contacts:
print y
I get a list of the contacts which is what I'd expect. However, if I
do the
following....
for c in [Category.get(h).contacts for h in [1,3]]:
print c
....I get the SQL statement that is used to retrieve the data.
Obviously, I
don't understand things at a lower level and don't know why I'm
getting this
difference. Is there something obvious that I'm missing? By the way,
I've
changed the RelatedJoin to SQLRelatedJoin in the Category and Contact
classes.
-Jim
Rick Flosi wrote:
Update your join to use SQLRelatedJoin instead of just RelatedJoin to
the the filter() function that I'm pretty sure is missing from
RelatedJoin.
Then you'd do something like:
c = Category.get(1)
c.contacts.filter(IN(Contact.q.id, [1,2,3]))
On Tue, 19 Jun 2007, Jim Steil wrote:
I've got a Contact class:
class Contact(SQLObject):
class sqlmeta:
style = Style(longID=True)
idName = 'contactId'
customerNumber = IntCol()
coopId = IntCol()
companyName = UnicodeCol(length=50)
...
categories = RelatedJoin("Category",
intermediateTable="contactCategory",
joinColumn="contactId",
otherColumn="categoryId")
and a Category class:
class Category(SQLObject):
class sqlmeta:
style = Style(longID=True)
idName = 'categoryId'
name = UnicodeCol(length=50)
description = UnicodeCol()
contacts = RelatedJoin("Contact",
intermediateTable="contactCategory",
joinColumn="categoryId",
otherColumn="contactId")
I've got 3 categories defined and want to get a list of the contacts
that belong to either Category 1 or Category 3. To do this with
SQL,
I'd do the following:
SELECT * FROM contact WHERE contactId IN (SELECT contactId FROM
contactCategory WHERE categoryId IN (1,3))
Is there a good sqlobject way to do this?
-Jim
------------------------------------------------------------------------
-
This SF.net email is sponsored by DB2 Express
Download DB2 Express C - the FREE version of DB2 express and take
control of your XML. No limits. Just data. Click to get it now.
http://sourceforge.net/powerbar/db2/
_______________________________________________
sqlobject-discuss mailing list
sqlobject-discuss@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
-------------------------------------------------------------------------
This SF.net email is sponsored by DB2 Express
Download DB2 Express C - the FREE version of DB2 express and take
control of your XML. No limits. Just data. Click to get it now.
http://sourceforge.net/powerbar/db2/
_______________________________________________
sqlobject-discuss mailing list
sqlobject-discuss@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
