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

stevevance

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?

allan

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

stevevance

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.

allan

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

stevevance

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.

stevevance

I upgraded to 2.0.6 and the problem remains.

It seems like the code is "punishing" (ignoring) a field that has more than one group of parentheses (meaning it has more than one function).

I think the issue is somewhere in _build_field (in lib/Database/Query.php).

stevevance

I may not have been identifying the problem accurately.

I am still having related issues. This time when I put GeoJSON in the field to get from the database.

This is the field I want to get, and I need it to alias to distance so that the feature in the database represented by geom can be sorted by how far away it is from the given GeoJSON.

$editor->field(
            Field::inst( "ST_Distance(geom, ST_Transform(ST_SetSRID(ST_GeomFromGeoJSON('{$geojson}'), 4326), 3435)) AS properties.distance" )
        );

This is the query that DataTables Editor is generating:

SELECT "id" as "id", "name" as "name", "operator" as "operator", "address" as "address", "type" as "type", "note" as "note", "data" as "data", "city" as "city", ('Feature') as "('Feature')", ST_AsGeoJSON(ST_Transform(geom, 4326), 5) as "ST_AsGeoJSON(ST_Transform(geom, 4326), 5)", ST_Distance(geom, ST_Transform(ST_SetSRID(ST_GeomFromGeoJSON('{"type":"Point","coordinates":[-87.654998,41.813802]}'), 4326), 3435)) as "ST_Distance(geom, ST_Transform(ST_SetSRID(ST_GeomFromGeoJSON('{"type":"Point","coordinates":[-87.654998,41.813802]}'), 4326), 3435))" FROM "view_m_logistics3" WHERE ST_DWithin(geom, ST_Transform(ST_SetSRID(ST_GeomFromGeoJSON('{"type":"Point","coordinates":[-87.654998,41.813802]}'), 4326), 3435), 52800) = :where_0 ORDER BY "operator" asc LIMIT 5 OFFSET 10

The query fails because it's not valid; there are double quotes inside the alias as "ST_Distance(geom, ST_Transform(ST_SetSRID(ST_GeomFromGeoJSON('{"type":"Point","coordinates":[-87.654998,41.813802]}'), 4326), 3435))". But the alias should have been properties.distance or just distance.

allan

Thanks for the clarification. Yes, unfortunately the Editor libraries aren't treating the alias quite as you are looking for here (a traditional SQL alias). Rather they are using it as an alias for the SQL -> JSON translation. I.e. the alias is use for the JSON name, rather than in the query.

My plan to address this, which will be enacted in Editor 2.1's server-side libraries, is to change how the SQL aliasing works. I haven't worked out the details yet, but all fields will be aliased to a valid SQL simple name and we'll then map that output.

The workaround until that is implements is to use a VIEW which does the function calling as need and then Editor can simply query the VIEW as if it were a table.

Regards,
Allan

stevevance

I left this problem alone for a while and now I'm back to it, and I re-discovered this thread I made.

One reason why a VIEW will not work for me is that the VIEW cannot measure the distance ahead of time, because it doesn't know which distance to measure until the user provides a street address.

I am going to try a workaround and create a custom PostgreSQL function so that I there are fewer () in the field definition.

Question @allan: Does Editor 2.1 include a change in how aliases are handled? I installed it and it doesn't seem to have changed from 2.0.10 (the prior version I was using) to 2.1.

allan

No sorry - it wasn't possible to include that in the 2.1 release. It is something that needs to be more closely looked at though and I've got it in my list for 2.2. The big thing I'm worried about is backwards compatibility since it is a public interface.

Are you using this for a read (SELECT) action only? If so, perhaps it would be worth just bypassing the Editor class at the moment and using Database->raw() to perform the query?

Allan

stevevance

@allan - this is for a SELECT action only query. I will look into using Database->raw()!

It may be complicated to rewrite my code, though, because I have a lot of WHERE conditions that are dynamically inserted as needed based on conditions that come through the $_REQUEST variable.

stevevance

I came up with an alternative, which was to write a custom function that combined the multiple functions into a single one and still respected the necessary arguments.

https://www.stevencanplan.com/2023/03/creating-a-postgresql-postgis-function-to-get-around-a-datatables-editor-limitation/

allan

That's awesome - a write up as well. Many thanks for sharing that and great to hear you've got a solution.

Allan