Calculated Field in Relationship

A place for users and developers of the Xataface to discuss and receive support.

Calculated Field in Relationship

Postby 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?
jmboettger
 
Posts: 8
Joined: Wed May 02, 2012 6:35 am

Re: Calculated Field in Relationship

Postby 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
shannah
 
Posts: 4457
Joined: Wed Dec 31, 1969 5:00 pm

Re: Calculated Field in Relationship

Postby 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
jmboettger
 
Posts: 8
Joined: Wed May 02, 2012 6:35 am

Re: Calculated Field in Relationship

Postby 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',
+  ),
+)
+
+
+
+
+
+),
+
);
shannah
 
Posts: 4457
Joined: Wed Dec 31, 1969 5:00 pm

Re: Calculated Field in Relationship

Postby jmboettger » Fri May 11, 2012 1:02 am

Thanks i'll try it out.
jmboettger
 
Posts: 8
Joined: Wed May 02, 2012 6:35 am

Re: Calculated Field in Relationship

Postby jmboettger » Fri May 11, 2012 1:19 am

Works!

Thx.
jmboettger
 
Posts: 8
Joined: Wed May 02, 2012 6:35 am


Return to Xataface Users

Who is online

Users browsing this forum: No registered users and 20 guests

Powered by Dataface
© 2005-2007 Steve Hannah All rights reserved