Modifying Table Query to Add Field

Need Help
1

I need to modify the query behind my table object to include a call to a PostGIS function. I’m not sure if I should do this in the Entity, or allow Cake to do it for me by adding the data to the query.

For example, Cake’s default query might look something like this…

select a, b, c, d from some_table;

What I need is

select a, b, c, d, function(d) as e from some table;

And my entities to reflect the properties a, b, c, d, AND e.

How, and where, should I implement this?

Thanks!

2

Hi.

That sounds to me like a

virtual field

is the things you are searching for.
Have a look at the docs and in this extended tutorial.

regards,
Daniel

3

Yeah I’m aware of the virtual fields, and am actually using them in the same Entity. It seems that this methodology would actually require another round trip to the DB, though. The first would return all my tabular data, and the second would then query again every time a new entity is created.

Table -> select a, b, c, d from some_table;

Entity->select function(d);

Which seems kind of inefficient, given that I can do the entire thing on the database side of the house in one fell swoop.

I’d really like this to be reflected in the Table, as that’s the most efficient place to do it.

Any ideas? Or am I baked (Ha!) in to this?

4

I don’t know which database you are using, but for MySQL you can define that calculation on table-level (MySQL manual).

This way you have the calculation done in the database and can use that field as usual in cake…