[glom] Related Records: Fix doubly-related records portals, such as a list of

[Murray Cumming <murrayc src gnome org>]

commit b53392f5baa6e1a089564f27afb57e1762c9a797
Author: Murray Cumming <murrayc murrayc com>
Date:   Fri Oct 2 12:56:41 2009 +0200

    Related Records: Fix doubly-related records portals, such as a list of
    an Artist's publishers (via their albums).
    
    * glom/libglom/utils.[h|cc]: build_sql_select_with_where_clause():
    Move some code into build_sql_select_fields_to_get(), so we can get the
    list of fields to get without the rest of the SQL.
    * glom/base_db.cc: set_found_set_where_clause_for_portal():
    Use build_sql_select_fields_to_get() to GROUP BY the full field list
    instead of just the primary key, as required by recent PostgreSQL,
    and probably by the SQL specification.
    We should investigate SELECT DISTINCT again to simplify this.

 ChangeLog             |   14 ++++++++++++++
 glom/base_db.cc       |   20 +++++++++++++++++---
 glom/libglom/utils.cc |   46 ++++++++++++++++++++++++++++++----------------
 glom/libglom/utils.h  |    4 +++-
 4 files changed, 64 insertions(+), 20 deletions(-)
---
diff --git a/ChangeLog b/ChangeLog
index b6b841a..cd45dac 100644
--- a/ChangeLog
+++ b/ChangeLog
@@ -1,3 +1,17 @@
+2009-10-02  Murray Cumming  <murrayc murrayc com>
+
+	Related Records: Fix doubly-related records portals, such as a list of 
+	an Artist's publishers (via their albums).
+
+	* glom/libglom/utils.[h|cc]: build_sql_select_with_where_clause(): 
+	Move some code into build_sql_select_fields_to_get(), so we can get the 
+	list of fields to get without the rest of the SQL.
+	* glom/base_db.cc: set_found_set_where_clause_for_portal(): 
+	Use build_sql_select_fields_to_get() to GROUP BY the full field list 
+	instead of just the primary key, as required by recent PostgreSQL, 
+	and probably by the SQL specification.
+	We should investigate SELECT DISTINCT again to simplify this. 
+
 2009-10-02  Michael Hasselmann  <michaelh openismus com>
 
 	Import: Cleanup, added comments.
diff --git a/glom/base_db.cc b/glom/base_db.cc
index b9e2143..00af727 100644
--- a/glom/base_db.cc
+++ b/glom/base_db.cc
@@ -3473,10 +3473,24 @@ void Base_DB::set_found_set_where_clause_for_portal(FoundSet& found_set, const s
     //found_set.m_extra_join = uses_rel_temp->get_sql_join_alias_definition();
     found_set.m_extra_join = "LEFT OUTER JOIN \"" + relationship->get_to_table() + "\" AS \"" + uses_rel_temp->get_sql_join_alias_name() + "\" ON (\"" + uses_rel_temp->get_sql_join_alias_name() + "\".\"" + relationship_related->get_from_field() + "\" = \"" + relationship_related->get_to_table() + "\".\"" + relationship_related->get_to_field() + "\")";
 
+
     //Add an extra GROUP BY to ensure that we get no repeated records from the doubly-related table:
-    sharedptr<Field> to_table_primary_key = get_field_primary_key_for_table( relationship_related->get_to_table() );
-    if(to_table_primary_key)
-      found_set.m_extra_group_by = "GROUP BY \"" + found_set.m_table_name + "\".\"" + to_table_primary_key->get_name() + "\"";
+    LayoutGroup::type_list_items portal_items = portal->get_items();
+    Utils::type_vecConstLayoutFields fields;
+    for(LayoutGroup::type_list_items::iterator iter = portal_items.begin(); iter != portal_items.end(); ++iter)
+    {
+      sharedptr<LayoutItem_Field> item_field = sharedptr<LayoutItem_Field>::cast_dynamic(*iter);
+      if(item_field)
+        fields.push_back(item_field);
+    }
+
+    Glib::ustring sql_part_from;
+    Glib::ustring sql_part_leftouterjoin;
+    const Glib::ustring sql_part_fields = Utils::build_sql_select_fields_to_get(
+      found_set.m_table_name, fields, found_set.m_extra_join, 
+      found_set.m_sort_clause, sql_part_from, sql_part_leftouterjoin);
+    found_set.m_extra_group_by = "GROUP BY " + sql_part_fields;
+
 
     //Adjust the WHERE clause appropriately for the extra JOIN:
     where_clause_to_table_name = uses_rel_temp->get_sql_join_alias_name();
diff --git a/glom/libglom/utils.cc b/glom/libglom/utils.cc
index 48a0b99..f04c1cb 100644
--- a/glom/libglom/utils.cc
+++ b/glom/libglom/utils.cc
@@ -230,9 +230,12 @@ static void add_to_relationships_list(type_list_relationships& list_relationship
  
 }
 
-Glib::ustring Utils::build_sql_select_with_where_clause(const Glib::ustring& table_name, const type_vecConstLayoutFields& fieldsToGet, const Glib::ustring& where_clause, const Glib::ustring& extra_join, const type_sort_clause& sort_clause, const Glib::ustring& extra_group_by)
+
+Glib::ustring Utils::build_sql_select_fields_to_get(const Glib::ustring& table_name, const type_vecConstLayoutFields& fieldsToGet, const Glib::ustring& extra_join, const type_sort_clause& sort_clause, Glib::ustring& sql_part_from, Glib::ustring& sql_part_leftouterjoin)
 {
-  Glib::ustring result;
+  //Initialize output parameters:
+  sql_part_from = Glib::ustring();
+  sql_part_leftouterjoin = Glib::ustring();
 
   //Get all relationships used in the query:
   typedef std::list< sharedptr<const UsesRelationship> > type_list_relationships;
@@ -252,7 +255,6 @@ Glib::ustring Utils::build_sql_select_with_where_clause(const Glib::ustring& tab
 
 
   Glib::ustring sql_part_fields;
-  Glib::ustring sql_part_from;
 
   for(type_vecConstLayoutFields::const_iterator iter = fieldsToGet.begin(); iter != fieldsToGet.end(); ++iter)
   {
@@ -287,17 +289,12 @@ Glib::ustring Utils::build_sql_select_with_where_clause(const Glib::ustring& tab
 
   if(sql_part_fields.empty())
   {
-    std::cerr << "Utils::build_sql_select_with_where_clause(): sql_part_fields.empty(): fieldsToGet.size()=" << fieldsToGet.size() << std::endl;
-    return result;
-  }
-  else
-  {
-    result =  "SELECT " + sql_part_fields +
-      " FROM \"" + table_name + "\"";
+    std::cerr << "Utils::build_sql_select_fields_to_get(): sql_part_fields.empty(): fieldsToGet.size()=" << fieldsToGet.size() << std::endl;
+    return sql_part_fields;
   }
 
-  //LEFT OUTER JOIN will get the field values from the other tables, and give us our fields for this table even if there is no corresponding value in the other table.
-  Glib::ustring sql_part_leftouterjoin; 
+  //LEFT OUTER JOIN will get the field values from the other tables, 
+  //and give us our fields for this table even if there is no corresponding value in the other table.
   for(type_list_relationships::const_iterator iter = list_relationships.begin(); iter != list_relationships.end(); ++iter)
   {
     sharedptr<const UsesRelationship> uses_relationship = *iter;
@@ -316,17 +313,34 @@ Glib::ustring Utils::build_sql_select_with_where_clause(const Glib::ustring& tab
     }
   }
 
-  if(!extra_join.empty())
-  {
-    sql_part_leftouterjoin += (" " + extra_join + " ");
-  }
+  return sql_part_fields;
+}
+
+
+Glib::ustring Utils::build_sql_select_with_where_clause(const Glib::ustring& table_name, const type_vecConstLayoutFields& fieldsToGet, const Glib::ustring& where_clause, const Glib::ustring& extra_join, const type_sort_clause& sort_clause, const Glib::ustring& extra_group_by)
+{
+  //Get the list of fields to SELECT, plus the tables that they are selected FROM.
+  Glib::ustring sql_part_from;
+  Glib::ustring sql_part_leftouterjoin;
+  const Glib::ustring sql_part_fields = Utils::build_sql_select_fields_to_get(
+    table_name, fieldsToGet, extra_join, sort_clause, sql_part_from, sql_part_leftouterjoin);
+
+  //Build the whole SQL statement:
+  Glib::ustring result = 
+    "SELECT " + sql_part_fields +
+    " FROM \"" + table_name + "\"";
 
   if(!sql_part_from.empty())
     result += ("," + sql_part_from);
 
+  if(!extra_join.empty())
+    sql_part_leftouterjoin += (" " + extra_join + " ");
+
   if(!sql_part_leftouterjoin.empty())
     result += (" " + sql_part_leftouterjoin);
 
+
+  //Add the WHERE clause:
   if(!where_clause.empty())
     result += " WHERE " + where_clause;
 
diff --git a/glom/libglom/utils.h b/glom/libglom/utils.h
index 9c83e41..310ae15 100644
--- a/glom/libglom/utils.h
+++ b/glom/libglom/utils.h
@@ -44,7 +44,9 @@ Glib::ustring string_replace(const Glib::ustring& src, const Glib::ustring searc
 typedef std::vector< sharedptr<LayoutItem_Field> > type_vecLayoutFields;
 typedef std::vector< sharedptr<const LayoutItem_Field> > type_vecConstLayoutFields;
 
-  //TODO: Move this to its own file:
+//TODO: Move these to their own file:
+Glib::ustring build_sql_select_fields_to_get(const Glib::ustring& table_name, const type_vecConstLayoutFields& fieldsToGet, const Glib::ustring& extra_join, const type_sort_clause& sort_clause, Glib::ustring& sql_part_from, Glib::ustring& sql_part_leftouterjoin);
+
 Glib::ustring build_sql_select_with_where_clause(const Glib::ustring& table_name, const type_vecLayoutFields& fieldsToGet, const Glib::ustring& where_clause = Glib::ustring(), const Glib::ustring& extra_join = Glib::ustring(), const type_sort_clause& sort_clause = type_sort_clause(), const Glib::ustring& extra_group_by = Glib::ustring());
 Glib::ustring build_sql_select_with_where_clause(const Glib::ustring& table_name, const type_vecConstLayoutFields& fieldsToGet, const Glib::ustring& where_clause = Glib::ustring(), const Glib::ustring& extra_join = Glib::ustring(), const type_sort_clause& sort_clause = type_sort_clause(), const Glib::ustring& extra_group_by = Glib::ustring());