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

 <instance of xo::db::CrClass> instance_select_query \
    [ -select_attributes select_attributes ] [ -orderby orderby ] \
    [ -where_clause where_clause ] [ -from_clause from_clause ] \
    [ -with_subtypes on|off ] [ -with_children on|off ] \
    [ -publish_status publish_status ] [ -count on|off ] \
    [ -folder_id folder_id ] [ -parent_id parent_id ] \
    [ -page_size page_size ] [ -page_number page_number ] \
    [ -base_table base_table ]

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

returns the SQL-query to select the CrItems of the specified object_type

Switches:

-select_attributes (optional)

attributes for the SQL query to be retrieved, in addition to item_id, name, publish_status, object_type, and package_id which are always returned

-orderby (optional)

for ordering the solution set

-where_clause (optional)

clause for restricting the answer set

-from_clause (optional)

-with_subtypes (optional, boolean, defaults to "true")

return subtypes as well

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

return immediate child objects of all objects as well

-publish_status (optional)

one of 'live', 'ready', or 'production'

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

return the query for counting the solutions

-folder_id (optional)

parent_id

-parent_id (optional)

-page_size (optional, defaults to "20")

-page_number (optional)

-base_table (optional, defaults to "cr_revisions")

typically automatic view, must contain title and revision_id

Returns:

SQL query

Testcases:

xowiki_test_cases

Source code:

if {![info exists folder_id]} {set folder_id ${:folder_id}}
if {![info exists parent_id]} {set parent_id $folder_id}

if {$base_table eq "cr_revisions"} {
  set attributes [list ci.item_id ci.name ci.publish_status acs_objects.object_type acs_objects.package_id]
} else {
  set attributes [list bt.item_id ci.name ci.publish_status bt.object_type "bt.object_package_id as package_id"]
}
foreach a $select_attributes {
  if {$a eq "title"} {set a bt.title}
  lappend attributes $a
}
set type_selection_clause [:type_selection_clause -base_table $base_table -with_subtypes $with_subtypes]
# :log "type_selection_clause -with_subtypes $with_subtypes returns $type_selection_clause"
if {$count} {
  set attribute_selection "count(*)"
  set orderby ""      ;# no need to order when we count
  set page_number  ""      ;# no pagination when count is used
} else {
  set attribute_selection [join $attributes ,]
}

set cond [list]
if {$type_selection_clause ne ""} {lappend cond $type_selection_clause}
if {$where_clause ne ""}          {lappend cond $where_clause}
if {[info exists publish_status]} {lappend cond "ci.publish_status = :publish_status"}
if {$base_table eq "cr_revisions"} {
  lappend cond "acs_objects.object_id = bt.revision_id"
  set acs_objects_table "acs_objects, "
} else {
  lappend cond "ci.item_id = bt.item_id"
  set acs_objects_table ""
}
lappend cond "coalesce(ci.live_revision,ci.latest_revision) = bt.revision_id"
if {$parent_id ne ""} {
  if {$with_children} {
    append from_clause ", (select $parent_id as item_id from dual union  select item_id from cr_items where parent_id = $parent_id) children"
    lappend cond "ci.parent_id = children.item_id"
  } else {
    lappend cond "ci.parent_id = $parent_id"
  }
}

if {$page_number ne ""} {
  set limit $page_size
  set offset [expr {$page_size*($page_number-1)}]
} else {
  set limit ""
  set offset ""
}

if {!$count} {
  #
  # In case the query is not explicitly referring to a context_id,
  # return the context_id of the item. The problem are queries
  # using "*" in the attribute list, which should be deprecated.
  # Before that we should walk through the common call patterns of
  # this function to check, if this is feasible.
  #
  # This local hack was necessary to deal with a recent fix that
  # honors now correctly changes in the context_id. Before this
  # change, e.g. "get_all_children" was returning due to the
  # nature of the call the context_id of the revision (not of the
  # item), although it was returning items. A following bug-fix
  # actually triggered this change.
  # https://cvs.openacs.org/changelog/OpenACS?cs=oacs-5-10%3Agustafn%3A20210308161117
  #
  # TODO: remove me, when not necessary anymore.
  #
  if {[lsearch -glob $attributes *context_id*] == -1} {
    append attribute_selection {,(select context_id from acs_objects where object_id = ci.item_id)}
  }
}

set sql [::xo::dc select  -vars $attribute_selection  -from "$acs_objects_table cr_items ci, $base_table bt $from_clause"  -where [join $cond " and "]  -orderby $orderby  -limit $limit -offset $offset]
#:log "--sql=$sql"
return $sql

XQL Not present:

Generic, PostgreSQL, Oracle