Multi Reference . Issue with displaying related data

[Hyperbytes]

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]

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

[Hyperbytes]

already tried that

[ben]

Hi Brian, are you looking for a result like:

[ben]

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]

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]

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]

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]

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

[ben]

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:

Using Tagify with Multi References

Intro Thanks to the multi reference support in Wappler you can easily insert and update your data based on many-to-many relations. In our example we created two tables and connected them with multi reference. Now we will show you how to insert and update the data in these tables and in the related junction table, with the help of the Tagify component. Inserting Data We will show you how to create a meeting and insert multiple attendees per meeting with a single insert record step. Insert Pa…

[Hyperbytes]

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

[Teodor]

Hi Brian, why do you have author id here in the book table?

@Hyperbytes

All you need are two tables:

books
id
name

authors
id
name

And create a multi reference under books:

Then your database query looks like:

Double click the multi reference table and add a join with the authors table to get the author name:

The result is as expected:

[Hyperbytes]

Also seeing same thing when adding a join in a sub table element

When i add this join

i get

[Hyperbytes]

@Teodor

Hi Brian, why do you have author id here in the book table?

Yes, i’m aware thanks, that’s actually a hangover from a previous design and it is redundant at that point. I have actually deleted it since.

[Teodor]

Here’s my sample db and server action if you want to test:

books.zip (2.3 KB)

[Hyperbytes]

Inital reaction was I have WAMP still in strict mode buit checked and it isn’t

Please view this

joins_issue

[Hyperbytes]

Tried you files, that works leaving me more confused

[Teodor]

Maybe there’s some issue with PHP then, i will test with php / mysql as well.

[Hyperbytes]

I am seeing an issue with aliasing, just realised this is actually on node as I switched testbeds so ignore PHP comments
Please see this video

joins_issue2