Multi Reference . Issue with displaying related data

Hyperbytes · July 23, 2022, 10:52am

I have 2 simple tables, books and authors

Books and authors are linked by a multi reference

The data is simple

and

So some books 1 author, some 1 and some zero
Similarly 1 author has 1 book, one 2 and one none

So i start with the simplest query, just book titles

Results are as expected

I then add the multi reference table

Results as expected

I double click the multi reference entry and open a new query dialogue

I don’t a actually want book_id here so i just add author_id

Again results as expected

BUT i want to also include the authors name alongside the author_id and then that’s where i hit a problem…

The only way I can see to add the author_name is to then add a join to the author table

Then add author_name

But that generates an error

From the error message I suspect the error originated from here

Anyone any ideas where this is going wrong?

bpj · July 23, 2022, 11:03am

Have you tried a left join for the author name? It might be failing because it is empty in some cases

Hyperbytes · July 23, 2022, 11:50am

already tried that

ben · July 23, 2022, 12:46pm

Hi Brian, are you looking for a result like:

ben · July 23, 2022, 1:00pm

Assuming that is what you want, maybe with a few tweaks

The Database:

The Tables:

The Main Query:

The Sub Query:
Double click on books.book_authors shown in the image above and add the INNER JOIN

Hyperbytes · July 23, 2022, 2:32pm

Thanks Ben for your time and effort, that is a good solution but i specifically wanted to do this using a multi reference feature as I believe this should be the “Wappler way to go” solution for this type of issue

I was hoping for

{
query: [
{
book_name: "Wappler Quickstart",
book_author: [
{
author_name: "Brian English",
author_id: 1
},
{
author_name: "George Petrov"
author_id: 3
}
]
},
{
book_name: "CSS For Newbies",
book_author: [
{
author_name: "Ben Plysier"
author_id: 2
}
]
},
{
book_name: "Writing Custom Formatters",
book_author: [ ]
},
{
book_name: "MySQL Primer",
book_author: [
{
author_name: "Brian English",
author_id: 1
}
]
}
]
}

(Above manually created)

mebeingken · July 23, 2022, 2:43pm

It may be the sub table alias name. I’ve had to change the alias name in order to make certain sub table queries work.

Hyperbytes · July 23, 2022, 3:04pm

I am sure you are right, thinking the same
The error is related to the column
book_book_author.book_id
in a where clause but i am not even seeing a “where” in the API JSON and only one reference to book_id which is in the initial query

<?php
require('../../dmxConnectLib/dmxConnect.php');


$app = new \lib\App();

$app->define(<<<'JSON'
{
  "meta": {
    "$_GET": [
      {
        "type": "text",
        "name": "sort"
      },
      {
        "type": "text",
        "name": "dir"
      }
    ]
  },
  "exec": {
    "steps": {
      "name": "query",
      "module": "dbconnector",
      "action": "select",
      "options": {
        "connection": "db",
        "sql": {
          "type": "SELECT",
          "columns": [
            {
              "table": "book",
              "column": "book_name"
            }
          ],
          "table": {
            "name": "book"
          },
          "primary": "book_id",
          "joins": [],
          "query": "SELECT book_name\nFROM book",
          "params": [],
          "sub": {
            "book_author": {
              "type": "SELECT",
              "table": {
                "name": "book_book_author",
                "alias": "book_author"
              },
              "key": "book_id",
              "columns": [
                {
                  "table": "book_author",
                  "column": "author_id"
                },
                {
                  "table": "author",
                  "column": "author_name"
                }
              ],
              "joins": [
                {
                  "table": "author",
                  "column": "*",
                  "type": "INNER",
                  "clauses": {
                    "condition": "AND",
                    "rules": [
                      {
                        "table": "author",
                        "column": "author_id",
                        "operator": "equal",
                        "value": {
                          "table": "book_author",
                          "column": "author_id",
                          "type": "number"
                        },
                        "operation": "="
                      }
                    ]
                  },
                  "primary": "author_id"
                }
              ],
              "query": "SELECT book_author.author_id, author.author_name\nFROM book_book_author AS book_author\nINNER JOIN author ON (author.author_id = book_author.author_id)",
              "params": []
            }
          }
        }
      },
      "output": true,
      "meta": [
        {
          "type": "text",
          "name": "book_name"
        },
        {
          "name": "book_author",
          "type": "array",
          "sub": [
            {
              "type": "number",
              "name": "author_id"
            },
            {
              "type": "text",
              "name": "author_name"
            }
          ]
        }
      ],
      "outputType": "array"
    }
  }
}
JSON
);
?>

Hyperbytes · July 24, 2022, 7:32am

@George, @Teodor Sorry to tag you but any ideas please

ben · July 24, 2022, 11:29am

Brian (@Hyperbytes) , I have tried everything possible and come to the conclusion that this is the most you can get

{"query":[{"name":"Wappler Quickstart","bookAuthor":[{"book_id":1,"author_id":1},{"book_id":1,"author_id":3}]},{"name":"CSS for Newbies","bookAuthor":[{"book_id":2,"author_id":2}]},{"name":"Writing Custom Formatters","bookAuthor":[]},{"name":"MtSQL Primer","bookAuthor":[]}]}

It is not not (presently) possible to get the name of the author. I have based this on the documentation:

Hyperbytes · July 24, 2022, 11:59am

Thanks @ben above and beyond as always
If the multi reference feature cant pull data from both sides of the reference then it is pretty much useless in my opinion.
I cant help feel this is probably a bug or an unforeseen omission of functionality, hopefully @George or @Teodor will clarify