Best Practice: Reusable custom methods for queries

Hi everyone,

I’m wondering what the best practice is when I want to reuse code to query the database (getting rid of delimiters in strings, transforming data in the database). I think the best way is to write a method or function that I can call whenever I need to.

However I don’t want to tamper with the existing framework, and I have a feeling that Cake is designed such that I will never have to do this but, in the case that I do, what is the best practice?


eg. I’d like to create a method that unravels data in the people.tags field. The current data is a string “Student;Teacher;Staff”. I’d like to transform the information then query the database to acquire this array:
[
student => ‘n’,
teacher => ‘n’,
staff => ‘y’
]
Then I’ll pass the array into the view for display.

The database frequently stores information like so and I prefer to avoid repetitive code. What is the best practice? Where should I store the method/function? (It seems that I can’t include files either :frowning: )

Please feel free to throw references at me,
Thanks!

https://book.cakephp.org/3/en/orm/retrieving-data-and-resultsets.html#custom-finder-methods
https://book.cakephp.org/4/en/orm/retrieving-data-and-resultsets.html#finding-key-value-pairs
https://book.cakephp.org/4/en/views/helpers/form.html#creating-select-checkbox-and-radio-controls

This is an unfortunate database design, and if it really is used in a lot of places, you might want to reconsider that. A structure where student, teacher and staff values are stored in an “attributes” or “tags” sort of table would give you what you want in your views without any custom coding required.

Thank you,

Sadly it was not my design…
Honestly always happy to hear back from you Zuluru.

If the design is not changeable (this applies less often than people think, but more often than it should…), then the best answer probably depends somewhat on the specifics of your scenario. For example, what exactly does the actual string look like? How does it transform from that into the array you’ve mentioned? Does it need to be able to transform in the other direction, from edit pages?

For example:
Roles table

id name tags
1 tiers Primary Supervisor;Secondary Supervisor;Tertiary Supervisor

Peoples table

id name primary_supervisor secondary_supervisor tertiary_supervisor
1 tiers Y N Y

I’d like to select “Primary Supervisor;Secondary Supervisor;Tertiary Supervisor” then pass it into the people table to create:
[
Primary Supervisor=> ‘Y,
Secondary Supervisor=> ‘N’,
Tertiary Supervisor=> ‘Y’
]

or something the like, to then pass it into the view layer (i.e. add.php or edit.php or view.php) for display as radio buttons or check boxes (in this case, they would be check boxes).

Of course, the structure of the array does not have to be the same as the one I just created as an example. As long as the data is in a form that can be displayed in the view, then it’s find by me.

Currently the code snippet looks something like this:
if(!empty($people->role->tags)){
$tags = $people->role->tags;
$tag_fields = explode(’;’, $tags);
for ($i = 0; $i < count($tag_fields); $i++)
{
$tag_fields[$i] = str_replace(’ ', ‘_’, strtolower($tag_fields[$i]));
}
$this->set(compact(‘people’, ‘tag_fields’));
}

But I’d like to reduce repetitive code, if there’s a better way, please let me know

So, in this particular case, there are fields on the people record (which is just a single person?) but which fields to display depends on what their role is? The peoples table has not just those three *_supervisor fields but also student, teacher and staff from the earlier example? But for somebody with role ID 1, those other fields would not be shown in your view, only the three supervisor fields?

Yes! There’s one record per person in the people table.
And the form display depends on the role this person holds.

All three fields would be shown. Another possible record in the role table would be

id name tags
1 tiers Primary Supervisor;Secondary Supervisor;Tertiary Supervisor
2 classroom positions Student;Teacher

Hence the people table would be extended to involve:

id name role primary_supervisor secondary_supervisor tertiary_supervisor student teacher staff
1 tiers 1 Y N Y null null null
2 james 2 null null null Y N null

Then if they’d like to edit james’ record, the form would produce two radio buttons-- one for student, and one for teacher.

How about something like this? In your Role entity:

public function _getTagFields() {
    if (empty($this->tags)) {
        return [];
    }

    // There are potential improvements to be made here via things like array_map or collections
    $tag_fields = explode(’;’, $this->tags);
    for ($i = 0; $i < count($tag_fields); $i++) {
        $tag_fields[$i] = str_replace(' ', '_', strtolower($tag_fields[$i]));
    }

    return $tag_fields;
}

Ad then you don’t need to set anything to do with this. In your view, you just access $people->role->tag_fields.

If you have other tables than roles that include this type of functionality, there are various ways that you could share this function among those entities; a trait would be the most obvious.

1 Like

Oh! I see!
There are important concepts about Cake in your answer that I didn’t know before.
Thank you so much for your guidance, this is definitely something that I can adapt and read more about :slight_smile:

https://book.cakephp.org/4/en/orm/entities.html#accessors-mutators

1 Like