2 steps relations?

[henninghall]

I am trying to attach relation data which works great except when it is "relations relation". In the example it should be possible to use mysql_crud_api_tranform() to achieve this.

I want to achieve this:

{ "posts": [ { "id": "1", "post_tags": [ { "id": "1", "post_id": "1", "tag_id": "1", "tags": [ { "id": "1", "name": "funny" } ] }, { "id": "2", "post_id": "1", "tag_id": "2", "tags": [ { "id": "2", "name": "important" } ] } ], "comments": [ { "id": "1", "post_id": "1", "message": "great" }, { "id": "2", "post_id": "1", "message": "fantastic" } ], "user_id": "1", "category_id": "1", "categories": [ { "id": "1", "name": "anouncement" } ], "content": "blog started" } ] }

But the result is :

{ "posts": [ { "id": "1", "post_tags": [ { "id": "1", "post_id": "1", "tag_id": "1", }, { "id": "2", "post_id": "1", "tag_id": "2", "tags": [ { "id": "2", "name": "important" } ] } ], "comments": [ { "id": "1", "post_id": "1", "message": "great" }, { "id": "2", "post_id": "1", "message": "fantastic" } ], "user_id": "1", "category_id": "1", "categories": [ { "id": "1", "name": "anouncement" } ], "content": "blog started" } ] "tags": [ { "id": "1", "name": "funny" } ] }

Do you know why or how i can fix this? If anyone is achieving the expected result, please provide an example of how you use mysql_crud_api_tranform(). Thanks for a great api!

[mevdschee]

Is that really the result? That is worrying as it is not even valid JSON. Can you confirm this?

How did you get this result? Please provide as much details as possible.

[Disane87]

Yep I have the same issue @mevdschee. I have a db as this model:

If I want to read one row of "RezeptMischungen", I have this structure by your api:
{ "RezepteMischungen": [ { "ID": "1", "Rezept_ID": "1", "Aroma_ID": "1" } ], "rezeptemischungen": [ { "ID": "1", "Rezept_ID": "1", "Rezepte": [ { "ID": "1", "Name": "Erdbeer", "Datum": "2016-02-09 12:00:42", "User_ID": "1", "Base_ID": "1", "Bewertung_ID": "2", "Reifezeit": "7" } ], "Aroma_ID": "1", "Aromen": [ { "ID": "1", "Name": "Erdbeer", "Datum": "2016-02-09 12:01:29", "Hersteller_ID": "1", "Bewertung_ID": "1" } ] } ], "Basen": [ { "ID": "1", "Name": "VPG", "Datum": "2016-02-09 11:43:19", "Hersteller_ID": "1", "Bewertung_ID": "2" }, { "ID": "2", "Name": "Traditional", "Datum": "2016-02-09 12:14:20", "Hersteller_ID": "1", "Bewertung_ID": "1" } ] }

Formated image of the return of your api:

My call of your api:
api.php/RezepteMischungen,Aromen,Rezepte,Basen?filter=rezept_id,eq1&callback=callback

Which is generally ok, but the table "Rezepte" has a relation to "Basen" which isn't applicated.
As you can see, there is a third level of a relation between RezepteMischung -> Rezepte -> Basen
I hope you know what I mean. If not, feel free to ask me anything.

Attached I send you my sql dump.
d020a380.txt

I expect a result like this:

Hope thats detailed enough inout for your debugging.
BTW: I'm using php_crud_api_transform.js

[mevdschee]

Thank you very much! I will look into this and report back after reproducing your findings.

[mevdschee]

Does this help?

{
    "RezepteMischungen": [
        {
            "ID": "1",
            "Rezept_ID": "1",
            "Rezepte": [
                {
                    "ID": "1",
                    "Name": "Erdbeer",
                    "Datum": "2016-02-09 12:00:42",
                    "User_ID": "1",
                    "Base_ID": "1",
                    "Basen": [
                        {
                            "ID": "1",
                            "Name": "VPG",
                            "Datum": "2016-02-09 11:43:19",
                            "Hersteller_ID": "1",
                            "Bewertung_ID": "2"
                        }
                    ],
                    "Bewertung_ID": "2",
                    "Reifezeit": "7"
                }
            ],
            "Aroma_ID": "1",
            "Aromen": [
                {
                    "ID": "1",
                    "Name": "Erdbeer",
                    "Datum": "2016-02-09 12:01:29",
                    "Hersteller_ID": "1",
                    "Bewertung_ID": "1"
                }
            ]
        }
    ]
}

And why do you get the table name both in mixed and in lower case? I can not reproduce that bug

[Disane87]

I really don't know why. Could be a collation problem I guess. I will cross check that and your solution tomorrow. Thanks for you effort! :)

[mevdschee]

Okay, your contribution is much appreciated. You are probably running MySQL on Windows, right?

[Disane87]

Oh no. Im running mysql within XAMPP under OSX El Capitan. I can check that with my external Apache with MySQL. But I created that DB with Navicat not PHPmyadmin.

[mevdschee]

@Disane87 Thank you for your cooperation on this second bug.

Please post the returned JSON on your machine and please also post the output of:

SELECT 1 as `N`,`TABLE_NAME` FROM `INFORMATION_SCHEMA`.`TABLES` WHERE `TABLE_NAME` LIKE 'RezepteMischungen' UNION
SELECT 2,`TABLE_NAME` FROM `INFORMATION_SCHEMA`.`TABLES` WHERE `TABLE_NAME` = 'RezepteMischungen' UNION
SELECT 3,`TABLE_NAME` FROM `INFORMATION_SCHEMA`.`TABLES` WHERE `TABLE_NAME` COLLATE 'utf8_bin' LIKE 'RezepteMischungen'  UNION
SELECT 4,`TABLE_NAME` FROM `INFORMATION_SCHEMA`.`TABLES` WHERE `TABLE_NAME` LIKE 'rezeptemischungen';

on my machine it is:

N,TABLE_NAME
1,RezepteMischungen
2,RezepteMischungen
3,RezepteMischungen
4,RezepteMischungen

because:

Suppose that a query searches the SCHEMATA.SCHEMA_NAME column for the test database. On Linux, file systems are case sensitive, so comparisons of SCHEMATA.SCHEMA_NAME with 'test' match, but comparisons with 'TEST' do not:
...
On Windows or OS X where file systems are not case sensitive, comparisons match both 'test' and 'TEST':

see: https://dev.mysql.com/doc/refman/5.1/en/charset-collation-information-schema.html

Your help is very valuable!

[henninghall]

Sorry my example wasn't the exact result. @Disane87 explains the same issue perfectly. I am using MySQL with XAMPP on Windows.

[mevdschee]

@henninghall You can update your "api.php" with the latest version as I applied a fix. Please let me know whether or not it works for you (it should).

[henninghall]

@mevdschee It works brilliantly! Thanks a lot for keeping this api so great!

[mevdschee]

@Disane87 Can you still help me to identify the other bug (with the casing) by sending the returned JSON and executing the SQL queries on your machine? I have no OSX to test on here.

[Disane87]

@mevdschee
My result of your script:

N   TABLE_NAME
1   RezepteMischungen
2   rezeptemischungen
3   RezepteMischungen
4   RezepteMischungen

Returned JSON with your updated api.php:

{
    "RezepteMischungen": [
        {
            "ID": "1",
            "Rezept_ID": "1",
            "Rezepte": [
                {
                    "ID": "1",
                    "Name": "Erdbeer",
                    "Datum": "2016-02-09 12:00:42",
                    "User_ID": "1",
                    "Base_ID": "1",
                    "Bewertung_ID": "2",
                    "Reifezeit": "7"
                }
            ],
            "Aroma_ID": "1"
        }
    ],
    "rezepte": [
        {
            "ID": "1",
            "Name": "Erdbeer",
            "Datum": "2016-02-09 12:00:42",
            "User_ID": "1",
            "Base_ID": "1",
            "Basen": [
                {
                    "ID": "1",
                    "Name": "VPG",
                    "Datum": "2016-02-09 11:43:19",
                    "Hersteller_ID": "1",
                    "Bewertung_ID": "2"
                }
            ],
            "Bewertung_ID": "2",
            "Reifezeit": "7"
        }
    ],
    "rezeptemischungen": [
        {
            "ID": "1",
            "Rezept_ID": "1",
            "Aroma_ID": "1",
            "Aromen": [
                {
                    "ID": "1",
                    "Name": "Erdbeer",
                    "Datum": "2016-02-09 12:01:29",
                    "Hersteller_ID": "1",
                    "Bewertung_ID": "1"
                }
            ]
        }
    ]
} 

[mevdschee]

Ah.. thank you very much! Very unexpected to me!

SELECT `TABLE_NAME` FROM `INFORMATION_SCHEMA`.`TABLES` WHERE `TABLE_NAME` COLLATE 'utf8_bin' = 'RezepteMischungen';

Can you post the output of this SQL query as well?

[Disane87]

Sure :)

TABLE_NAME
RezepteMischungen

[mevdschee]

Thank you, I will prepare a new version tonight that should fix the bug.

[Disane87]

Thank you! :)
I will use your API for a bigger project, so I guess we'll stay in contact. But very great work! Really appreciate it!

[mevdschee]

The case sensitivity issue should also be fixed now. See: 7bd9919

[Disane87]

Thanks, works now!