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
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
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)
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.
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
);
?>
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:
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
Hi Brian, why do you have author id here in the book table?
All you need are two tables:
books
id
name
authors
id
name
And create a multi reference under books:
Double click the multi reference table and add a join with the authors table to get the author name:
The result is as expected:
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.
Inital reaction was I have WAMP still in strict mode buit checked and it isn’t
Please view this
joins_issue
Tried you files, that works leaving me more confused
Maybe there’s some issue with PHP then, i will test with php / mysql as well.
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
Going to do full reinstall in case Wappler install is to blame
re-install did not make any difference
It does work when I follow @Teodor’s steps, thank you Teodor.
Incidentally, my table structure looks like