For Invoice Ninja we wanted each account to be able to use friendly URLs where their Ids would increment.
This would mean the URL for the first client they create would be https://www.invoiceninja.com/clients/1
Many accounts are stored in the same database so we’re not able to use the auto-incrementing Id field. The solution we’re using is to have all classes which store user data (ie, clients, invoices, payments, etc) extend the EntityModel
class. To create a new instance you call the createNew()
method.
Here are the benefits of this approach:
- Context is tracked: The account and user the record belong to is automatically set. If an item is created by the system (ie, an automatically sent recurring invoice) the context can be inherited from an existing record.
- Out of the box security: When setting foreign key values we use
getPrivateId()
to resolve the public id to a private table Id. This prevents the user from hand modifying the HTML to reference another account’s data. - Easy scoping: Using the
scope()
method we’re able to easily load data filtered by the account. For exampleClient::scope()->get()
returns all of the accounts clients, whileClient::scope($publicId)->get()
returns a client using its public Id.
And finally the code…
class EntityModel extends Eloquent { protected $softDelete = true; public $timestamps = true; protected $hidden = ['id', 'created_at', 'deleted_at', 'updated_at']; public static function createNew($parent = false) { $className = get_called_class(); $entity = new $className(); if ($parent) { $entity->user_id = $parent->user_id; $entity->account_id = $parent->account_id; } else if (Auth::check()) { $entity->user_id = Auth::user()->id; $entity->account_id = Auth::user()->account_id; } else { Utils::fatalError(); } $lastEntity = $className::withTrashed()->scope(false, $entity->account_id)->orderBy('public_id', 'DESC')->first(); if ($lastEntity) { $entity->public_id = $lastEntity->public_id + 1; } else { $entity->public_id = 1; } return $entity; } public static function getPrivateId($publicId) { $className = get_called_class(); return $className::scope($publicId)->pluck('id'); } public function scopeScope($query, $publicId = false, $accountId = false) { if (!$accountId) { $accountId = Auth::user()->account_id; } $query->whereAccountId($accountId); if ($publicId) { if (is_array($publicId)) { $query->whereIn('public_id', $publicId); } else { $query->wherePublicId($publicId); } } return $query; } }
I’m sure there are other ways to tackle this problem. Let me know in the comments or on twitter @hillelcoren if you have a different solution.
I was able to find good info from ylur articles.
what is the benefit of public_id column in invoice ninja database?
It enables each company in a multi-tentant setup to use URLs like /invoices/1/edit
you mean it is like id, but we use public_id instead of id in URLs.
is it right?
Correct, the app resolves the public_id to the id.
Note: in the next version of the app we’re no longer using the approach, instead we’re hashing the id field so the URL is something like /invoices/a7u7SC/edit