Calculated Field in Relationship

Archived from the Xataface Users forum.

jmboettger — Sun May 06, 2012 10:01 am

Hi i’m trying to add a calculated field in a relation table.

Code: Select all

__sql__="SELECT * , DATE_ADD( STR_TO_DATE( Datum, '%Y-%m-%d' ) , INTERVAL 2 YEAR ) AS Ablaufdatum FROM TnMed pc INNER JOIN Betriebsmedizin c ON pc.MedID = c.MedID WHERE pc.TnID = '$TnID'"

but it always fails. I Just want to have the Date in Datum plus 2 years added as Ablaufdatum.

Table Structure

Betriebsmedizin
MedID
Title

TnMed
MedID
TnID
Datum

Mitarbeiter
TnID
Name

Any Ideas why that always fails?

---

shannah — Mon May 07, 2012 11:47 am

Where is this __sql__ directive? In a relationships.ini file or the fields.ini file. Calculated fields should be handled in the fields.ini file __sql__ directive. What do you mean by “fail”. Is there an error message? If so, what does it say? And when does it appear?

-Steve

---

jmboettger — Tue May 08, 2012 11:19 am

its in the relationships.ini and is supposed to be a grafted field. the error is

Code: Select all

The Error was Parse error: Expected an expression and unit for the interval

.

Thx

Jakob

---

shannah — Tue May 08, 2012 2:17 pm

Fixed in SVN.
Trunk rev 3567,
1.3.x rev 3568
1.5.x rev 3569

Code: Select all

`` van-fcat-07:xataface shannah$ svn diff -r 3566:3567 .
Index: lib/SQL/Parser.php
===================================================================
— lib/SQL/Parser.php   (revision 3566)
+++ lib/SQL/Parser.php   (revision 3567)
@@ -55,6 +55,12 @@
     var $reserved = array();
     var $units = array();
     var $dialect;
+   
+    /**
+     * A flag to see if there has been an interval that has not been closed
+     * by a unit.
+     */
+    var $openInterval = false;

     var $dialects = array(“ANSI”, “MySQL”);
@@ -268,9 +274,14 @@
         $this->token = $this->lexer->lex();
         $this->tokText = $this->lexer->tokText;
         // deal with case where the token may be identified as a function but is actually an identifier
+        if ( $this->token == ‘interval’ ){
+           $this->openInterval = true;
+        }
         
-       
-        if ( !isset($this->constants[$this->token]) and isset( $this->functions[$this->token]) and
+        if ( $this->openInterval and isset($this->units[$this->token]) ){
+           // just leave it be… this a unit
+           $this->openInterval = false;
+        } else if ( !isset($this->constants[$this->token]) and isset( $this->functions[$this->token]) and
             !isset( $this->reserved[$this->token]) ){
            
            $nextTok = $this->lexer->lex();
Index: lib/SQL/tests/mysql_select.php
===================================================================
— lib/SQL/tests/mysql_select.php   (revision 3566)
+++ lib/SQL/tests/mysql_select.php   (revision 3567)
@@ -7658,4 +7658,145 @@
),

+array(
+’sql’ => “SELECT * , DATE_ADD( STR_TO_DATE( Datum, ‘%Y-%m-%d’ ) , INTERVAL 2 year ) AS Ablaufdatum FROM TnMed pc”,
+’expected_compiled’ => “select , date_add(str_to_date(Datum, ‘%Y-%m-%d’), interval 2 year) as Ablaufdatum from TnMed as pc”,
+’expect’ => array (
+  ‘command’ => ‘select’,
+  ‘columns’ =>
+  array (
+    0 =>
+    array (
+      ‘type’ => ‘glob’,
+      ‘table’ => ‘’,
+      ‘value’ => ‘’,
+      ‘alias’ => ‘’,
+    ),
+    1 =>
+    array (
+      ‘type’ => ‘func’,
+      ‘table’ => ‘’,
+      ‘value’ =>
+      array (
+        ‘name’ => ‘date_add’,
+        ‘args’ =>
+        array (
+          0 =>
+          array (
+            ‘type’ => ‘function’,
+            ‘value’ =>
+            array (
+              ‘name’ => ‘str_to_date’,
+              ‘args’ =>
+              array (
+                0 =>
+                array (
+                  ‘type’ => ‘ident’,
+                  ‘value’ => ‘Datum’,
+                ),
+                1 =>
+                array (
+                  ‘type’ => ‘text_val’,
+                  ‘value’ => ‘%Y-%m-%d’,
+                ),
+              ),
+            ),
+          ),
+          1 =>
+          array (
+            ‘type’ => ‘interval’,
+            ‘value’ => 2,
+            ‘expression_type’ => ‘int_val’,
+            ‘unit’ => ‘year’,
+          ),
+        ),
+        ‘alias’ => ‘Ablaufdatum’,
+      ),
+      ‘alias’ => ‘Ablaufdatum’,
+    ),
+  ),
+  ‘column_tables’ =>
+  array (
+    0 => ‘’,
+  ),
+  ‘column_names’ =>
+  array (
+    0 => ‘*’,
+  ),
+  ‘column_aliases’ =>
+  array (
+    0 => ‘’,
+  ),
+  ‘set_function’ =>
+  array (
+    0 =>
+    array (
+      ‘name’ => ‘date_add’,
+      ‘args’ =>
+      array (
+        0 =>
+        array (
+          ‘type’ => ‘function’,
+          ‘value’ =>
+          array (
+            ‘name’ => ‘str_to_date’,
+            ‘args’ =>
+            array (
+              0 =>
+              array (
+                ‘type’ => ‘ident’,
+                ‘value’ => ‘Datum’,
+              ),
+              1 =>
+              array (
+                ‘type’ => ‘text_val’,
+                ‘value’ => ‘%Y-%m-%d’,
+              ),
+            ),
+          ),
+        ),
+        1 =>
+        array (
+          ‘type’ => ‘interval’,
+          ‘value’ => 2,
+          ‘expression_type’ => ‘int_val’,
+          ‘unit’ => ‘year’,
+        ),
+      ),
+      ‘alias’ => ‘Ablaufdatum’,
+    ),
+  ),
+  ‘table_names’ =>
+  array (
+    0 => ‘TnMed’,
+  ),
+  ‘table_aliases’ =>
+  array (
+    0 => ‘pc’,
+  ),
+  ‘tables’ =>
+  array (
+    0 =>
+    array (
+      ‘type’ => ‘ident’,
+      ‘value’ => ‘TnMed’,
+      ‘alias’ => ‘pc’,
+    ),
+  ),
+  ‘table_join_clause’ =>
+  array (
+    0 => ‘’,
+  ),
+  ‘all_tables’ =>
+  array (
+    0 => ‘TnMed’,
+  ),
+)

• +),
• ); ``

---

jmboettger — Fri May 11, 2012 1:02 am

Thanks i’ll try it out.

---

jmboettger — Fri May 11, 2012 1:19 am

Works!

Thx.