xo::db::Class instproc instance_select_query (public)

 <instance of xo::db::Class> instance_select_query \
    [ -select_attributes select_attributes ] [ -orderby orderby ] \
    [ -where_clause where_clause ] [ -from_clause from_clause ] \
    [ -count on|off ] [ -page_size page_size ] \
    [ -page_number page_number ]

Defined in /var/www/openacs.org/packages/xotcl-core/tcl/05-db-procs.tcl

Returns the SQL-query to select ACS Objects of the object_type of the class.

Switches:

-select_attributes (optional)

attributes for the SQL query to be retrieved. if no attributes are specified, all attributes are retrieved.

-orderby (optional)

for ordering the solution set

-where_clause (optional)

clause for restricting the answer set

-from_clause (optional)

-count (optional, boolean, defaults to "false")

return the query for counting the solutions

-page_size (optional, defaults to "20")

-page_number (optional)

Returns:

SQL query

Testcases:

No testcase defined.

Source code:

set tables [list]
set id_column [:id_column]

if {$count} {
  set select_attributes "count(*)"
  set orderby ""         ;# no need to order when we count
  set page_number  ""    ;# no pagination when count is used
}

set all_attributes [expr {$select_attributes eq ""}]
set join_expressions [list]
foreach cl [list [self] {*}[:info heritage]] {
  #if {$cl eq "::xo::db::Object"} break
  if {$cl eq "::xotcl::Object"} break
  set tn [$cl table_name]

  if {$tn  ne ""} {
    lappend tables $tn
    if {$all_attributes} {
      foreach {slot_name slot} [$cl array get db_slot] {
        # avoid duplicate output names
        set name [$slot name]
        if {![info exists names($name)]} {
          lappend select_attributes [$slot attribute_reference $tn]
        }
        set names($name) 1
      }
    }
    if {$cl ne [self]} {
      lappend join_expressions "$tn.[$cl id_column] = [:table_name].$id_column"
    }
  }
}

if {$page_number ne ""} {
  set limit $page_size
  set offset [expr {$page_size*($page_number-1)}]
} else {
  set limit ""
  set offset ""
}
set sql [::xo::dc select  -vars   [join $select_attributes ,]  -from  "[join $tables ,] $from_clause"  -where  [string trim "[join $join_expressions { and }] $where_clause"]  -orderby $orderby  -limit $limit -offset $offset]
return $sql

XQL Not present:

Generic, PostgreSQL, Oracle