Editor isn't respecting the alias in a field with an SQL function

Editor isn't respecting the alias in a field with an SQL function

stevevancestevevance Posts: 42Questions: 5Answers: 1
edited January 11 in Editor

I am trying to convert geometry in a PostgreSQL/PostGIS table into GeoJSON inside Editor and setting an alias for that field. In the "Getting started" documentation there is an example code (showing a MySQL function) that indicates that it's possible to use SQL functions in the fields.

The field I'm creating looks like the following:

Field::inst( 'ST_AsGeoJSON(ST_Transform(ST_Centroid(files_info.geom), 4326))', 'geometry' )
            ->getFormatter( function($val, $data, $opts) { return json_decode($val, true); } ) // server to client
            ->set(Field::SET_NONE),

However, Editor doesn't see the alias in a field with a function.

Using Editor's debug() mode, I can inspect the SQL that it's generated. Instead of using the geometry alias for the function that converts geometry to GeoJSON, it is using the function as as the alias! Thus, DataTables cannot find the data: "geometry" property in the data that's returned from the database.

This is the query Editor is trying:

SELECT wishlists_items.id as \"wishlists_items.id\", ST_AsGeoJSON(ST_Transform(ST_Centroid(wishlists_items.geom), 4326)) as \"ST_AsGeoJSON(ST_Transform(ST_Centroid(wishlists_items.geom), 4326))\" FROM wishlists_items ORDER BY wishlists_items.created_at desc LIMIT 10

I tried using an alias on a simpler function:

Field::inst( 'extract(year from wishlists_items.created_at) AS year' )
            ->set( false ),

And the problem in Editor's resulting SQL is still ignoring the year alias:

SELECT wishlists_items.id as \"wishlists_items.id\", extract(year from wishlists_items.created_at) as \"extract(year from wishlists_items.created_at)\" FROM wishlists_items ORDER BY wishlists_items.created_at desc LIMIT 10

I have found relevant code in the Query.php and Field.php files, and I tried tinkering with _dbField() and _buildField() functions, but I couldn't get the alias to be recognized when there's a function in the field name.

Is there a bug?

Answers

  • allanallan Posts: 51,453Questions: 1Answers: 7,759 Site admin

    However, Editor doesn't see the alias in a field with a function.

    An SQL alias is not actually quite the intention of the second parameter for the Field factory. The second parameter is what the value should be sent by in the JSON and also what the HTTP parameter name should be for reading sent data from the client-side.

    So I think it does do what you want (if it worked), but I wouldn't expect to see the "alias" in the SQL statement, but I would expect to see geometry in the resulting JSON.

    I've just tried modifying our simple example by adding:

            Field::inst( 'UPPER(first_name)', 'upper' )->set(false),
    

    And right enough the resulting JSON contains upper:

        {
          "DT_RowId": "row_1",
          "first_name": "Tiger",
          "upper": "TIGER",
          "last_name": "Nixon",
          "position": "System Architect",
          "email": "t.nixon@datatables.net",
          "office": "Edinburgh",
          "extn": "5421",
          "age": "61",
          "salary": "320800",
          "start_date": "2011-04-25"
        },
    

    So I'm not immediately sure what's going wrong here I'm afraid. What version of the PHP libraries are you using?

    Thanks,
    Allan

  • stevevancestevevance Posts: 42Questions: 5Answers: 1
    edited January 15

    I don't need it as an alias in the PostgreSQL sense; I need it as an alias in the JSON that Editor returns.

    This is the field I'm creating in Editor:

    Field::inst( 'ST_AsGeoJSON(ST_Transform(ST_Centroid(files_info.geom), 4326))', 'geometry' )
                ->getFormatter( function($val, $data, $opts) { return json_decode($val, true); } ) // server to client
                ->set(Field::SET_NONE),
    

    Instead of returning the field ST_AsGeoJSON(ST_Transform(ST_Centroid(files_info.geom), 4326)) as geometry, it returns it as ST_AsGeoJSON(ST_Transform(ST_Centroid(files_info.geom), 4326)).

    The query that's returned in the JSON data when $editor->debug(true):

    SELECT id as "id", files_info.geojson as "files_info.geojson", 
    ST_AsGeoJSON(ST_Transform(ST_Centroid(files_info.geom), 4326)) as "ST_AsGeoJSON(ST_Transform(ST_Centroid(files_info.geom), 4326))" 
    FROM files_info 
    WHERE (files_info.type = :where_1 OR files_info.type = :where_2 ) 
    ORDER BY files_info.created_at desc
     LIMIT 5 OFFSET 5
    

    There are three PostGIS functions in that field.

  • allanallan Posts: 51,453Questions: 1Answers: 7,759 Site admin

    Thanks for the extra information. Could you confirm what version of the Editor PHP libraries you are using please? You can find it from the version property in Editor.php.

    It would be worth trying a simple case such as the UPPER(...) that I used above as well to see if that works for you and possibly isolate the issue to the more complex expression - although I don't see why it would make any difference!

    Allan

  • stevevancestevevance Posts: 42Questions: 5Answers: 1

    I'm using Editor version 1.9.0

    Field::inst( 'upper(wishlists_items.user_text) AS wishlists_items.user_text' )` indeed returns the text in ALL CAPS.
    

    I simplified the function that converts the geometry features stored in the PostGIS feature, by using only 1 function instead of 3. It looks like this:

    Field::inst( 'ST_AsGeoJSON(wishlists_items.geom)', 'wishlists_items.geometry' )
    

    When I do that, the alias works and data is returned in the geometry property.

    However, I need all 3 functions because I need to transform the geometry to a projection that can be displayed on a web map (WGS84/EPSG 4326, in case anyone else is wondering).

    As soon as I add a second function, the original problem returns.

Sign In or Register to comment.