Get database records with custom JSON structure

[schloram]

Hi, in our current project we have a custom content element where we can list pages by given page types (doktype). To get these pages we use a DatabaseQueryProcessor currently. Now we want to use the headless extension and we we need a way to render this content element as JSON. The output should be something like:

...
{
    "content": {
        "pages": [
            {
                "id": 1,
                "pid": 0,
                ... SAME STRUCTURE AS "lib.page" FROM EXT:headless
            },
            { ... }
        ]
    }
}

Inside pages every page object should have the same structure as in lib.page.

Do you think it’s a good idea to write an own DatabaseQueryProcessor to get data from a specific table in a custom structure? Or is it better to use the CONTENT/RECORDS Content Objects for this (combined with the JSON Content Object)? And do you think this functionality should be added to EXT:headless?

best, R

[lukaszuznanski]

Hi,

we are currently using RECORDS for this kind of 'nested' rendering.

fields {
        content {
            fields {
                boxes = COA
                boxes {
                    stdWrap.innerWrap = [|]
                    10 = RECORDS
                    10 {
                        source.field = tx_extension_box_item
                        tables = tx_extension_box_item
                        conf.tx_extension_box_item.stdWrap.wrap = |###BREAK###
                    }
                    stdWrap.split {
                        token = ###BREAK###
                        cObjNum = 1 |*|2|*| 3
                        1.current = 1
                        1.stdWrap.wrap = |

                        2.current = 1
                        2.stdWrap.wrap = ,|

                        3.current = 1
                        3.stdWrap.wrap = |
                    }
                }
            }
        }
    }

then, tx_extension_box_item has it's own typoscript JSON template

I will add this example to documentation.

[schloram]

But doesn't this also just output all the database tables?

[lukaszuznanski]

Why? I specify table, specify source field, and then i specify tx_extension_box_item in typoscript as JSON and render all required properties

[schloram]

Thanks for your example.

But doesn't this also just output all the database tables?

Sorry I meant "columns" not "tables" :)

How do you "specify tx_extension_box_item in typoscript"? With tt_content.*?

I was especially thinking about a generic way of getting the fields of a table in a defined JSON structure without always using CONTENT/RECORDS with stdWrap.wrap, stdWrap.split and stdWrap.innerWrap.

For now I use the following for getting the pages (but it feels quite hacky):

fields {
    content {
        fields {
            pages = COA
            pages {
                stdWrap.innerWrap = [|]
                10 = CONTENT
                10 {
                    table = pages
                    select {
                        pidInList.field = pages
                        orderBy = sorting
                        recursive.field = recursive
                    }
                    renderObj =< lib.page
                    renderObj.stdWrap.wrap = |###BREAK###
                }

                stdWrap.split {
                    token = ###BREAK###
                    cObjNum = 1 |*|2|*| 3
                    1.current = 1
                    1.stdWrap.wrap = |

                    2.current = 1
                    2.stdWrap.wrap = ,|

                    3.current = 1
                    3.stdWrap.wrap = |
                }
            }
        }
    }
}

best, Ramón

[lukaszuznanski]

I specify tx_extension_box_item without tt_content as it's not tt_content

tx_extension_box_item =< lib.contentElement
tx_extension_box_item {
    fields {
        content {
            fields {
                header = TEXT
                header {
                    field = header
                }
                bodytext = TEXT
                bodytext {
                    field = bodytext
                    parseFunc =< lib.parseFunc_links
                }
                link = TEXT
                link {
                    field = link
                    typolink {
                        parameter {
                            field = link
                        }
                        returnLast = url
                    }
                }
            }
        }
    }
}

I agree that we should find some simpler way to render those nested properties

[schloram]

Ah okay. This is what was confusing me :) Thanks for clearing it up. 👍

What about a modified DatabaseQueryProcessor where you can additionally define fields to to map the JSON fields to database columns.

Something like:

fields {
    myRecords {
        dataProcessing {
            10 = FriendsOfTYPO3\Headless\DataProcessing\JsonRecordsProcessor
            10 {
                pidInList =
                recursive =
                selectFields =
                languageField =
                where = 
                ...

                # fields with stdWrap applied (without defining Content Object)
                fields {
                    myCustomLink {
                        field = link
                        typolink {
                            parameter {
                                field = link
                            }
                            returnLast = url
                        }
                    }

                    myCustomId.field = uid

                    myCustomText.field = bodytext
                }
            }
        }
    }
}

Output:

{
    "myRecords": [
        {
            "myCustomLink": "/target/page",
            "myCustomId": 2,
            "myCustomText": "my bodytext"
        },
        {...},
        {...}
    ]
}

Just an idea. :)

[lukaszuznanski]

Personally I like this idea, because actual solution is a bit tricky (I don't like this part with |###BREAK###), solution with DataProcessor looks much cleaner.

[schloram]

Thanks for your feedback. I will open a PR for this issue soon.

[lukaszuznanski]

I think JsonRecordsProcessor is great idea. We should create one on upcoming sprint.