def _joinTableSQL(self,
conn,
thisAttrNames,
pivotTable,
thisSideColumns,
thatSideColumns,
thatObject,
thatAttrNames,
extraTables,
wheresql,
wherevals,
order,
limit,
offset):
"""SQL generating function for joinTable"""
if extraTables is None:
extraTables=[]
thisAttrNames = _tupleize(thisAttrNames)
thisSideColumns = _tupleize(thisSideColumns)
thatSideColumns = _tupleize(thatSideColumns)
thatAttrNames = _tupleize(thatAttrNames)
if len(thisSideColumns) != len(thisAttrNames):
raise ValueError, ('thisSideColumns and thisAttrNames must '
'contain the same number of elements')
if len(thatSideColumns) != len(thatAttrNames):
raise ValueError, ('thatSideColumns and thatAttrNames must '
'contain the same number of elements')
sql=[thatObject._baseSelect(True),
', ',
', '.join([pivotTable]+extraTables),
' WHERE ']
joins = []
converter=conn.getConverter()
for attr, col in zip(thisAttrNames, thisSideColumns):
lit=converter(self[attr])
joins.append("%s.%s = %s" % (pivotTable, col, lit))
vals=converter.values
joins.extend(['%s.%s = %s.%s' % (pivotTable,
col,
thatObject.getTable(),
attr) \
for attr, col in zip(thatAttrNames,
thatSideColumns)])
sql.append(' AND '.join(joins))
if wheresql:
sql.append(' AND (%s)' % wheresql)
if wherevals:
vals=vals+wherevals
if filter(None, (order, limit, offset)):
sql.append(conn.orderByString(order, limit, offset))
return ''.join(sql), vals
def joinTable(self,
thisAttrNames,
pivotTable,
thisSideColumns,
thatSideColumns,
thatObject,
thatAttrNames,
*whereArgs,
**extra):
"""Handles many to many relations. In short, do:
SELECT thatObject.getColumns(1)
FROM thatObject.table, pivotTable
WHERE pivotTable.thisSideColumn = self.myAttrName
AND pivotTable.thatSideColumn = thatObject.table.thatAttrName
and return a list of thatObjects representing the resulting
rows. The parameters which accept column names
(thisAttrNames, thisSideColumns, thatSideColumns,
thatAttrNames) can be strings (silently turned to tuples of
length 1) or tuples of strings. For each pair P of the two
pairs (thisSideColumns, thisAttrNames) and (thatSideColumns,
thatAttrNames) len(P[0]) must equal len(P[1]).
In addition, you can add extra tables and arbitrary sql to the
where clause, as you can with getSome(), including order,
limit and offset, using the "extraTables", "order", "limit"
and "offset" keyword arguments, using SQLOperators or
(sql-string, bind values) as positional arguments, and by
specifying columns by keyword argument.
"""
extraTables=extra.pop('extraTables', None)
if extraTables:
extraTables=list(_tupleize(extraTables))
order=extra.pop('order', None)
limit=extra.pop('limit', None)
offset=extra.pop('offset', None)
conn=self.getDBI()
wheresql, wherevals=self._processWhere(conn, whereArgs, extra)
sql, vals = self._joinTableSQL(conn,
thisAttrNames,
pivotTable,
thisSideColumns,
thatSideColumns,
thatObject,
thatAttrNames,
extraTables,
wheresql,
wherevals,
order,
limit,
offset)
results = conn.execute(sql, vals)
if results:
return map(thatObject, results)
return []
def _joinTableSQL(self, conn, thisAttrNames, pivotTable, thisSideColumns,
thatSideColumns, thatObject, thatAttrNames, extraTables,
wheresql, wherevals, order, limit, offset):
"""SQL generating function for joinTable"""
if extraTables is None:
extraTables = []
thisAttrNames = _tupleize(thisAttrNames)
thisSideColumns = _tupleize(thisSideColumns)
thatSideColumns = _tupleize(thatSideColumns)
thatAttrNames = _tupleize(thatAttrNames)
if len(thisSideColumns) != len(thisAttrNames):
raise ValueError, ('thisSideColumns and thisAttrNames must '
'contain the same number of elements')
if len(thatSideColumns) != len(thatAttrNames):
raise ValueError, ('thatSideColumns and thatAttrNames must '
'contain the same number of elements')
sql = [
thatObject._baseSelect(True), ', ',
', '.join([pivotTable] + extraTables), ' WHERE '
]
joins = []
converter = conn.getConverter()
for attr, col in zip(thisAttrNames, thisSideColumns):
lit = converter(self[attr])
joins.append("%s.%s = %s" % (pivotTable, col, lit))
vals = converter.values
joins.extend(['%s.%s = %s.%s' % (pivotTable,
col,
thatObject.getTable(),
attr) \
for attr, col in zip(thatAttrNames,
thatSideColumns)])
sql.append(' AND '.join(joins))
if wheresql:
sql.append(' AND (%s)' % wheresql)
if wherevals:
vals = vals + wherevals
if filter(None, (order, limit, offset)):
sql.append(conn.orderByString(order, limit, offset))
return ''.join(sql), vals
def joinTable(self, thisAttrNames, pivotTable, thisSideColumns,
thatSideColumns, thatObject, thatAttrNames, *whereArgs,
**extra):
"""Handles many to many relations. In short, do:
SELECT thatObject.getColumns(1)
FROM thatObject.table, pivotTable
WHERE pivotTable.thisSideColumn = self.myAttrName
AND pivotTable.thatSideColumn = thatObject.table.thatAttrName
and return a list of thatObjects representing the resulting
rows. The parameters which accept column names
(thisAttrNames, thisSideColumns, thatSideColumns,
thatAttrNames) can be strings (silently turned to tuples of
length 1) or tuples of strings. For each pair P of the two
pairs (thisSideColumns, thisAttrNames) and (thatSideColumns,
thatAttrNames) len(P[0]) must equal len(P[1]).
In addition, you can add extra tables and arbitrary sql to the
where clause, as you can with getSome(), including order,
limit and offset, using the "extraTables", "order", "limit"
and "offset" keyword arguments, using SQLOperators or
(sql-string, bind values) as positional arguments, and by
specifying columns by keyword argument.
"""
extraTables = extra.pop('extraTables', None)
if extraTables:
extraTables = list(_tupleize(extraTables))
order = extra.pop('order', None)
limit = extra.pop('limit', None)
offset = extra.pop('offset', None)
conn = self.getDBI()
wheresql, wherevals = self._processWhere(conn, whereArgs, extra)
sql, vals = self._joinTableSQL(conn, thisAttrNames, pivotTable,
thisSideColumns, thatSideColumns,
thatObject, thatAttrNames, extraTables,
wheresql, wherevals, order, limit,
offset)
results = conn.execute(sql, vals)
if results:
return map(thatObject, results)
return []