Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with HTTPS or Subversion.

Download ZIP

Loading…

PostgreSQL inheritance and "select * from ONLY {{table}}" #1953

Open
fabioaccetta opened this Issue · 6 comments

3 participants

Fabio Accetta Carsten Brandt Alexander Makarov
Fabio Accetta

How can I handle postgresql table inheritance and the use of "ONLY" postgresql instruction in Active Record?
I tried with this:

class Model extends CActiveRecord
{
   public function tableName()
   {
      return 'ONLY {{table_name}}';
   }
}

but it makes schema metadata queries to fail.

Carsten Brandt
Collaborator

This is currently not possible.
Is this only for performance, or is there a semantical change of the query, when you use ONLY?
Can you give an example about how to use it in a normal query?

Fabio Accetta

The ONLY sql instruction changes the results of the query; without ONLY, the previous title query is identical to

SELECT field_list FROM {{table_name}}*;

Note the * after table_name: it means "search results inside ALL tables in the inheritance chain". I need to select results ONLY from specific table instead, and not from all connected tables.

Thank you.

Fabio Accetta

The use of the * (without ONLY) is the default postgres behavior. It could be changed from config, but postgres manual says that it is not recommended...

Fabio Accetta

Quote from postgresql manual about sql_inheritance option (http://www.postgresql.org/docs/9.2/static/runtime-config-compatible.html#GUC-SQL-INHERITANCE):

This setting controls whether undecorated table references are considered
to include inheritance child tables.
The default is on, which means child tables are included
(thus, a * suffix is assumed by default).
If turned off, child tables are not included (thus, an ONLY prefix is assumed).
The SQL standard requires child tables to be included, so the off setting
is not spec-compliant, but it is provided for compatibility with PostgreSQL
releases prior to 7.1. See Section 5.8 for more information.

Turning sql_inheritance off is deprecated, because that behavior has been found
to be error-prone as well as contrary to SQL standard.
Discussions of inheritance behavior elsewhere in this manual
generally assume that it is on.
Fabio Accetta

If CDbCriteria would have a 'from' option, a possible workaround could be

$criteria = array('from' => 'ONLY {{table_name}}';
$models = Model::model()->findAll($criteria);

But CDbCriteria, as we know, has no 'from' property...

Fabio Accetta

A workaround until implemented: add a field "table_name" to each table in hierarchy, so values from ONLY one table can be fetched with:

$my_tn = 'my_table_name';
$criteria = array(
   'condition' => 'table_name = :tn',
   'params' => array(
      ':tn' => $my_tn,
   ),
);
$models = Model::model()->findAll($criteria);
Alexander Makarov samdark reopened this
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.