{"result":{"depiction_url":null,"edges":{"references":{"objects":[{"created":"2020-05-30T05:47:53Z","object_id":{"id":1638,"is_a":["text","documentation","developerguide"],"name":"doc_developerguide_search","title":{"_type":"trans","tr":{"en":"Search"}},"uri":"https:\/\/zotonic.com\/id\/1638"},"seq":1000000},{"created":"2020-05-30T05:47:53Z","object_id":{"id":1276,"is_a":["text","documentation","developerguide"],"name":"doc_developerguide_resources","title":"Resources","uri":"https:\/\/zotonic.com\/id\/1276"},"seq":1000000}],"predicate":{"id":332,"is_a":["meta","predicate"],"name":"references","title":{"_type":"trans","tr":{"en":"References"}},"uri":"https:\/\/zotonic.com\/id\/references"}},"relation":{"objects":[{"created":"2020-05-30T05:47:53Z","object_id":{"id":1638,"is_a":["text","documentation","developerguide"],"name":"doc_developerguide_search","title":{"_type":"trans","tr":{"en":"Search"}},"uri":"https:\/\/zotonic.com\/id\/1638"},"seq":1000000},{"created":"2020-05-30T05:47:53Z","object_id":{"id":1282,"is_a":["text","documentation","cookbook"],"name":"doc_cookbook_pivot_templates","title":"Pivot Templates","uri":"https:\/\/zotonic.com\/id\/1282"},"seq":1000000}],"predicate":{"id":303,"is_a":["meta","predicate"],"name":"relation","title":{"_type":"trans","tr":{"nl":"Relatie","en":"Relation"}},"uri":"http:\/\/purl.org\/dc\/terms\/relation"}}},"id":1281,"is_a":["text","documentation","cookbook"],"links":[{"rel":"self","target":"https:\/\/zotonic.com\/.zotonic\/websub\/topic\/1281"},{"rel":"hub","target":"https:\/\/zotonic.com\/.zotonic\/websub"}],"medium":null,"medium_url":null,"name":"doc_cookbook_custom_pivot","page_url":{"en":"https:\/\/zotonic.com\/cookbook\/1281\/custom-pivots","x-default":"https:\/\/zotonic.com\/cookbook\/1281\/custom-pivots"},"preview_url":null,"resource":{"body":"<div>\n            \n  <div class=\"section\">\n\n<aside class=\"admonition seealso\">\n<p class=\"first admonition-title\">See also<\/p>\n<p class=\"last\"><a class=\"reference internal\" href=\"\/id\/doc_developerguide_search#custompivot\"><span class=\"std std-ref\">pivot.name<\/span><\/a> search argument for filtering on custom pivot columns.<\/p>\n<\/aside>\n<aside class=\"admonition seealso\">\n<p class=\"first admonition-title\">See also<\/p>\n<p class=\"last\"><a class=\"reference internal\" href=\"\/id\/doc_cookbook_pivot_templates#cookbook-pivot-templates\"><span class=\"std std-ref\">Pivot Templates<\/span><\/a> to change the content of regular pivot columns and search texts.<\/p>\n<\/aside>\n<p><a class=\"reference internal\" href=\"\/id\/doc_developerguide_search#guide-datamodel-query-model\"><span class=\"std std-ref\">Search<\/span><\/a> can only sort and filter on\n<a class=\"reference internal\" href=\"\/id\/doc_developerguide_resources#guide-datamodel-resources\"><span class=\"std std-ref\">resources<\/span><\/a> that actually have a database\ncolumn. Zotonic’s resources are stored in a serialized form. This\nallows you to very easily add any property to any resource but\nyou cannot sort or filter on them until you make database columns\nfor these properties.<\/p>\n<p>The way to take this on is using the “custom pivot” feature. A custom\npivot table is an extra database table with columns in which the props\nyou define are copied, so you can filter and sort on them.<\/p>\n<p>Say you want to sort on a property of the resource called <code class=\"docutils literal notranslate\"><span class=\"pre\">requestor<\/span><\/code>.<\/p>\n<p>Create (and export!) an <code class=\"docutils literal notranslate\"><span class=\"pre\">init\/1<\/span><\/code> function in your site where you define a custom pivot table:<\/p>\n<div class=\"highlight-erlang notranslate\"><div class=\"highlight\"><pre><span><\/span><span class=\"nf\">init<\/span><span class=\"p\">(<\/span><span class=\"nv\">Context<\/span><span class=\"p\">)<\/span> <span class=\"o\">-&gt;<\/span>\n    <span class=\"nn\">z_pivot_rsc<\/span><span class=\"p\">:<\/span><span class=\"nf\">define_custom_pivot<\/span><span class=\"p\">(<\/span><span class=\"n\">pivotname<\/span><span class=\"p\">,<\/span> <span class=\"p\">[{<\/span><span class=\"n\">requestor<\/span><span class=\"p\">,<\/span> <span class=\"s\">&quot;varchar(80)&quot;<\/span><span class=\"p\">}],<\/span> <span class=\"nv\">Context<\/span><span class=\"p\">),<\/span>\n    <span class=\"n\">ok<\/span><span class=\"p\">.<\/span>\n<\/pre><\/div>\n<\/div>\n<p>The new table will be called <code class=\"docutils literal notranslate\"><span class=\"pre\">pivot_&lt;pivotname&gt;<\/span><\/code>. When you change the column\nnames in the table definition, the table will be recreated and <strong>the data inside will be lost<\/strong>.<\/p>\n<p>To fill the pivot table with data when a resource gets saved, create a notification\nlistener function <code class=\"docutils literal notranslate\"><span class=\"pre\">observe_custom_pivot\/2<\/span><\/code>:<\/p>\n<div class=\"highlight-erlang notranslate\"><div class=\"highlight\"><pre><span><\/span><span class=\"nf\">observe_custom_pivot<\/span><span class=\"p\">(<\/span><span class=\"nl\">#custom_pivot<\/span><span class=\"p\">{<\/span> <span class=\"n\">id<\/span> <span class=\"o\">=<\/span> <span class=\"nv\">Id<\/span> <span class=\"p\">},<\/span> <span class=\"nv\">Context<\/span><span class=\"p\">)<\/span> <span class=\"o\">-&gt;<\/span>\n    <span class=\"nv\">Requestor<\/span> <span class=\"o\">=<\/span> <span class=\"nn\">m_rsc<\/span><span class=\"p\">:<\/span><span class=\"nf\">p<\/span><span class=\"p\">(<\/span><span class=\"nv\">Id<\/span><span class=\"p\">,<\/span> <span class=\"n\">requestor<\/span><span class=\"p\">,<\/span> <span class=\"nv\">Context<\/span><span class=\"p\">),<\/span>\n    <span class=\"p\">{<\/span><span class=\"n\">pivotname<\/span><span class=\"p\">,<\/span> <span class=\"p\">[{<\/span><span class=\"n\">requestor<\/span><span class=\"p\">,<\/span> <span class=\"nv\">Requestor<\/span><span class=\"p\">}]}.<\/span>\n<\/pre><\/div>\n<\/div>\n<p>This will fill the ‘requestor’ property for every entry in your\ndatabase, when the resource is pivoted.<\/p>\n<p>Recompile your site and restart it (so the <code class=\"docutils literal notranslate\"><span class=\"pre\">init<\/span><\/code> function is called)\nand then in the admin under ‘System’ -&gt; ‘Status’ choose ‘Rebuild\nsearch indexes’. This will gradually fill the new pivot table. Enable\nthe logging module and choose “log” in the admin menu to see the pivot\nprogress. Once the table is filled, you can use the pivot table to do\nsorting and filtering.<\/p>\n<p>To sort on ‘requestor’, do the following:<\/p>\n<div class=\"highlight-django notranslate\"><div class=\"highlight\"><pre><span><\/span><span class=\"cp\">{%<\/span> <span class=\"k\">with<\/span> <span class=\"nv\">m.search.paged<\/span><span class=\"o\">[{<\/span><span class=\"nv\">query<\/span> <span class=\"nv\">cat<\/span><span class=\"o\">=<\/span><span class=\"s1\">&#39;foo&#39;<\/span> <span class=\"nv\">sort<\/span><span class=\"o\">=<\/span><span class=\"s1\">&#39;pivot.pivotname.requestor&#39;<\/span><span class=\"o\">}]<\/span> <span class=\"k\">as<\/span> <span class=\"nv\">result<\/span> <span class=\"cp\">%}<\/span><span class=\"x\"><\/span>\n<\/pre><\/div>\n<\/div>\n<p>Or you can filter on it:<\/p>\n<div class=\"highlight-django notranslate\"><div class=\"highlight\"><pre><span><\/span><span class=\"cp\">{%<\/span> <span class=\"k\">with<\/span> <span class=\"nv\">m.search.paged<\/span><span class=\"o\">[{<\/span><span class=\"nv\">query<\/span> <span class=\"nv\">filter<\/span><span class=\"o\">=[<\/span><span class=\"s2\">&quot;pivot.pivotname.requestor&quot;<\/span><span class=\"o\">,<\/span> <span class=\"p\">`<\/span><span class=\"o\">=<\/span><span class=\"p\">`<\/span><span class=\"o\">,<\/span> <span class=\"s2\">&quot;hello&quot;<\/span><span class=\"o\">]}]<\/span>\n   <span class=\"k\">as<\/span> <span class=\"nv\">result<\/span> <span class=\"cp\">%}<\/span><span class=\"x\"><\/span>\n<\/pre><\/div>\n<\/div>\n<\/div>\n\n\n           <\/div>","category_id":{"id":318,"is_a":["meta","category"],"name":"cookbook","title":{"_type":"trans","tr":{"en":"Cookbook"}},"uri":"https:\/\/test.zotonic.com\/id\/318"},"content_group_id":{"id":339,"is_a":["meta","content_group"],"name":"default_content_group","title":{"_type":"trans","tr":{"en":"Default Content Group"}},"uri":"https:\/\/zotonic.com\/id\/default_content_group"},"created":"2020-05-30T05:47:16Z","creator_id":{"id":336,"is_a":["person","robot"],"name":"gitbot","title":"Git","uri":"https:\/\/zotonic.com\/id\/336"},"github_url":"https:\/\/github.com\/zotonic\/zotonic\/tree\/master\/doc\/cookbook\/custom-pivot.rst","is_authoritative":true,"is_dependent":false,"is_featured":false,"is_protected":false,"is_published":true,"is_unfindable":false,"language":["en"],"modified":"2022-02-15T10:01:32Z","modifier_id":{"id":336,"is_a":["person","robot"],"name":"gitbot","title":"Git","uri":"https:\/\/zotonic.com\/id\/336"},"name":"doc_cookbook_custom_pivot","pivot_geocode":null,"pivot_location_lat":null,"pivot_location_lng":null,"privacy":0,"publication_end":"9999-06-01T00:00:00Z","publication_start":"2022-02-15T10:01:32Z","slug":"custom-pivots","title":"Custom pivots","title_slug":"custom-pivots","tz":"UTC","uri":null,"version":18,"visible_for":0},"uri":"https:\/\/zotonic.com\/id\/1281","uri_template":"https:\/\/zotonic.com\/id\/:id","websub":{"hub":"https:\/\/zotonic.com\/.zotonic\/websub","topic":"https:\/\/zotonic.com\/.zotonic\/websub\/topic\/1281"}},"status":"ok"}