| 333 | | sub unmake_foos : Test(teardown) { |
|---|
| 334 | | MT::Test->reset_table_for('Foo'); |
|---|
| | 379 | sub alias : Tests(2) { |
|---|
| | 380 | my $self = shift; |
|---|
| | 381 | $self->make_pc_data(); |
|---|
| | 382 | |
|---|
| | 383 | my $vista = Foo->load(3); # not a search |
|---|
| | 384 | |
|---|
| | 385 | # select * from foo, bar bar1, bar bar2 |
|---|
| | 386 | # where bar1.bar_foo_id = foo_id |
|---|
| | 387 | # and bar2.bar_foo_id = bar1.bar_foo_id |
|---|
| | 388 | # and bar1.status = 2 |
|---|
| | 389 | # and bar2.status = 3 |
|---|
| | 390 | my @a_foos = Foo->load( |
|---|
| | 391 | undef, |
|---|
| | 392 | { join => [ 'Bar', undef, { foo_id => \'= foo_id', status => 2 }, |
|---|
| | 393 | { join => [ 'Bar', undef, { foo_id => \'= bar1.bar_foo_id', status => 3 }, |
|---|
| | 394 | { alias => 'bar2' } ], |
|---|
| | 395 | alias => 'bar1' |
|---|
| | 396 | } |
|---|
| | 397 | ], |
|---|
| | 398 | sort => 'created_on', direction => 'descend', |
|---|
| | 399 | } |
|---|
| | 400 | ); |
|---|
| | 401 | are_objects(\@a_foos, [ $vista ], 'Has Bars with status=2 and status=3 (alias)'); |
|---|
| | 402 | |
|---|
| | 403 | @a_foos = Foo->load( |
|---|
| | 404 | undef, |
|---|
| | 405 | { join => [ 'Bar', undef, { foo_id => \'= foo_id', status => 2 }, |
|---|
| | 406 | { join => [ 'Bar', undef, { foo_id => \'= bar1.bar_foo_id', status => 0 }, |
|---|
| | 407 | { alias => 'bar2' } ], |
|---|
| | 408 | alias => 'bar1' |
|---|
| | 409 | } |
|---|
| | 410 | ], |
|---|
| | 411 | sort => 'created_on', direction => 'descend', |
|---|
| | 412 | } |
|---|
| | 413 | ); |
|---|
| | 414 | is_deeply(\@a_foos, [], 'No Foo has Bars with status=2 and status=0 (alias)'); |
|---|
| | 415 | } |
|---|
| | 416 | |
|---|
| | 417 | sub conjunctions : Tests(3) { |
|---|
| | 418 | my $self = shift; |
|---|
| | 419 | $self->make_pc_data(); |
|---|
| | 420 | |
|---|
| | 421 | my $count = Foo->count( [{status => 10}, -or => {name => 'Apple'}] ); |
|---|
| | 422 | # ==> select count(*) from mt_foo where foo_status = 10 or foo_name = 'Apple' |
|---|
| | 423 | is($count, 3, '-or count'); |
|---|
| | 424 | |
|---|
| | 425 | $count = Foo->count( [ { status => { '<=' => 20 }, name => 'Apple' }, -and_not => { status => 11 } ] ); |
|---|
| | 426 | # ==> select count(*) from mt_foo where (foo_status <= 20 and foo_name = 'Apple') and not (foo_status = 11) |
|---|
| | 427 | is($count, 1, '-and_not count'); |
|---|
| | 428 | |
|---|
| | 429 | $count = Foo->count( [ |
|---|
| | 430 | { status => 10 }, |
|---|
| | 431 | -or => { name => 'Apple' }, |
|---|
| | 432 | -or => { name => { like => '%nux' } }, |
|---|
| | 433 | ] ); |
|---|
| | 434 | # ==> select count(*) from mt_foo where (foo_status = 10) or (foo_name = 'Apple') or (foo_name like '%nux') |
|---|
| | 435 | # (selects Apple+MacBook, Apple+iBook, Microsoft+XP, Linux+Ubuntu) |
|---|
| | 436 | is($count, 4, '-or count, 3 clauses'); |
|---|
| | 437 | } |
|---|
| | 438 | |
|---|
| | 439 | sub clean_db : Test(teardown) { |
|---|
| | 440 | MT::Test->reset_table_for(qw( Foo Bar )); |
|---|
| 806 | | # -or |
|---|
| 807 | | my $newdata = Foo->new; |
|---|
| 808 | | $newdata->status(11); |
|---|
| 809 | | $newdata->name('Apple'); |
|---|
| 810 | | $newdata->text('MacBook'); |
|---|
| 811 | | $newdata->save; |
|---|
| 812 | | $newdata = Foo->new; |
|---|
| 813 | | $newdata->status(12); |
|---|
| 814 | | $newdata->name('Linux'); |
|---|
| 815 | | $newdata->text('Ubuntu'); |
|---|
| 816 | | $newdata->save; |
|---|
| 817 | | $newdata = Foo->new; |
|---|
| 818 | | $newdata->status(13); |
|---|
| 819 | | $newdata->name('Microsoft'); |
|---|
| 820 | | $newdata->text('Vista'); |
|---|
| 821 | | $newdata->save; |
|---|
| 822 | | $newdata = Foo->new; |
|---|
| 823 | | $newdata->status(10); |
|---|
| 824 | | $newdata->name('Microsoft'); |
|---|
| 825 | | $newdata->text('XP'); |
|---|
| 826 | | $newdata->save; |
|---|
| 827 | | $newdata = Foo->new; |
|---|
| 828 | | $newdata->status(10); |
|---|
| 829 | | $newdata->name('Apple'); |
|---|
| 830 | | $newdata->text('iBook'); |
|---|
| 831 | | $newdata->save; |
|---|
| 832 | | |
|---|
| 833 | | my $count = Foo->count( [{status => 10}, -or => {name => 'Apple'}] ); |
|---|
| 834 | | # ==> select count(*) from mt_foo where foo_status = 10 or foo_name = 'Apple' |
|---|
| 835 | | is($count, 3, '-or count'); |
|---|
| 836 | | |
|---|
| 837 | | $count = Foo->count( [ { status => { '<=' => 20 }, name => 'Apple' }, -and_not => { status => 11 } ] ); |
|---|
| 838 | | # ==> select count(*) from mt_foo where (foo_status <= 20 and foo_name = 'Apple') and not (foo_status = 11) |
|---|
| 839 | | is($count, 1, '-and_not count'); |
|---|
| 840 | | |
|---|
| 841 | | $count = Foo->count( [ |
|---|
| 842 | | { status => 10 }, |
|---|
| 843 | | -or => { name => 'Apple' }, |
|---|
| 844 | | -or => { name => { like => '%nux' } }, |
|---|
| 845 | | ] ); |
|---|
| 846 | | # ==> select count(*) from mt_foo where (foo_status = 10) or (foo_name = 'Apple') or (foo_name like '%nux') |
|---|
| 847 | | # (selects Apple+MacBook, Apple+iBook, Microsoft+XP, Linux+Ubuntu) |
|---|
| 848 | | is($count, 4, '-or count, 3 clauses'); |
|---|
| 849 | | |
|---|
| 850 | | # alias support |
|---|
| 851 | | my $vista = Foo->load({name=>'Microsoft', status=>13}); |
|---|
| 852 | | my $newbar = Bar->new; |
|---|
| 853 | | $newbar->foo_id($vista->id); |
|---|
| 854 | | $newbar->name('Silverlight'); |
|---|
| 855 | | $newbar->status(2); |
|---|
| 856 | | $newbar->save; |
|---|
| 857 | | sleep(3); |
|---|
| 858 | | $newbar = Bar->new; |
|---|
| 859 | | $newbar->foo_id($vista->id); |
|---|
| 860 | | $newbar->name('IronPython'); |
|---|
| 861 | | $newbar->status(3); |
|---|
| 862 | | $newbar->save; |
|---|
| 863 | | sleep(3); |
|---|
| 864 | | my $mb = Foo->load({name=>'Apple', status=>11}); |
|---|
| 865 | | $newbar = Bar->new; |
|---|
| 866 | | $newbar->foo_id($mb->id); |
|---|
| 867 | | $newbar->name('IronRuby'); |
|---|
| 868 | | $newbar->status(0); |
|---|
| 869 | | $newbar->save; |
|---|
| 870 | | |
|---|
| 871 | | # select * from foo, bar bar1, bar bar2 |
|---|
| 872 | | # where bar1.bar_foo_id = foo_id |
|---|
| 873 | | # and bar2.bar_foo_id = bar1.bar_foo_id |
|---|
| 874 | | # and bar1.status = 2 |
|---|
| 875 | | # and bar2.status = 3 |
|---|
| 876 | | my @a_foos = Foo->load( |
|---|
| 877 | | undef, |
|---|
| 878 | | { join => [ 'Bar', undef, { foo_id => \'= foo_id', status => 2 }, |
|---|
| 879 | | { join => [ 'Bar', undef, { foo_id => \'= bar1.bar_foo_id', status => 3 }, |
|---|
| 880 | | { alias => 'bar2' } ], |
|---|
| 881 | | alias => 'bar1' |
|---|
| 882 | | } |
|---|
| 883 | | ], |
|---|
| 884 | | sort => 'created_on', direction => 'descend', |
|---|
| 885 | | } |
|---|
| 886 | | ); |
|---|
| 887 | | is(scalar(@a_foos), 1, 'join the same table using alias 1'); |
|---|
| 888 | | is($a_foos[0]->id, $vista->id, 'join the same table using alias 2'); |
|---|
| 889 | | |
|---|
| 890 | | @a_foos = Foo->load( |
|---|
| 891 | | undef, |
|---|
| 892 | | { join => [ 'Bar', undef, { foo_id => \'= foo_id', status => 2 }, |
|---|
| 893 | | { join => [ 'Bar', undef, { foo_id => \'= bar1.bar_foo_id', status => 0 }, |
|---|
| 894 | | { alias => 'bar2' } ], |
|---|
| 895 | | alias => 'bar1' |
|---|
| 896 | | } |
|---|
| 897 | | ], |
|---|
| 898 | | sort => 'created_on', direction => 'descend', |
|---|
| 899 | | } |
|---|
| 900 | | ); |
|---|
| 901 | | is(scalar(@a_foos), 0, 'join the same table using alias 3'); |
|---|
| 902 | | |
|---|
| 903 | | |
|---|