Self-reference

[frnco]

Trying to self-reference on a oneToMany relationship ("Parent" column) breaks the area with ER_NONUNIQ_TABLE.

[simov]

Can you paste the part of your config you are working on, also what your tables configuration is, and what you are trying to achieve?

[frnco]

Of course. I need to have categories, many of them with sub-categories. I'm doing this through a column called "parent" which basically has the ID of another category. If this column's value is 0 this category has no Parent.

For the settings of this table as it is today, here goes:

"categories": {
    "slug": "categories",
    "table": {
        "name": "categories",
        "pk": "id",
        "verbose": "Categories"
    },
    "columns": [
        {
            "name": "id",
            "verbose": "id",
            "control": {
                "text": true
            },
            "type": "int(11)",
            "allowNull": true,
            "defaultValue": null,
            "listview": {
                "show": false
            },
            "editview": {
                "show": true
            }
        },
        {
            "name": "name",
            "verbose": "Name",
            "control": {
                "text": true
            },
            "type": "varchar(255)",
            "allowNull": false,
            "defaultValue": null,
            "listview": {
                "show": true
            },
            "editview": {
                "show": true
            }
        },
        {
            "name": "parent",
            "verbose": "parent",
            "control": {
                "select": true
            },
            "oneToMany": {
                "table": "categories",
                "pk": "id",
                "columns": [
                    "nome"
                ]
            },
            "type": "int(11)",
            "allowNull": true,
            "defaultValue": 0,
            "listview": {
                "show": true
            },
            "editview": {
                "show": true
            }
        },
    ],
    "mainview": {
        "show": true
    },
    "listview": {
        "order": {},
        "page": 25
    },
    "editview": {
        "readonly": false
    }
}

[theRichu]

I just came across this problem :(
How can it solve?

[simov]

The query generated for the listview looks like this

SELECT `tbl`.`id` AS __pk,`tbl`.`name`,
GROUP_CONCAT(DISTINCT CONCAT_WS(' ',CAST(`tbl`.`name` AS CHAR))) AS `parent` 
FROM `tbl`  
LEFT JOIN `tbl` ON `tbl`.`parent` = `tbl`.`id`   
GROUP BY `tbl`.`id`  ORDER BY `tbl`.`id` ASC LIMIT 0,25 ;

but in case of a self reference, it should be transformed to this

SELECT `tbl`.`id` AS __pk,`tbl`.`name`,
GROUP_CONCAT(DISTINCT CONCAT_WS(' ',CAST(`tbl2`.`name` AS CHAR))) AS `parent` 
FROM `tbl`  
LEFT JOIN `tbl` `tbl2` ON `tbl2`.`parent` = `tbl`.`id`   
GROUP BY `tbl`.`id`  ORDER BY `tbl`.`id` ASC LIMIT 0,25 ;

the only difference is the alias

I'll definitely think about it

[simov]

@ellahn @theRichu just pushed to master, if you are anxious to try it out just pull (it's not published to npm yet)