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 e3b3aab..b20dc46 100644 (file)
--- a/t/201_select.t
+++ b/t/201_select.t
@@ -1,19 +1,26 @@
 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",
@@ -23,15 +30,13 @@ is $sqla->dispatch(
   { -type => 'select',
     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',
-      args => [ 
-        {-type => 'alias', ident => {-type => 'name', args => [qw/foo/]}, as => 'me' },
-        {-type => 'name', args => [qw/bar/] },
-      ],
+      lhs => $foo_as_me,
+      rhs => {-type => 'name', args => [qw/bar/] },
       on => {
         -type => 'expr',
         op => '==',
@@ -47,4 +52,39 @@ is $sqla->dispatch(
 ), "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";
 
-__END__
+
+is $sqla->dispatch(
+  { -type => 'select',
+    columns => [
+      { -type => 'name', args => [qw/me */] },
+    ],
+    tablespec => $foo_as_me,
+    where => {
+      -type => 'expr',
+      op => '==',
+      args => [
+        {-type => 'name', args => [qw/me id/]},
+        {-type => 'value', value => 1 },
+      ]
+    }
+  }
+
+
+), "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";