On Thursday 11 October 2007 08:38:22 krsyoung wrote:
> Hey all,
>
> Does anybody have a slick way to build up sql queries for use with
> SQLObject? I've looked at the sqlbuilder documentation but I don't
> see how to build it automatically (ie with multiple if statements or a
> for loop). I've also looked at the "Smart Queries" section of the
> Turbogears book but it creates the queries statically.
>
> What I'm specifically looking to do is make a search page, but I only
> want to search for fields entered and I also want to AND all of the
> values. Overall there will be about 10 columns that can be searched,
> varying in type (BoolCol, StringCol...).
>
> In pseudo(ish) code:
>
> if name defined:
> whereClause.append(Request.q.name.contains(name))
>
> if alive defined:
> whereClause.append(Request.q.alive == True)
It's easy. You can just create complex statements by combining them the way
you do inside a select - just out of it. From my production code:
lang = self['language']
filterClause, orderBy = [], None
name_join = AND(m.BaseBookable.q.nameID == m.I18NText.q.id,
m.I18NText.q.id == m.I18NEntry.q.parentID,
m.I18NEntry.q.lang == lang
)
if self.order == 'name':
orderBy = m.I18NEntry.q.text
if self['_name']:
filterClause.append(AND(m.BaseBookable.q.nameID ==
m.I18NText.q.id,
m.I18NText.q.id == m.I18NEntry.q.parentID,
m.I18NEntry.q.lang == lang,
LIKE(func.LOWER(m.I18NEntry.q.text), "%%%s%%" % self['_name'].lower())))
show_normal, show_tours = self['show_normal'], self['show_tours']
#, show_guides = , self['show_guides']
joins = []
# if any of the above are _not_ checked, we need to alter the
filterclause
if not(show_normal and show_tours): # and show_guides):
typeClauses = []
if show_normal:
typeClauses.append(m.BaseBookable.q.childName == "Bookable")
#if show_guides:
# typeClauses.append(m.BaseBookable.q.childName
== "GuideBookable")
if show_tours:
typeClauses.append(m.BaseBookable.q.childName == "Tour")
if not typeClauses:
typeClauses.append(NOT(SQLTrueClause))
if filterClause:
filterClause = [AND(*(filterClause + [OR(*typeClauses)]))]
else:
filterClause = OR(*typeClauses)
else:
# we have no filtering, so remove the features from
# this query.
typeClause = AND(m.BaseBookable.q.childName != 'Feature')
if filterClause:
filterClause = [AND(filterClause, typeClause)]
else:
filterClause = [typeClause]
if not self['show_deleted']:
filterClause = AND(filterClause, m.BaseBookable.q.deleted ==
False)
clauses = [name_join]
if filterClause:
clauses.append(filterClause)
clause = AND(*clauses)
self._the_result = m.BaseBookable.select(clause,
join=joins,
orderBy=orderBy)
Obviously, this doesn't make much sense to you object-model-wise - but you
should get an idea how to work with SO.
Diez
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"TurboGears" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/turbogears?hl=en
-~----------~----~----~----~------~----~------~--~---
