extended get_data

the standard get_data function can be extended to submit a full and complex sql-select-statement and still use the features of multilanguage and protected documents !!

instead of submitting the database-name you can submit a hash:

{db=>DATABASENAME,
  sql=>SQL-STATEMENT}

The parameters order, where etc. are ignored in this case. The SQL-Statement must start with an uppercase SELECT -keyword !!

The programmer is fully responsible for the sql-statement, which means that in case of protected databases or multilanguage-databases all necessary fields have to be returned with proper names !!
To use protected documents a additional where-clause needs to be entered in the sql-statement:

sql=>'select id,name,name_en from table where WHERE_PROTECTED'

WHERE_PROTECTED is a keyword that is replaced by the proper where-clause then to provide only accessible documents. Note that the field used to identify protected documents needs to be provided by the sql-query in case of complex queries.

example as used in kat_level that provides multilang-features:

  $g_var->{sub}->{level_kat}=sub{
    my $var=shift;
    my $name=shift;
    my $db1=shift;
    my $db2=shift;

    my $sql="SELECT k.id,k.kategorie,k.kategorie_en,k.beschreibung,k.beschreibung_en,(position(' ' in k.kategorie)-1)/2 as l\
evel,count(d.oid) as num_textes from ".$db1." k left outer join ".$db2." d on (k.id=d.kategorie) group by k.id,k.kategorie,k\
.kategorie_en,k.beschreibung,k.beschreibung_en order by replace(k.kategorie,' ','0')";

    $var->getdata($name,{db=>$db1,sql=>$sql});
    return '';

  };

a untested extension of this example to also satisfy protected documents (in case we ever want protected kategories):

    my $sql="SELECT * FROM (SELECT k.id,k.protected,k.kategorie,k.kategorie_en,k.beschreibung,k.beschreibung_en,(position(' ' in k.kategorie)-1)/2 as l\
evel,count(d.oid) as num_textes from ".$db1." k left outer join ".$db2." d on (k.id=d.kategorie) group by k.id,k.protected,k.kategorie,k\
.kategorie_en,k.beschreibung,k.beschreibung_en order by replace(k.kategorie,' ','0')) AS temp where WHERE_PROTECTED";