From 7acbe9233bdad08182042cf609f1a92bfb5f32c3 Mon Sep 17 00:00:00 2001
From: Boris Kolpackov <boris@codesynthesis.com>
Date: Fri, 6 Feb 2015 08:57:30 +0200
Subject: Implement join types support in views

---
 view/driver.cxx   | 22 ++++++++++++++++++++++
 view/employee.hxx | 18 ++++++++++++++++++
 2 files changed, 40 insertions(+)

diff --git a/view/driver.cxx b/view/driver.cxx
index 74d6eed..d975cc4 100644
--- a/view/driver.cxx
+++ b/view/driver.cxx
@@ -314,6 +314,28 @@ main (int argc, char* argv[])
       t.commit ();
     }
 
+    // Get the list of employers that have any employees.
+    //
+    {
+      typedef odb::result<employer_with_employees> result;
+
+      shared_ptr<employer> es (new employer (3, "Empty Shell LLC"));
+
+      transaction t (db->begin ());
+      db->persist (es);
+
+      result r (db->query<employer_with_employees> ());
+
+      cout << "Employers with employees" << endl;
+
+      for (result::iterator i (r.begin ()); i != r.end (); ++i)
+        cout << "  " << i->e->name () << endl;
+
+      cout << endl;
+
+      db->erase (es);
+      t.commit ();
+    }
 
     // Get the list of employees that have accumulated vacation days.
     //
diff --git a/view/employee.hxx b/view/employee.hxx
index 87f0bce..4e1b745 100644
--- a/view/employee.hxx
+++ b/view/employee.hxx
@@ -253,6 +253,24 @@ struct employee_country_objects
   shared_ptr<country> nat;
 };
 
+// An example of using inner join type. Here we want to find employers
+// that have any employees. If we were to use the default left join type,
+// then we would have gotten all the employers, regardless of whether
+// they have any employees. By using the inner join, we make sure that
+// only matching employers are returned.
+//
+// It is also likely that there will be more than one employee for any
+// particular employer which will lead to duplicate employer records
+// being returned. To avoid this we use the 'distinct' result modifier.
+//
+// Try to change the join type or remove 'distinct' to observe the
+// change in behavior.
+//
+#pragma db view object(employer) object(employee inner) query(distinct)
+struct employer_with_employees
+{
+  shared_ptr<employer> e;
+};
 
 // An example of a native view that provides a complete query and is based
 // on an ad-hoc table. This view allows us to load the employee vacation
-- 
cgit v1.1