Re: [SQLObject] Looking for selection hints

[Frank B. <fb...@fo...>]

Hallo,
Frank Barknecht hat gesagt: // Frank Barknecht wrote:

> Hallo,
> Ian Bicking hat gesagt: // Ian Bicking wrote:
> 
> > On Sunday, October 26, 2003, at 04:48 PM, Frank Barknecht wrote:
> > > {'title': ['rock'], 'format': ['cd'], 'artist': ['strokes', 'the']}
> > 
> > query = None
> > for colName, ops in above_dict.items():
> >     for op in ops:
> >         subquery =  LIKE(getattr(Product.q, colName), op)
> >         if query:
> >             query = AND(query, subquery)
> >         else:
> >             query = subquery
> > 
> > 
> > It might be nice if Product.q allowed dictionary access too.  And if 
> > AND(anything, None) return anything.  But this will work too.
> 
> I *knew* it had to be something basic (or built-in). A thousand thanks
> for opening my eyes.

So, and to give something back, here's my QueryBuilder class that's
intended to be used with Webware's extraPathInfo and automates
searching via the extra-URL:

### snip ###

from SQLObject import *

class QueryBuilder(object):

	def __init__(self, table, url=""):
		self.__url = url
		self.__sql = None
		self.__conds = {}
		self._table = table
		self._cols = self._table._SO_columnDict.keys()
		id url:
			self.parse()

	def set_url(self, url):
		self.__url = url
		self.parse()
	
	def get_sql(self):
		query = None
		for colName, ops in self.__conds.items():
			for op in ops:                     
				subquery =  LIKE(getattr(self._table.q, colName), "%%%s%%" % op)
				if colName == "id":
					subquery = self._table.q.id == op
				if query:
					query = AND(query, subquery)
				else:
					query = subquery
		return query

	def parse(self):
		cols = self._cols
		cols.append("id")
		u = self.__url.split("/")
		l = len(u)
		i = 0
		conds = {}
		last = None
		while i < l:
			if last and u[i]:
				conds[last] = conds.get(last, [])
				conds[last].append(u[i])
				last = None
			elif u[i] in cols:
				last = u[i] 
			i += 1	
		self.__conds = conds

	url =  property(None, set_url, None, "URL property, write only")
	sql =  property(get_sql, None, None, "SQL property, read only")

### snip ###

Not that elegant, but working fine for "flat" databases.  It's used
like this: 

# Product is an SQLObject with attrs: name, title, description,...
q = QueryBuilder(Product)
q.url = "name/potter/name/harry/description/book/foo/bar/"
print q.sql
print "Number of results: %d " % len (table.select(q.sql))
for n in list(table.select(q.sql)[0:10]):
	print "%s: %s" % (n.name, n.title)


ciao
-- 
 Frank Barknecht                               _ ______footils.org__