view release on metacpan or  search on metacpan

t/02searches_joins.t  view on Meta::CPAN

    is_deeply( $users_obj, $clean_obj, 'after CleanSlate looks like new object');
    ok( !$users_obj->_isJoined, "new object isn't joined");
    my $alias = $users_obj->NewAlias( 'UsersToGroups' );
    ok( $alias, "new alias" );
    is($users_obj->Join(
            TYPE   => 'LEFT',
            FIELD1 => 'id',
            ALIAS2 => $alias,
            FIELD2 => 'UserId' ),
        $alias, "joined table"
    );
    $users_obj->Limit( ALIAS => $alias, FIELD => 'id', OPERATOR => 'IS', VALUE => 'NULL' );
    ok( $users_obj->BuildSelectQuery =~ /LEFT JOIN/, 'LJ is not optimized away');
    is( $users_obj->Count, 1, "user is not member of any group" );
}

diag "main <- alias <- join" if $ENV{'TEST_VERBOSE'};
{
    # The join depends on the alias, we should build joins with correct order.
    $users_obj->CleanSlate;
    is_deeply( $users_obj, $clean_obj, 'after CleanSlate looks like new object');
    ok( !$users_obj->_isJoined, "new object isn't joined");
    my $alias = $users_obj->NewAlias( 'UsersToGroups' );
    ok( $alias, "new alias" );
    ok( $users_obj->_isJoined, "object with aliases is joined");
    $users_obj->Limit( FIELD => 'id', VALUE => "$alias.UserId", QUOTEVALUE => 0);
    ok( my $groups_alias = $users_obj->Join(
            ALIAS1 => $alias,
            FIELD1 => 'GroupId',
            TABLE2 => 'Groups',
            FIELD2 => 'id',
        ),
        "joined table"
    );
    $users_obj->Limit( ALIAS => $groups_alias, FIELD => 'Name', VALUE => 'Developers' );
    is( $users_obj->Count, 3, "three members" );
}

diag "main <- alias <- join into main" if $ENV{'TEST_VERBOSE'};
{
    # DBs' parsers don't like: FROM X, Y JOIN C ON C.f = X.f
    $users_obj->CleanSlate;
    is_deeply( $users_obj, $clean_obj, 'after CleanSlate looks like new object');
    ok( !$users_obj->_isJoined, "new object isn't joined");

    ok( my $groups_alias = $users_obj->NewAlias( 'Groups' ), "new alias" );
    ok( my $g2u_alias = $users_obj->Join(
            ALIAS1 => 'main',
            FIELD1 => 'id',
            TABLE2 => 'UsersToGroups',
            FIELD2 => 'UserId',
        ),
        "joined table"
    );
    $users_obj->Limit( ALIAS => $g2u_alias, FIELD => 'GroupId', VALUE => "$groups_alias.id", QUOTEVALUE => 0);
    $users_obj->Limit( ALIAS => $groups_alias, FIELD => 'Name', VALUE => 'Developers' );
    #diag $users_obj->BuildSelectQuery;
    is( $users_obj->Count, 3, "three members" );
}

diag "cascaded LEFT JOIN optimization" if $ENV{'TEST_VERBOSE'}; 
{
    $users_obj->CleanSlate;
    is_deeply( $users_obj, $clean_obj, 'after CleanSlate looks like new object');
    ok( !$users_obj->_isJoined, "new object isn't joined");
    my $alias = $users_obj->Join(
        TYPE   => 'LEFT',
        FIELD1 => 'id',
        TABLE2 => 'UsersToGroups',
        FIELD2 => 'UserId'
    );
    ok( $alias, "Join returns alias" );
    $alias = $users_obj->Join(
        TYPE   => 'LEFT',
        ALIAS1 => $alias,
        FIELD1 => 'GroupId',
        TABLE2 => 'Groups',
        FIELD2 => 'id'
    );
    $users_obj->Limit( ALIAS => $alias, FIELD => 'id', OPERATOR => 'IS NOT', VALUE => 'NULL' );
    ok( $users_obj->BuildSelectQuery !~ /LEFT JOIN/, 'both LJs are optimized away');
    is( $users_obj->Count, 3, "users whos is memebers of at least one group" );
}

diag "LEFT JOIN optimization and OR clause" if $ENV{'TEST_VERBOSE'}; 
{
    $users_obj->CleanSlate;
    is_deeply( $users_obj, $clean_obj, 'after CleanSlate looks like new object');
    ok( !$users_obj->_isJoined, "new object isn't joined");
    my $alias = $users_obj->Join(
        TYPE   => 'LEFT',
        FIELD1 => 'id',
        TABLE2 => 'UsersToGroups',
        FIELD2 => 'UserId'
    );
    $users_obj->_OpenParen('my_clause');
    $users_obj->Limit(
        SUBCLAUSE => 'my_clause',
        ALIAS => $alias,
        FIELD => 'id',
        OPERATOR => 'IS NOT',
        VALUE => 'NULL'
    );
    $users_obj->Limit(
        SUBCLAUSE => 'my_clause',
        ENTRY_AGGREGATOR => 'OR',
        FIELD => 'id',
        VALUE => 3
    );
    $users_obj->_CloseParen('my_clause');
    ok( $users_obj->BuildSelectQuery =~ /LEFT JOIN/, 'LJ is not optimized away');
    is( $users_obj->Count, 4, "all users" );
}

diag "DISTINCT in Join" if $ENV{'TEST_VERBOSE'};
{
    $users_obj->CleanSlate;
    is_deeply( $users_obj, $clean_obj, 'after CleanSlate looks like new object');
    ok( !$users_obj->_isJoined, "new object isn't joined");
    my $alias = $users_obj->Join(
        FIELD1 => 'id',