Setting aliases for fields (frustraded by book)

In the templates I want to use for example the label ‘Water’ for the fieldname ‘H20’.

Don’t know why, but it was hard find in the book or by google. Aliases, rename, label, etc. mostly give answers about forms. It’s a bit mysterious why such a common feature is so hard to find (I know, by me, wrong search word etc, but nevertheless). Maybe a subheading in the book could help google?

Finally found it by a link on stackoverflow discussing a completely other topic, query builder, selecting data.

// Results in SELECT id AS pk, title AS aliased_title, body ...
$query = $articles->find();
$query->select(['pk' => 'id', 'aliased_title' => 'title', 'body']);

Pasted and changed the table and fields in my controller: find on null gives problem.

Guess these lines are needed somewhere?

use Cake\ORM\Locator\LocatorAwareTrait;

$query = $this->getTableLocator()->get('Articles')->find();

Tried some, but no success. I ended with a working

$individuals = $this->paginate($this->Individuals->find()->select(['id','name','water'=>'data_1']));

Just keep wondering why the book is at places so hard to understand by noobs. It’s like it’s written for people who have a deep understanding of cakephp, but that group of people isn’t the audience.

One thing I guess is the start, using the console, clean sheet after fresh install or baked files. At this point the book presumes console/clean sheet, while I’m working with baked code?

Ok, that’s my frustration, if there are more people having this idea, I’m willing to help to build a book where the baking is the core, and with three levels of knowledge. I come across a lot of features I know I will never use, or even just don’t know when I should use them. Such simple things and much used as aliases are hard to find and implement (by me).

The question I can’t find an awnser: When working sloppy (that’s me), I will forget to change copied templates for 100%. In the end a template can use a field which isn’t selected anymore. The value gets ‘0’, that’s misleading.

A blank would be better, an error the best (like in other cases when a field/data is not available to the view/template).

How to get that done?

I think you’re trying to change the name of a field in the entity, when maybe what is best is to change the name of the field only in the UI. echo $this->Form->control('H2O', ['label' => 'Water']) would do that in your form, for example. It’s not something that would survive re-baking the file, but then again any such change that you might make to rename a field is going to have that problem if you re-bake whatever file you made that change in. Am I missing something?

No, I’m not trying to change the name of a field in the entity, just the fieldname in the default baked index.php.

I succeeded in doing so with

$individuals = $this->paginate($this->Individuals->find()->select(['id','name','water'=>'data_1']));

The holy grail of how to let changes survive in a generator-environment, I know I will not find it with ’ bake’ :slight_smile:

So now I just have 2 issues:

  • NULL gets 0;
  • just found out I can’t sort when clicking on the new column-name’s in view. Arrows get visible, but nothing happens.

I think you’re missing my point, or I’m missing yours. Can you share the relevant baked template code, and add comments about what it’s doing that you don’t like?

Yep, I think so, forget about it.

Let’s focus on just the actual issue I have at the moment.

This is the code I’m using now in my testbed, a function in my individuals-controller, aiming to get a subset of the available fields. It’s based on baked code, I just extended the ($this->Individuals):

        public function subset()
    {

        $this->paginate = [
            'contain' => ['Groups'],
        ];
     
       $individuals = $this->paginate($this->Individuals->find()->select(['id','name','water'=>'data_1']));
 
       $this->set(compact('individuals'));
    }

I editted the baked subset.php template, took the non-selected fields out and use the alias:

   <h3><?= __('Individuals (SubSet)') ?></h3>
    <div class="table-responsive">
        <table>
            <thead>
                <tr>
                    <th><?= $this->Paginator->sort('id') ?></th>
                    <th><?= $this->Paginator->sort('name') ?></th>
                    <th><?= $this->Paginator->sort('water') ?></th>               
                    <th class="actions"><?= __('Actions') ?></th>
                </tr>
            </thead>
            <tbody>
                <?php foreach ($individuals as $individual): ?>
                <tr>
                    <td><?= $this->Number->format($individual->id) ?></td>
                    <td><?= h($individual->name) ?></td>
                    <td><?= $this->Number->format($individual->water) ?></td>
                    <td class="actions">
                        <?= $this->Html->link(__('View'), ['action' => 'view', $individual->id]) ?>
                        <?= $this->Html->link(__('Edit'), ['action' => 'edit', $individual->id]) ?>
                        <?= $this->Form->postLink(__('Delete'), ['action' => 'delete', $individual->id], ['confirm' => __('Are you sure you want to delete # {0}?', $individual->id)]) ?>
                    </td>
                </tr>
                <?php endforeach; ?>
            </tbody>
        </table>
    </div

This gives:

Clicking on the arrow doesn’t the asc/desc sorting.

Is there an error in my code, of is it just not possible to sort on alias?

Get rid of the “aliasing” in your query, and use $this->Paginator->sort('data_1', 'water').

1 Like

Thanks!

(Well, I could have read the cookbook 1000 times, but never would have the slightest idea to use this to rename the column)

This is what I meant about changing names in the UI instead of the data in the entity.

First thing was to read that section, but still, no clue to use it as ‘renamer’ of column heads.

Think I interpret ‘entity’ like something in the database, at least have the same idea with the word ‘UI’ :slightly_smiling_face:

That’s essentially what an entity is, yes. By changing your select statement, you were creating a disconnect between what fields are in the entity and what fields are in the database. That disconnect was causing all of your problems. All you wanted to do was change the labels (aka names aka titles) on things in the UI, and the template is the place to make UI changes, not the database query.

Hi All,

Can anyone help me with how to alias field name with hyphen? For example in below code I want global_ledger_group_id as data-global_ledger_group_id

It is working fine in 3.4 and I’m upgrading into 4.5 version

Thanks

$clgList = $this->LedgerGroup->find()
->disableHydration()
->where([‘is_deleted’ => 0, ‘company_id’ => $companyInfo[‘id’], ‘id !=’ => $clg_id])
->select([‘value’ => ‘id’, ‘text’ => ‘name’, ‘data-global_ledger_group_id’ => ‘global_ledger_group_id’])
->toArray();

SQL doesn’t allow - inside column names because otherwise it can’t correctly detect e.g. mathematical operations like intcolumn1-intcolum2 which is of course also valid.

If you really really really can’t deal with this you will have to enable quoteIdentifiers in your Datasource config.

As the docs say be aware, that this decreases performance.

See also Database Basics - 4.x

1 Like

Thanks for providing clarification.