Project

General

Profile

« Previous | Next » 

Revision 2187

sql.py: mk_select(): joins: Fixed bug where join_not_equal did not do what it was designed for, which is filtering out matches of the join condition (before the bug fix, it effectively did a cross join with matching rows excluded, causing duplication of rows). Renamed join_not_equal to filter_out to reflect its intended use. Support table-scoped column names in the WHERE conds list.

View differences:

lib/sql.py
365 365

  
366 366
join_using = object() # tells mk_select() to join the column with USING
367 367

  
368
join_not_equal = object() # tells mk_select() to join the column with !=
368
filter_out = object() # tells mk_select() to filter out rows that match the join
369 369

  
370 370
def mk_select(db, tables, fields=None, conds=None, limit=None, start=None,
371 371
    order_by=order_by_pkey, table_is_esc=False):
372 372
    '''
373 373
    @param tables The single table to select from, or a list of tables to join
374
        together: [table0, (table1, dict(right_col=left_col, ...)), ...]
374
        together: [table0, (table1, joins), ...]
375
        
376
        joins has the format: dict(right_col=left_col, ...)
377
        * if left_col is join_using, left_col is set to right_col
378
        * if left_col is filter_out, the tables are LEFT JOINed together and the
379
          query is filtered by `right_col IS NULL` (indicating no match)
375 380
    @param fields Use None to select all fields in the table
376 381
    @param table_is_esc Whether the table name has already been escaped
377 382
    @return tuple(query, params)
......
410 415
    def cond(entry):
411 416
        '''Parses conditions'''
412 417
        col, value = entry
413
        cond_ = esc_name_(col)+' '
418
        cond_ = parse_col(col)+' '
414 419
        if value == None: cond_ += 'IS'
415 420
        else: cond_ += '='
416 421
        cond_ += ' %s'
......
425 430
    left_table = table0
426 431
    for table, joins in tables:
427 432
        if not table_is_esc: table = esc_name_(table)
428
        query += ' JOIN '+table
429 433
        
434
        left_join_ref = [False]
435
        
430 436
        def join(entry):
431 437
            '''Parses non-USING joins'''
432 438
            right_col, left_col = entry
433 439
            
434
            negative = False
435
            
436 440
            # Parse special values
437 441
            if left_col == None: left_col = (left_col,)
438 442
                # for None values, tuple is optional
439 443
            elif left_col == join_using: left_col = right_col
440
            elif left_col == join_not_equal:
444
            elif left_col == filter_out:
441 445
                left_col = right_col
442
                negative = True
446
                left_join_ref[0] = True
447
                conds[(table, right_col)] = None # filter query by no match
443 448
            
444 449
            # Create SQL
445 450
            right_col = table+'.'+esc_name_(right_col)
......
456 461
                sql_ += ('= '+left_col+' OR ('+right_col+' IS NULL AND '
457 462
                    +left_col+' IS NULL)')
458 463
            
459
            if negative: sql_ = 'NOT ('+sql_+')'
460
            
461 464
            return sql_
462 465
        
466
        # Create join condition and determine join type
463 467
        if reduce(operator.and_, (v == join_using for v in joins.itervalues())):
464 468
            # all cols w/ USING, so can use simpler USING syntax
465
            query += ' USING ('+(', '.join(joins.iterkeys()))+')'
466
        else: query += ' ON '+(' AND '.join(map(join, joins.iteritems())))
469
            join_cond = 'USING ('+(', '.join(joins.iterkeys()))+')'
470
        else: join_cond = 'ON '+(' AND '.join(map(join, joins.iteritems())))
467 471
        
472
        # Create join
473
        if left_join_ref[0]: query += ' LEFT'
474
        query += ' JOIN '+table+' '+join_cond
475
        
468 476
        left_table = table
469 477
    
470 478
    missing = True

Also available in: Unified diff