Membership app, Processwire limitations on unique records?

[Kiwi Chris]

From recent discussions around the future of Processwire, I suspect my use case might be a bit different to others, as rather than using it in the website 'builder' type scenario, I'm using it more as a database management system, where structured data is critical, and quite often 'pages' will never be visualised via a template, and if they are, the template file is effectively a 'report'.

In my case I'm tending to build sites where the admin IS the site, and there's often little or no front-end. 

I've wondered whether I'd be better to learn some framework like Yii or ASP.Net that have CRUD code generators that can generate data entry forms for SQL tables, but I like Processwire's admin, and the permissions system is something I rely on a lot.

I've started working on a membership app, that will have quite a few users, and I've immediately run into a problem. 

I want admin users to be able to store first name, last name, address etc for members, but it's quite conceivable with a large enough list that it's possible to have more than one say 'John Smith', living at different addresses.

I know how to write a hook to put an arbitrary auto-incrementing number in the title field (which will also populate the name field), but this seems to me to break the DRY principle. There's already a page ID, and if I'm effectively populating two other indexed database fields with an arbitrary number so that I can uniquely identify records and keep Processwire happy, it doesn't feel quite right.

In straight SQL I can use an auto-increment numeric surrogate primary key and create a constraint on multiple columns so I could have as many 'John Smiths' as I like as long as they reside at different addresses, but I'm not sure how I'd go about this in Processwire, as each field resides in its own table.

Perhaps the new Combo Profield @ryan recently released might be have potential for this, as all the subfields reside in a common database table, and there's already some capability to edit the database schema, although I'm not sure if this extends to creating custom constraints? That would be a really great feature, as it would allow defining unique records based on a combination of sub-fields.

The Combo fieldtype is still a fieldtype though which needs to be added to a template with mandatory fields, so I still potentially have page id, name, and title all essentially duplicating each other's functionality as arbitrary numeric fields.

I understand why page name is meant to be mandatory, as a page that has a template file needs to be accessible via a URL, and the page name is part of the routing, however I'm not sure whether it's practical for pages that don't have a template file to simply work off the page ID? What might be useful is a template property setting that can indicate that the id should be used as the name as well, so there's still a 'name' but it's populated at runtime as a reference to the page id. I'm guessing it might be possible to do something like this via a hook?

[szabesz]

1 hour ago, Kiwi Chris said:

There's already a page ID

 The page id is an auto-incrementing number. Can't you just simply rely on that? Am I missing something perhaps?

1 hour ago, Kiwi Chris said:

id should be used as the name as well

Something like the following?

 

[Kiwi Chris]

1 hour ago, szabesz said:

The page id is an auto-incrementing number. Can't you just simply rely on that? Am I missing something perhaps?

I can write a hook to make the page name the page ID. It's only one duplicate field, and I can rename the display name for title for the templates involved as First Name I guess, as that's always going to be a required field, just not unique.

It doesn't solve the issue around validation of uniqueness across multiple fields, although I guess that can be achieved with a hook before saving, to check whether  $pages->count() > 0 for the field combination I want to be unique.

[monchu]

In my case to solve duplicate user name, I used a name page with this format: Y-m-d-page->id-userfname-userlname

With this format, I can get both, User Account ID and Registration Date.

[szabesz]

8 hours ago, Kiwi Chris said:

It doesn't solve the issue around validation of uniqueness across multiple fields

Since PW 3.0.127 there is the option of setting "Unique" status for pages. Maybe you could utilize that? https://processwire.com/blog/posts/pw-3.0.127/

I'm not quite sure what you are referring to by saying "across multiple fields". Do you need the value of one field to be unique on the very same page compared to another field(s) of the same page, perhaps?

[Kiwi Chris]

1 hour ago, szabesz said:

Since PW 3.0.127 there is the option of setting "Unique" status for pages. Maybe you could utilize that? https://processwire.com/blog/posts/pw-3.0.127/

I'm not quite sure what you are referring to by saying "across multiple fields". Do you need the value of one field to be unique on the very same page compared to another field(s) of the same page, perhaps?

The "Unique" status in Processwire does quite the opposite of what I want.

What I need is to be able to have First name: John, last name: Smith, Address: 10 main street allowed but unique

and also First name: John, last name: Smith, Address: 10 high street also allowed but unique

Address can't be the unique field though because there could be someone else living at the same address, so what makes a record unique is the combination of all three fields. 

This is easy to do in SQL, eg CREATE UNIQUE INDEX person ON members (firstName, lastName, address);

but not quite so obvious in Processwire, although I think perhaps a hook before Pages::added could do a $pages->count('firstName=John,lastName=Smith,Address=10 main street') and if this returns a non-zero value, return an error.

I'm not sure what sort of performance implications there would be doing it as a hook vs in SQL on multiple fields in a table.

[MoritzLost]

Quote

It doesn't solve the issue around validation of uniqueness across multiple fields, although I guess that can be achieved with a hook before saving, to check whether  $pages->count() > 0 for the field combination I want to be unique.

This is the way.

Quote

but not quite so obvious in Processwire, although I think perhaps a hook before Pages::added could do a $pages->count('firstName=John,lastName=Smith,Address=10 main street') and if this returns a non-zero value, return an error.

I'm not sure what sort of performance implications there would be doing it as a hook vs in SQL on multiple fields in a table.

Performance is relative. Using a hook to check if a duplicate record exists will always be fast, or at the most have linear complexity. How many users do you expect to add each day? How many users / people entries in total do you have? If it's 10 million, then you'll have to start thinking about the performance of a uniqueness check, but if it's significantly less ...

Besides performance, what other benefits do you hope to gain by using a unique index in SQL as opposed to a custom check on the interface-level (i.e. a ProcessWire hook)? The only thing I can think of is that uniqueness is guaranteed at database level, but is that going to matter to you? If you use the right hook, you can prevent duplicate entries from both the CMS interface and ProcessWire's API, so unless someone is going to manually insert records to your database through the command line, the result is identical.

By the way, the unique setting for pages guarantees a globally unique page name, not uniqueness of an individual field. Just set the name to include the first name, last name and address (you can do that through the template settings or use a hook if you need more control) and use a hook to set new pages to unique. This will guarantee uniqueness for that combination of fields. Granted, it's only guaranteed on API level, not database level, but again, this might be absolutely enough for your use-case.

[bernhard]

On 2/10/2021 at 8:43 PM, Kiwi Chris said:

I've wondered whether I'd be better to learn some framework like Yii or ASP.Net that have CRUD code generators that can generate data entry forms for SQL tables, but I like Processwire's admin, and the permissions system is something I rely on a lot.

I'm using ProcessWire all the time for Backend-only applications. Sometimes I also feel like I'm using it in a "wrong" way... But sometimes it seems that there are more people using PW for database applications. I think it is a great tool for such scenarios - I have to admit though that I did not try any others. What's really missing is a way to present tabular lists. That's why I created RockGrid/RockTabulator/RockFinder. RockFinder3 is a great product, while RockGrid/RockTabulator is better than nothing but I'm working on a better solution ? 

I don't get your problem though... What's wrong with a list like this?

id | forename | surname | address    | name
1  | john     | doe     | exroad123  | 1
2  | john     | doe     | exroad234  | 2
3  | maria    | müller  | musterweg  | 3

I tend to use the title field for a representation of the page that can be used easily on page lists etc:

id | title                    | forename | surname | address    | name
1  | john doe (exroad123)     | john     | doe     | exroad123  | 1
2  | john doe (exroad234)     | john     | doe     | exroad234  | 2
3  | maria müller (musterweg) | maria    | müller  | musterweg  | 3

That's a simple saveReady hook.

I'd recommend using custom page classes a lot for such setups. It keeps the code clean and a lot easier to maintain. You'll also get the benefit of code completion in your IDE and your hooks will get a lot cleaner, easier and better maintainable as well:

// in site/init.php or an autoload module
$contact = new Contact();
$contact->init();

// in site/classes/Contact.php
<?php namespace ProcessWire;
class Contact extends Page {

  public function init() {
    $this->addHookAfter("Pages::saveReady", $this, "setTitle");
  }

  /**
   * Get page title based on other fields
   * @return string
   */
  public function getTitle() {
    return $this->forename." ".$this->surname." ({$this->address})";
  }

  public function setTitle(HookEvent $event) {
    $page = $event->arguments(0);
    if(!$page instanceof self) return;
    $title = $page->getTitle();
    $exists = $event->pages->get([
      'template' => $page->template,
      'title' => $title,
      'id!=' => $page->id,
    ]);

    if($exists->id) {
      // page exists, show error message and do whatever is necessary
    }
    else {
      $page->title = $title;
    }
  }
}

Code completion will help you a LOT when the project grows...

Combine that with RockMigrations and you feel like you got superpowers. At least I do ? 

[szabesz]

6 hours ago, bernhard said:

$contact = new Contact();
$contact->init();

If I'm not mistaken, it can be simplified by utilizing the __loaded() method instead. PW will call that method after the page object's data has been populated from the database (so all its field values are accessible), and it can also act like your "manually" called init function.

Credits for this "tip" goes to @Robin S: https://processwire.com/talk/topic/25107-custom-page-class-how-to-use-__construct-or-similar/?do=findComment&comment=211144

 

[bernhard]

Thx @szabesz

the problem is, that this will be called every time a page is loaded (I think?). But we want the init() to fire only once for all instances of Contact, so that all hooks only get added once and not multiple times.

RockMigrations has a helper for that to trigger init/ready on a pageclass or even trigger it for all classes in a given folder. But I did not investigate a lot in that approach so if there are better solutions (maybe using PWs ClassLoader that I havent really worked with) I'm happy to hear them ? 

[szabesz]

1 hour ago, bernhard said:

the problem is, that this will be called every time a page is loaded (I think?). But we want the init() to fire only once for all instances of Contact, so that all hooks only get added once and not multiple times.

I have not yet tested the difference, so it is something I need to look into. To tell the truth, I have just started to use page classes, so I am happy to hear how others make use of it.

[Pixrael]

On 2/10/2021 at 11:30 PM, monchu said:

In my case to solve duplicate user name, I used a name page with this format: Y-m-d-page->id-userfname-userlname

With this format, I can get both, User Account ID and Registration Date.

In my current project, I have the user's email field as required, and realizing that the email is unique to each user, I use base64_encode () successfully, having john.doe@email.com the page name of that user is unique like am9obi5kb2VAZW1haWwuY29t, in addition, this keeps a record of the initial email used when registration was made. Initially I had thought of using the email with $sanitizer->pageName() but I did not want to expose the email address so obvious in the url.

[Robin S]

1 hour ago, Pixrael said:

I use base64_encode () successfully, having john.doe@email.com the page name of that user is unique like am9obi5kb2VAZW1haWwuY29t, in addition, this keeps a record of the initial email used when registration was made. Initially I had thought of using the email with $sanitizer->pageName() but I did not want to expose the email address so obvious in the url.

Getting OT here, but if the page names are publicly accessible anywhere but the email addresses are supposed to be private then you risk leaking the users' emails because base64 encoding is pretty recognisable and easily decoded by anyone. In that case it would be safer to encrypt the email addresses for the name using something like openssl_encrypt(). Also base64 strings can contain characters that are not URL safe so you'd need to replace those, e.g. "+" and "/".

[Kiwi Chris]

On 2/12/2021 at 9:31 PM, bernhard said:

I'm using ProcessWire all the time for Backend-only applications. Sometimes I also feel like I'm using it in a "wrong" way... But sometimes it seems that there are more people using PW for database applications. I think it is a great tool for such scenarios - I have to admit though that I did not try any others. What's really missing is a way to present tabular lists. That's why I created RockGrid/RockTabulator/RockFinder. RockFinder3 is a great product, while RockGrid/RockTabulator is better than nothing but I'm working on a better solution ? 

I don't get your problem though... What's wrong with a list like this?

@bernhard Thanks for a really helpful example. It's also good to know other people are using Processwire as a full backend application. Thanks also for making RockFinder3, it's a great module.

For non-editable tabular lists, I've just used the core MarkupAdminDataTable that works well enough for my needs, although I should probably build some sort of reporting module so that I can easily define group headers and footers for totals, headings etc. 

[Pixrael]

On 2/12/2021 at 5:13 PM, Robin S said:

Getting OT here, but if the page names are publicly accessible anywhere but the email addresses are supposed to be private then you risk leaking the users' emails because base64 encoding is pretty recognisable and easily decoded by anyone. In that case it would be safer to encrypt the email addresses for the name using something like openssl_encrypt(). Also base64 strings can contain characters that are not URL safe so you'd need to replace those, e.g. "+" and "/".

You're right, but I think it depends on the project. In my case, it's an internal company system, and only the admin accesses the user's url in the backend. I also take into account a future situation, when the user needs to enter his profile on the front-end, in that situation he is the only one who has access to the url that shows the name of the page. For me it works perfectly. The developer just needs to think ahead and decide. Any page name you want to use in PW should be sanitize first as a rule.

[MarkE]

On 2/14/2021 at 7:43 PM, Kiwi Chris said:

It's also good to know other people are using Processwire as a full backend application.

I chose to use PW precisely because of its back-end capabilities - it facilitates quick and effective building of back-end applications. I've used it for a few sites now - one (a holiday cottage booking system) is an almost completely submerged "iceberg" which has hardly any front-end pages and even those are access-controlled - e.g. for use by other front-end apps. In that one, I have a similar issue to yours with customer pages. Of course, all the pages and names are unique, but that is not the real issue, which is to say precisely when a combination of fields should determine that a new page is added rather than an existing one updated . My solution is to have a hidden field (uniqueId) which is a concatenation of first name, last name, email address and postcode. So I have this in the before Pages::save hook:

            case 'Person' :
                if ($p->id) {
                    $p->of(false);
                    $postCode = ($p->address) ? $p->address->postal_code : '';
                    $p->uniqueId = wire()->sanitizer->selectorValue($p->firstName . '_' . $p->lastName . ' ' . $p->email . ' ' . $postCode);
                    // Prevent duplicate records
                    if ($p->parents("name=contacts")->count()) {
                        $uniqueMatches = wire()->pages->find("has_parent=/contacts/, template=Person, uniqueId=$p->uniqueId, id!=$p->id, include=unpublished");
                        if ($uniqueMatches->count() > 0) {
                            $this->warning("Change to contact record not saved - Unique id: " . $p->uniqueId . " already exists. (Use search box to find it). If you just created this record, please delete it if it is a duplicate.");
                            $event->replace = true;
                            $event->return;
                        } else {
                            $event->arguments(0, $p);  // Save the unique value, overwriting any previous value
                        }
                    }
                }
                break;

One might argue that the uniqueID is superfluous and that the key should be assigned to name instead, but my preference was for a hidden field.

Another site is less of an iceberg (about 30% above the water) and is a membership system whereby members can access their records only through the front end via an email challenge protocol (like a password reset every time - a lot of the members are quite old and struggle with passwords). There are various admin access permissions for different roles within the club (membership secretary, treasurer, event organiser etc.). This one just uses the email address as the unique id - an approach not possible for the first site as not all customers book on line.

I think PW is admirably suited for this type of application - I researched a whole bunch of other CMS/CMFs and nothing came close. The balance of features vs flexibility is spot on. The only issue that irks me slightly is the database vs. code issue - i.e. the database effectively contains business logic as well as data which does create maintenance issues (some careful thought is required as to what logic should be capable of being amended in the admin and what is best to put in the code if possible - for example I moved all Hanna code logic into a separate php script). I suspect the recent debate about front-end enhancements is precisely because PW is seen as a bit more lacking in that department.

[bernhard]

4 hours ago, MarkE said:

The only issue that irks me slightly is the database vs. code issue - i.e. the database effectively contains business logic as well as data which does create maintenance issues

I hope I don't bother anybody asking that over and over again ? Have you tried RockMigrations?

[MarkE]

2 hours ago, bernhard said:

Have you tried RockMigrations?

? I was going to mention the 2 migrations modules, but they are not part of the core. I have taken a look at RockMigrations and will do again - it's just that my last migration was too messy to use as an initial attempt. I will try using it for a simple migration first, to get the hang of it. 

[Kiwi Chris]

On 2/25/2021 at 11:27 PM, MarkE said:

I think PW is admirably suited for this type of application - I researched a whole bunch of other CMS/CMFs and nothing came close. The balance of features vs flexibility is spot on. The only issue that irks me slightly is the database vs. code issue - i.e. the database effectively contains business logic as well as data which does create maintenance issues (some careful thought is required as to what logic should be capable of being amended in the admin and what is best to put in the code if possible - for example I moved all Hanna code logic into a separate php script). I suspect the recent debate about front-end enhancements is precisely because PW is seen as a bit more lacking in that department.

I've looked at quite a few other CMS/CMFs and most of them fall down in some respect compared to ProcessWire, whether it be documentation, community, or ease of use. That doesn't make ProcessWire perfect, and given the diversity of usage scenarios there will always be things that could be done differently.

The admin certainly can be a mixed blessing. It's so easy to use, that it's tempting to rely on it at least to start with, but then when you need to maintain changes, it can get complicated.

I think what could be quite nice, if it's possible, would be to have the ability to enable change tracking for templates and fields, when they're modified via the admin. It's already possible to export and import field and template definitions as JSON, so combine this with the ability to track what's changed, and something like @bernhard's migrations module, and it could be easier to combine the ease of using ProcessWire admin to make changes, and the need to be able to roll out changes from development to production sites.