From Translations to Multi Dimension Entities

Content management is more than just adding a text editor to your project. Initially, you may only need a basic text editor for your Entity. However, as your project grows, you might require support for multiple languages, and when drafting or versioning come into play, things can get tricky.

In this talk, I will provide a quick introduction to different ways of data modeling and the libraries available to meet these needs. I'll also share a brief history of Sulu's content storage and explain why we decided to redesign the entire content storage system for a better future.

You'll learn how to evolve from a simple Doctrine entity to a translatable entity, and finally, to a multi-dimensional entity that supports multiple languages, drafting, versioning, and more. These concepts are reusable even if you're not using our beloved CMS.

Transcript

1. About me Name: Alexander Schranz Workplace: Sulu (sulu.io) Tools: PHP, Symfony, Twig, Elasticsearch, Redis ReactJS, MobX, React Native (Expo) Experience: Web Developer since 2012 Certified Symfony 5 Expert OSS: Symfony Redis Messenger implementation SEAL – Search Engine Abstraction Layer https:/ /github.com/PHP-CMSIG/search @alexander-schranz, @alex_s_
2. From Translations to Multi Dimension Entities Storing data that is draftable, localized, and versionized.
3. Basic Entity with TextEditor id: int description: text Adding support for multiple languages Page id: int Page id: int locale: string description: text PageTranslation 1 - n
4. Content Management is more as a Text Editor • Content should be separated from its presentation • Content should be translatable • Content should be draft and publishable • Content should be versionized
5. Adding Drafting / Publishing Mechanism id: int Page id: int locale: string description: text isPublished: bool PageTranslation 1 - n id: int Page id: int locale: string description: text stage: string PageTranslation 1 - n vs.
6. id: int Page id: int version: int PageVersion 1 - n id: int locale: string description: text PageVersionTranslation 1 - n id: int Page id: int locale: string PageTranslation 1 - n id: int version: int description: text PageTranslationVersion 1 - n vs. Add Versioning
7. Vienna
8. History “Sulu” Content Storage OCK / iCubus ( 1997 – 2008) Java / ASP XML based Zoolu (2008 – ca. 2013) Relational Database Store MySQL via PHP / Zend 1 Sulu 1 / 2 ( 2013 – 2024 ) Unstructured Content Store via PHPCR Sulu 3 ( 2025 ) Unstructured Content Store via JSON database fields
9. What we learned from Zoolu • Storing Content in relational way is a lot additional work for developers. • People don’t want to write SQL upgrades to add new fields to the CMS and make errors when deploying them. • Content structure / metadata should be part of your Git Repository and not live in your database. Deploy content changes with code changes directly. • Changing structure / metadata needs to be easy as copy a few lines of code. • Defining content structure needs to be easy for Backend and Frontend developers.
10. What we learned from PHPCR • ✅ Unstructured content works ◦ not all things need to be stored via relations and foreign keys • ✅ It did bring out of the box support of lot of content features ◦ Tree Structure / Multi Language / Versioning / References • ✅ Lot of features with less effort • ❌ Using the same single item to store multiple language can be a bottleneck ◦ Loading a PHPCR Node loads includes all translations of that Node • ❌ Installing additional service like Jackrabbit is something that people avoid ◦ even if it has advantages with additional features like versioning • ❌ Own Query Language (SQL2) make things harder • ❌ Looking at the jackalope tables which stores content in XML is hard to debug
11. Looking for alternatives for Sulu 3.0 (Neos Content Storage) • ✅ Brings lot of common content features out of the box • ❌ Is not based on doctrine/orm what most of the Symfony Community is familiar with • ❌ Complexity of Event-Sourcing and CQRS is may be hard to understand for newcomers
12. Requirements for own content storage • 🟡 No overfetching (load only content / languages we require) • 🟡 No additional service requirement for full features (versioning, drafting …) • 🟡 Pages / Articles should just be another Doctrine ORM Entity • 🟡 Any Entity should have the possibility to have content • 🟡 Query content should be able to be done with SQL and DQL queries
13. Content has different Variants What Effects our Dimensions Localization ( de, en, en_GB … ) Stage ( edit / live ) Version
14. Put content into a Dimension Entity id: int Page id: int content: json PageDimension 1 - n locale: string|null stage: string version: int Dimension Attributes
15. Put content into a Dimension Entity PageDimension id locale content 1 de {“description”: “...”} 2 en {“description”: “...”} 3 NULL {“key”: “...”}
16. Put content into a Dimension Entity PageDimension id locale stage content 1 de edit {“description”: “...”} 2 en edit {“description”: “...”} 3 NULL edit {“key”: “...”} 4 de live {“description”: “...”} 5 en live {“description”: “...”} 6 NULL live {“key”: “...”}
17. Put content into a Dimension Entity PageDimension id locale stage version content 1 de edit 0 {“description”: “...”} 2 en edit 0 {“description”: “...”} 3 NULL edit 0 {“key”: “...”} 7 de live 0 {“description”: “...”} 8 en live 0 {“description”: “...”} 4 de live 1726605235 {“description”: “...”} 5 en live 1726605235 {“description”: “...”} 6 NULL live 1726605235 {“key”: “...”}
18. WHERE (dimensionAttribute IS NULL OR dimensionAttribute = :dimensionAttribute) PageDimension id locale stage version content 1 de edit 0 {“description”: “...”} 2 en edit 0 {“description”: “...”} 3 NULL edit 0 {“key”: “...”} 7 de live 0 {“description”: “...”} 8 en live 0 {“description”: “...”} 9 NULL live 0 {“key”: “...”} 4 de live 1726605235 {“description”: “...”} 5 en live 1726605235 {“description”: “...”} 6 NULL live 1726605235 {“key”: “...”}
19. WHERE (locale IS NULL OR locale = :locale) AND (stage IS NULL OR stage = :stage) AND (version IS NULL OR version = :version) :locale = “de” :stage = “edit” :version = 0 PageDimension id locale stage version content 1 de edit 0 {“description”: “...”} 2 en edit 0 {“description”: “...”} 3 NULL edit 0 {“key”: “...”} 7 de live 0 {“description”: “...”} 8 en live 0 {“description”: “...”} 9 NULL live 0 {“key”: “...”} 4 de live 1726605235 {“description”: “...”} 5 en live 1726605235 {“description”: “...”} 6 NULL live 1726605235 {“key”: “...”}
20. WHERE (locale IS NULL OR locale = :locale) AND (stage = :stage) AND (version = :version) :locale = “en” :stage = “edit” :version = 0 PageDimension id locale stage version content 1 de edit 0 {“description”: “...”} 2 en edit 0 {“description”: “...”} 3 NULL edit 0 {“key”: “...”} 7 de live 0 {“description”: “...”} 8 en live 0 {“description”: “...”} 9 NULL live 0 {“key”: “...”} 4 de live 1726605235 {“description”: “...”} 5 en live 1726605235 {“description”: “...”} 6 NULL live 1726605235 {“key”: “...”}
21. WHERE (locale IS NULL OR locale = :locale) AND (stage = :stage) AND (version = :version) :locale = “de” :stage = “live” :version = 1726605235 PageDimension id locale stage version content 1 de edit 0 {“description”: “...”} 2 en edit 0 {“description”: “...”} 3 NULL edit 0 {“key”: “...”} 7 de live 0 {“description”: “...”} 8 en live 0 {“description”: “...”} 9 NULL live 0 {“key”: “...”} 4 de live 1726605235 {“description”: “...”} 5 en live 1726605235 {“description”: “...”} 6 NULL live 1726605235 {“key”: “...”}
22. WHERE (locale IS NULL OR locale = :locale) AND (stage = :stage) AND (version = :version) :locale = “de” :stage = “live” :version = 0 PageDimension id locale stage version content 1 de edit 0 {“description”: “...”} 2 en edit 0 {“description”: “...”} 3 NULL edit 0 {“key”: “...”} 7 de live 0 {“description”: “...”} 8 en live 0 {“description”: “...”} 9 NULL live 0 {“key”: “...”} 4 de live 1726605235 {“description”: “...”} 5 en live 1726605235 {“description”: “...”} 6 NULL live 1726605235 {“key”: “...”}
23. Avoid bringing complexity to End Users Keep things as simple as possible for the End Users and Developers. • Administration of Content is always on Version 0 • No difference between localized and unlocalized data (UI need handle UX) • Publishing is just a Workflow trigger always editing the draft stage
24. Merge Dimension (used for rendering HTML or APIs) More specified dimension (locale “de” overrides locale NULL) JSON fields get merged together PageDimension id locale stage version content 1 de live 0 {“description”: “...”} 3 NULL live 0 {“key”: “...”} de live 0 {“key”: “...”, “description”: “...”}
25. API Response { “id”: 1, “locale”: “de”, “key”: “...”, “description”: “...” } Rendering Content <div id=”{{ content.key }}”> {{ content.description|raw }} </div> Always edit Version 0 and Stage Draft allows us not to bring the dimension complexity to end users. Always render Version 0 and Stage Live allows us not to bring the dimension complexity to end users.
26. Concept of dimensions are extendable For example we could introduce workspaces for users. PageDimension id locale stage version workspace content 1 de edit 0 default {“description”: “...”} 2 en edit 0 default {“description”: “...”} 3 NULL edit 0 default {“key”: “...”} 4 en edit 0 alex {“description”: “Work”} 5 NULL edit 0 alex {“key”: “WIP”}
27. Add additional columns for better query performance Combining JSON fields with other columns, we can query on `authorId = 1` to perform indexed based queries. PageDimension id locale stage version content authorId 1 de edit 0 {“description”: “...”} 1 2 en edit 0 {“description”: “...”} 2 3 NULL edit 0 {“key”: “...”} NULL
28. Add structured related things to main entity id: int Page id: int content: json PageDimension 1 - n locale: string|null stage: string version: int left: int right: int depth: int parentId: int Using a nested set to produce a tree structure of pages on the main entity.
29. Your Domain Logic can live side by side with Content id: int Product id: int content: json ProductDimension 1 - n locale: string|null stage: string version: int id: int locale: string title: string ProductTranslation 1 - n Not all your data needs to be draftable, versionized … Build your business domain objects the way that makes most sense for you. Business Logic Unstructured Content
30. Copy Language $contentCopier->copy( $page, [‘locale’ => ‘de’], // default stage = ‘edit’ and default ‘version’ = 0 [‘locale’ => ‘en’], ); Content Copier rules different workflows PageDimension id locale stage version content 1 de edit 0 {“description”: “...”} 2 NULL edit 0 {“key”: “...”} 3 en edit 0 {“description”: “...”}
31. $contentCopier->copy( $page, [‘locale’ => ‘de’], // default stage = ‘edit’ and default ‘version’ = 0 [‘locale’ => ‘de’, ‘stage’ => ‘live’], ); Publish Content Content Copier rules different workflows PageDimension id locale stage version content 1 de edit 0 {“description”: “...”} 2 NULL edit 0 {“key”: “...”} 3 de live 0 {“description”: “...”} 4 NULL live 0 {“key”: “...”}
32. $contentCopier->copy( $page, [‘locale’ => ‘de’], // default stage = ‘edit’ and default ‘version’ = 0 [‘locale’ => ‘de’, ‘version’ => time()], ); Create Version Content Copier rules different workflows PageDimension id locale stage version content 1 de edit 0 {“description”: “New”} 2 NULL edit 0 {“key”: “new”} 3 de live 0 {“description”: “Old”} 4 NULL live 0 {“key”: “old”}
33. $contentCopier->copy( $page, [‘locale’ => ‘en’], // default stage = ‘edit’ and default ‘version’ = 0 [‘locale’ => ‘de’, ‘version’ => time()], ); Create Version Content Copier rules different workflows PageDimension id locale stage version content 1 de edit 0 {“description”: “New”} 2 NULL edit 0 {“key”: “new”} 3 de live 0 {“description”: “Old”} 4 NULL live 0 {“key”: “old”} 5 de live 1735685999 {“description”: “Old”} 6 NULL live 1735685999 {“key”: “old”}
34. $contentCopier->copy( $page, [‘locale’ => ‘en’], // default stage = ‘edit’ and default ‘version’ = 0 [‘locale’ => ‘de’, ‘version’ => time()], ); Create Version Content Copier rules different workflows PageDimension id locale stage version content 1 de edit 0 {“description”: “New”} 2 NULL edit 0 {“key”: “new”} 3 de live 0 {“description”: “New”} 4 NULL live 0 {“key”: “new”} 5 de live 1735685999 {“description”: “Old”} 6 NULL live 1735685999 {“key”: “old”}
35. $contentCopier->copy( $page, [‘locale’ => ‘de’], // default stage = ‘edit’ and default ‘version’ = 0 [‘locale’ => ‘de’, ‘version’ => time()], ); Create Version Content Copier rules different workflows Copy Language $contentCopier->copy( $page, [‘locale’ => ‘de’], // default stage = ‘edit’ and default ‘version’ = 0 [‘locale’ => ‘en’], ); $contentCopier->copy( $page, [‘locale’ => ‘de’], // default stage = ‘edit’ and default ‘version’ = 0 [‘locale’ => ‘de’, ‘stage’ => ‘live’], ); Publish Content
36. Connect to Symfony Workflow component $workflow->apply($pageDimension, ‘publish’); Workflow Subscribers call the Content Copier service to apply changes. So the published dimension and version dimension is created.
37. Conclusion • ✅ No overfetching (load only content / languages we require) • ✅ No additional service requirement for full features (versioning, drafting …) • ✅ Pages / Articles are just another Doctrine ORM Entity • ✅ Any Entity has the possibility to be enhanced by content • ✅ Query content can be done with basic SQL and DQL knowledge
38. Is it battle tested / used in production? • “Eating your own dog food” First version we deployed to production 3 years ago Since we improved the storage and optimized it. • Using it in different own client projects already over the last years. E.g. Recipes Platform with ~120 000 dimensions objects • Partners and Friends of Sulu CMS are also using it already for their projects. E.g. Sylius Ecommerce Shop with ~180 000 dimensions with Sulu Content • Sulu 3.0 coming next year with it
39. Benchmarks
40. Time for your questions Github: @alexander-schranz Bluesky: @alexander-schranz.bsky.social X / Twitter: @alex_s_ Mastodon: alex_s_@mastodon.social Questions?
41. Thank you for listening Want to talk about: - Sulu or the new Content Storage with Dimensions - SEAL – Search Engine Abstraction Layer - Redis Messenger integration - or other things Catch me at the hallway or during breaks.