I’ve checked a lot of threads here but I’m still stuck with something: Getting the name instead of the ID of a second table with a Join.
I have these two tables:
Posts (rows: ID, Title, Category) - The Category row is the Reference for the ID from the Categories table
Categories (ID, Title) - The
In my page, with the repeat component, I try to show all the posts from my DB and just after the Post Title I would like to show the Category Title associated.
It’s currently showing the Category ID and not the Category Title.
It seems that I need to use the Inner Join (or Left or Right) in order to achieve this, but I can’t get it work unfortunately.
I’ve checked a lot of posts, even those:
Every time I try the solutions I get this message:
{status: "500", code: "ER_DUP_FIELDNAME",…}
code: "ER_DUP_FIELDNAME"
message: "select count(*) as `Total` from (select `Posts`.`id`, `Posts`.`category`, `Posts`.`title`, `Posts`.`description`, `Posts`.`tag`, `Posts`.`slug`, `Posts`.`public`, `Posts`.`creation_date`, `cat`.`id`, `cat`.`title`, `cat`.`description`, `cat`.`slug` from `Posts` left join `Categories` as `cat` on `cat`.`title` = `Posts`.`category` where `Posts`.`public` = true order by `Posts`.`id` DESC) as `t1` limit 1 - ER_DUP_FIELDNAME: Duplicate column name 'id'"
stack: "Error: ER_DUP_FIELDNAME: Duplicate column name 'id'↵ at Query.Sequence._packetToError (/opt/node_app/node_modules/mysql/lib/protocol/sequences/Sequence.js:47:14)↵ at Query.ErrorPacket (/opt/node_app/node_modules/mysql/lib/protocol/sequences/Query.js:79:18)↵ at Protocol._parsePacket (/opt/node_app/node_modules/mysql/lib/protocol/Protocol.js:291:23)↵ at Parser._parsePacket (/opt/node_app/node_modules/mysql/lib/protocol/Parser.js:433:10)↵ at Parser.write (/opt/node_app/node_modules/mysql/lib/protocol/Parser.js:43:10)↵ at Protocol.write (/opt/node_app/node_modules/mysql/lib/protocol/Protocol.js:38:16)↵ at Socket.<anonymous> (/opt/node_app/node_modules/mysql/lib/Connection.js:88:28)↵ at Socket.<anonymous> (/opt/node_app/node_modules/mysql/lib/Connection.js:526:10)↵ at Socket.emit (events.js:315:20)↵ at addChunk (_stream_readable.js:295:12)↵ --------------------↵ at Protocol._enqueue (/opt/node_app/node_modules/mysql/lib/protocol/Protocol.js:144:48)↵ at Connection.query (/opt/node_app/node_modules/mysql/lib/Connection.js:198:25)↵ at /opt/node_app/node_modules/knex/lib/dialects/mysql/index.js:135:18↵ at new Promise (<anonymous>)↵ at Client_MySQL._query (/opt/node_app/node_modules/knex/lib/dialects/mysql/index.js:129:12)↵ at Client_MySQL.query (/opt/node_app/node_modules/knex/lib/client.js:169:17)↵ at Runner.query (/opt/node_app/node_modules/knex/lib/runner.js:151:36)↵ at /opt/node_app/node_modules/knex/lib/runner.js:40:23↵ at /opt/node_app/node_modules/knex/lib/runner.js:277:24↵ at processTicksAndRejections (internal/process/task_queues.js:97:5)"
status: "500"
This error message is pretty clear: Duplicate column name ‘id’
BUT I don’t know how I could fix it, I can’t rename the ID in my Database structure with Wappler.
I just would like to be able to select the category name and show it in my content page for every post (it’s a list of posts).
I get the same error (ER_DUP_FIELDSNAME), but adding aliases doesn’t solve the issue.
Also strange is that this error doesn’t show when querying the local SQLite database, however it does on the remote target’s MySQL database.
This is the database query:
SELECT Products.*, Translations.* AS Translations, Pricing.* AS Pricing
FROM Products
INNER JOIN Product_Translations AS Translations ON Translations.Product_id = Products.id INNER JOIN Product_Pricing AS Pricing ON Pricing.Product_id = Products.id
WHERE (Translations.Language = :P1 /* {{$_GET.language}} */ AND Pricing.Country = :P2 /* {{$_GET.country}} */ AND Products.CategoryID IN ({{$_GET.categories.split(',')}}) AND Products.BrandID IN ({{$_GET.brands.split(',')}}) AND Products.CollectionID IN ({{$_GET.collections.split(',')}}) AND Products.StyleID IN ({{$_GET.styles.split(',')}}))
And this is the full response:
{"status":"500","code":"ER_DUP_FIELDNAME","message":"select count(*) as `Total` from (select `Products`.*, `Translation`.*, `Pricing`.* from `Products` inner join `Product_Translations` as `Translation` on `Translation`.`Product_id` = `Products`.`id` inner join `Product_Pricing` as `Pricing` on `Pricing`.`Product_id` = `Products`.`id` where (`Translation`.`Language` = 'en' and `Pricing`.`Country` = 'US')) as `t1` limit 1 - ER_DUP_FIELDNAME: Duplicate column name 'id'","stack":"Error: ER_DUP_FIELDNAME: Duplicate column name 'id'\n"}
I do understand the problem is that the id fields have the same name in all sub tables. When the sub tables’ (inner join tables’) data are removed from the output columns, the error is not thrown, but of course a lot of data is missing. Setting aliases for these sub tables doesn’t solve the issue unfortunately.
Sounds like you have a field in both tables which have the same name.
You should allocate an alias to one of them so the name is unique. This will require selecting the individual fields rather than using table.* and then you can allocate an alias via the query builder
Yes indeed the id fields have the same name and can’t be changed in Wappler.
I also thought of selecting all the fields individually instead of the full table to be able to set aliases for the id fields. However, I would rather find a solution that allows the full (sub) tables to be selected as it’s more future proof (in case fields are added to the tables later on). Do you think selecting fields individually is the only way this could be solved?
And what could declare that this error is thrown on (more extensive) MySQL and not on SQLite?! I would expect if SQLite can handle the query properly, MySQL would be able to do so as well.
So my field in the Towns table which joins with the Counties table has a slightly different name. The ‘t’ at the starts is the initial of the table (Towns) which I find makes everything logical when using it later.
Aah, I’m not yet using the Database Manager in Wappler much and have preferred to stick with my normal method of working with data so I’m not going to be much help with that. Sorry. I do plan to start using it, I just need to get my head around how they name things and get used to then working with those structures.
Actually it does not future proof against new fields being added as the query schema still needs to be updated so you still need to open and reserve the query.
So your choices are use aliases or chamde the table field names (which is also problematic as wappler bd manager does not allow changing of I’d fields retrospectively, you will need to use an external editir)
Thanks, then I suppose the best option is to query for individual field values instead of full sub tables.
Thanks for thinking along and the explanation!
I think you are in danger of making a very simple problem complicated. Dont know if you watched any of my video tutorials but the issue your area having is why I always rename the id fields when creating tables. Sub tables are the way to go. Specify the fields and alias
Yes, use aliases and in future make ID fields (in fact all if possible) unique. The advice from Jon ( @sitestreet ) about prefixes is a great method of managing field names