示例#1
0
    def _joinTableSQL(self,
                      conn,
                      thisAttrNames,
                      pivotTable,
                      thisSideColumns,
                      thatSideColumns,
                      thatObject,
                      thatAttrNames,
                      extraTables,
                      wheresql,
                      converter,
                      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 = []
        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)
            sql=' AND '.join(wheresql, sql)
        if filter(None, (order, limit, offset)):
            sql.append(' ')
            sql.append(conn.orderByString(order, limit, offset))                
        return ''.join(sql), vals
示例#2
0
    def _joinTableSQL(self,
                      conn,
                      thisAttrNames,
                      pivotTable,
                      thisSideColumns,
                      thatSideColumns,
                      thatObject,
                      thatAttrNames,
                      extraTables,
                      wheresql,
                      #wherevals,
                      converter,
                      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 = []
        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 filter(None, (order, limit, offset)):
            sql.append(conn.orderByString(order, limit, offset))                
        return ''.join(sql), vals
示例#3
0
    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()
        converter=conn.getConverter()
        wheresql, wherevals=self._processWhere(conn, whereArgs, extra, converter=converter)
        sql, vals = self._joinTableSQL(conn,
                                       thisAttrNames,
                                       pivotTable,
                                       thisSideColumns,
                                       thatSideColumns,
                                       thatObject,
                                       thatAttrNames,
                                       extraTables,
                                       wheresql,
                                       #wherevals,
                                       converter,
                                       order,
                                       limit,
                                       offset)
        results = conn.execute(sql, vals)
        if results and isinstance(results, (list, tuple)):
            return map(thatObject, results)
        return []
示例#4
0
    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()
        converter=conn.getConverter()
        wheresql, wherevals=self._processWhere(conn, whereArgs, extra, converter=converter)
        sql, vals = self._joinTableSQL(conn,
                                       thisAttrNames,
                                       pivotTable,
                                       thisSideColumns,
                                       thatSideColumns,
                                       thatObject,
                                       thatAttrNames,
                                       extraTables,
                                       wheresql,
                                       converter,
                                       order,
                                       limit,
                                       offset)
        results = conn.execute(sql, vals)
        if results and isinstance(results, (list, tuple)):
            return map(thatObject, results)
        return []