[Silas] - 2005-12-8 - The objective of this page is to discuss alghorithms to help in "query synthesys" (I don't know if "synthesys" and "synthesize" are the best word to discuss a stuff like that. If not, please, rename or remove this page).

Suppose you have a list with redundat data:

  {Owner Car}

  {Silas Ferrari}
  {Silas Porche}
  {John  Honda}
  {Smith Renaut}
  {Smith Ford}

See this list has redundant data. If I want to show it in a report, it probably be confuse to the viewer. So it's a good idea to take off this junk data:

  {Owner Car}

  {Silas Ferrari}
  {{}    Porche}
  {John  Honda}
  {Smith Renaut}
  {{}    Ford}

This kind of information is common from queries with JOINs. I developed a cool proc which takes off this junk data. It is in version 0.1 yet, because I'm still trying it... not 100% stable. Needs some fixes. I'll update it as I note errors. For now, it conforms to my purposes:

  proc synthesize {List reference Fields} {
    # synthesize: Receive a list of lists and synthesizes a query with rows 
    # returned by a JOIN. Returns the synthesized list
    #
    # List: List to be synthesized
    # reference: fields to be compared
    # Fields: Fields which will be used in synthesize. It's a list of indexes
    # version 0.1 - 2005-12-06
    # by [Silas] Justiniano - silasdb at gmail dot com

    set i 0

    while {$i < [llength $List]} {
      for {set k 0} {$k < [llength [lindex $List 0]]} {incr k} {
	set aux($k) {}
      }

      set j [expr $i+1]
      while {[lindex $List $i $reference] eq [lindex $List $j $reference]} {
	for {set k 0} {$k < [llength [lindex $List 0]]} {incr k} {
	  if {[lsearch $aux($k) [lindex $List $j $k]] == -1
	   && [lsearch $Fields $k] != -1} {
	    lappend aux($k) [lindex $List $j $k]
	  }
	}
	incr j
	if {[expr $j-1] == [llength $List]} {return $List}
      }

      set max 0
      foreach valor [array names aux] {
	if {[lindex $aux($valor) 0] == [lindex [lindex $List $i] $valor]} {
	  set aux($valor) [lreplace aux($valor) 0 0]
	}
	if {$max < [llength $aux($valor)]} {
	  set max [llength $aux($valor)]
	}
      }

      for {set k $i} {$k <= [expr $max+$i]} {incr k} {
	for {set l 0} {$l < [llength [lindex $List 0]]} {incr l} {
	  if {$k == $i} {
	    lappend Itens [lindex [lindex $List $i] $l]
	  } else {
	    if {[lsearch $Fields $l] == -1} {
	      lappend Itens {}
	    } else {
	      lappend Itens [lindex $aux($l) 0]
	      set aux($l) [lreplace $aux($l) 0 0]
	    }
	  }
	}
	set List [linsert $List $k $Itens]
	unset -nocomplain Itens
      }

      set List [lreplace $List [expr $i+$max+1] [expr $j+$max]]

      if {$max == 0 && $i == [llength $List]-1} break
      incr i [expr $max+1]
    }

    return $List
  }

#Testing...:

  set A {
    {Silas Ferrari}
    {Silas Porche}
    {John  Honda}
    {Smith Renaut}
    {Smith Ford}
  }
  puts [synthesize $A 0 1]

Notes the first argument is the list, the second is the "field" that you will use as reference to synthesize and the last is a list of indexes to be deleted when found more than one (is this explanation fine? :) ).

As I said, the last argument can receive a list of indexes. So in the following list:

  {Owner Car     Color}

  {Silas Porche  Blue}
  {Silas Porche  Black}
  {Silas Ferrari Red}
  {John  Honda   Blue}
  {Smith Renaut  White}
  {Smith Renaut  Gray}
  {Smith Ford    Green}

Each owner has a car, some have more than one, some of them are not different, but have different color. A synthesized list would be:

  {Owner Car     Color}

  {Silas Porche  Blue}
  {{}    {}      Black}
  {{}    Ferrari Red}
  {John  Honda   Blue}
  {Smith Renaut  White}
  {{}    {}      Gray}
  {{}    Ford    Green}

I reached it using:
  puts [synthesize $A 0 {1 2}]

Please, help to improve this proc.
----
[RS] experimented with this (which has no column-width formatting):
 proc synthesize llist {
   set last {}
   set ress {}
   foreach list $llist {
	set reslist {}
        foreach e $list f $last {
           lappend reslist [expr {$e eq $f? {}: $e}]
        }
        lappend ress $reslist
        set last $list
   }
   set ress
 }
 set data {
  {Owner Car     Color}

  {Silas Porche  Blue}
  {Silas Porche  Black}
  {Silas Ferrari Red}
  {John  Honda   Blue}
  {Smith Renaut  White}
  {Smith Renaut  Gray}
  {Smith Ford    Green}
 }
 % join [synthesize $data] \n
 Owner Car Color
 Silas Porche Blue
 {} {} Black
 {} Ferrari Red
 John Honda Blue
 Smith Renaut White
 {} {} Gray
 {} Ford Green


----

[Category Algorithm]