ORDER BY, HAVING, GROUP BY and WHERE clauses on select

[dbsrgits/SQL-Abstract-2.0-ish.git] / t / 201_select.t

diff --git a/t/201_select.t b/t/201_select.t
index 61b57d0..b20dc46 100644 (file)
--- a/t/201_select.t
+++ b/t/201_select.t
@@ -1,49 +1,90 @@
 use strict;
 use warnings;
 
-use Test::More tests => 3;
+use Test::More tests => 5;
 use Test::Differences;
 
 use_ok('SQL::Abstract') or BAIL_OUT( "$@" );
 
 my $sqla = SQL::Abstract->create(1);
 
+my $foo_as_me = {
+  -type => 'alias', 
+  ident => {-type => 'name', args => [qw/foo/]}, 
+  as => 'me'
+};
+my $me_foo_id = { -type => 'name', args => [qw/me foo_id/] };
+
 is $sqla->dispatch(
   { -type => 'select',
-    tablespec => {-type => 'alias', ident => {-type => 'name', args => [qw/foo/]}, as => 'me' },
+    tablespec => $foo_as_me,
     columns => [
       { -type => 'name', args => [qw/me id/] },
-      { -type => 'alias', ident => { -type => 'name', args => [qw/me foo_id/] }, as => 'foo' },
+      { -type => 'alias', ident => $me_foo_id, as => 'foo' },
     ]
   }
 ), "SELECT me.id, me.foo_id AS foo FROM foo AS me",
    "simple select clause";
 
-__END__
 is $sqla->dispatch(
   { -type => 'select',
-    tablespec => {-type => 'alias', ident => {-type => 'name', args => [qw/foo/]}, as => 'me' },
     columns => [
       { -type => 'name', args => [qw/me id/] },
-      { -type => 'alias', ident => { -type => 'name', args => [qw/me foo_id/] }, as => 'foo' },
+      { -type => 'alias', ident => $me_foo_id, as => 'foo' },
       { -type => 'name', args => [qw/bar name/] },
-    ]
+    ],
+    tablespec => {
+      -type => 'join',
+      lhs => $foo_as_me,
+      rhs => {-type => 'name', args => [qw/bar/] },
+      on => {
+        -type => 'expr',
+        op => '==',
+        args => [
+          {-type => 'name', args => [qw/bar id/]}, 
+          {-type => 'name', args => [qw/me bar_id/]}
+        ],
+      }
+    },
   }
 
 
+), "SELECT me.id, me.foo_id AS foo, bar.name FROM foo AS me JOIN bar ON (bar.id = me.bar_id)", 
+   "select with join clause";
+
+
+is $sqla->dispatch(
   { -type => 'select',
-    from => [-alias => [-name => 'foo'] => 'me' ],
-    columns => [ -list => 
-        [ -name => qw/me id/ ],
-        [ -alias => [ -name => qw/me foo_id/ ], 'foo' ],
-        [ -name => qw/bar name/ ],
+    columns => [
+      { -type => 'name', args => [qw/me */] },
     ],
-    join => {
-      tablespec => [-name => qw/bar/],
-      on => [ '==', [-name => qw/bar id/], [ -name => qw/me bar_id/ ] ],
+    tablespec => $foo_as_me,
+    where => {
+      -type => 'expr',
+      op => '==',
+      args => [
+        {-type => 'name', args => [qw/me id/]},
+        {-type => 'value', value => 1 },
+      ]
     }
   }
-), "SELECT me.id, me.foo_id AS foo, bar.name FROM foo AS me JOIN bar ON (bar.id = me.bar_id)", 
-   "select with join clause";
 
 
+), "SELECT me.* FROM foo AS me WHERE me.id = ?",
+   "select with where";
+
+
+is $sqla->dispatch(
+  { -type => 'select',
+    tablespec => $foo_as_me,
+    columns => [
+      { -type => 'name', args => [qw/me id/] },
+      { -type => 'alias', ident => $me_foo_id, as => 'foo' },
+    ],
+    order_by => [
+      { -type => 'ordering', expr => { -type => 'name', args => [qw/me name/] }, direction => 'desc' },
+      $me_foo_id,
+    ]
+  }
+), "SELECT me.id, me.foo_id AS foo FROM foo AS me ORDER BY me.name DESC, me.foo_id",
+   "select clause with order by";