# Modifying Table Query to Add Field

**URL:** <https://discourse.cakephp.org/t/modifying-table-query-to-add-field/3928>\
**Category:** Need Help\
**Created:** [February 16, 2018, 5:51am UTC](https://discourse.cakephp.org/t/modifying-table-query-to-add-field/3928 "2018-02-16T05:51:30Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![jerome.oneil](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.cakephp.org/jerome.oneil/32/376_2.png) [@jerome.oneil](https://discourse.cakephp.org/u/jerome.oneil)\
**Post date:** [February 16, 2018, 5:51am UTC](https://discourse.cakephp.org/t/modifying-table-query-to-add-field/3928/1 "2018-02-16T05:51:30Z")

</div>

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!

---

<div class="post-metadata">

**Author:** ![dmuenstermann](https://avatars.discourse-cdn.com/v4/letter/d/b9bd4f/32.png) [@dmuenstermann](https://discourse.cakephp.org/u/dmuenstermann)\
**Post date:** [February 16, 2018, 9:27am UTC](https://discourse.cakephp.org/t/modifying-table-query-to-add-field/3928/2 "2018-02-16T09:27:05Z")

</div>

Hi.

That sounds to me like a

> virtual field

is the things you are searching for.  
Have a look [at the docs](https://book.cakephp.org/3.0/en/orm/entities.html#creating-virtual-properties) and in [this extended tutorial](http://www.naidim.org/cakephp-3-tutorial-11-virtual-fields).

regards,  
Daniel

---

<div class="post-metadata">

**Author:** ![jerome.oneil](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.cakephp.org/jerome.oneil/32/376_2.png) [@jerome.oneil](https://discourse.cakephp.org/u/jerome.oneil)\
**Post date:** [February 16, 2018, 8:57pm UTC](https://discourse.cakephp.org/t/modifying-table-query-to-add-field/3928/3 "2018-02-16T20:57:08Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![dmuenstermann](https://avatars.discourse-cdn.com/v4/letter/d/b9bd4f/32.png) [@dmuenstermann](https://discourse.cakephp.org/u/dmuenstermann)\
**Post date:** [February 26, 2018, 12:27pm UTC](https://discourse.cakephp.org/t/modifying-table-query-to-add-field/3928/4 "2018-02-26T12:27:11Z")

</div>

I don’t know which database you are using, but for MySQL you can define that calculation on table-level ([MySQL manual](https://dev.mysql.com/doc/refman/5.7/en/create-table-generated-columns.html)).

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