DB query fails when using a WHERE condition from a JOINED table - NodeJS

Philip_J · July 13, 2020, 4:48am

Wappler Version : 3.0.1 NodeJS
Operating System : Windows 10

Expected behavior

When you create a standard DB query with at least 1 other INNER JOIN, you should be able to use a WHERE condition to filter the results using a column from the 2nd (or 3rd, 4th etc) table that you have joined.

…

Actual behavior

When you create a query with multiple tables, and start to place WHERE conditions on the query, it will break the moment you use a column from any of the additional tables you have created a JOIN on.

i.e. If you just use columns from your primary table, the query will still work. But if you use a column from any of the joined tables it will error with the below error details.

NB. This only happened on the standard query builder. If you write a custom query it works as expected.

How to reproduce

Create a standard DB query on a NodeJS project
-Add at least one other table and create an INNER JOIN
-Add a CONDITION using a column from any of the additional joined tables
-Run query / check error.

Here is a shot of the query i built:

Here is the error message:

{"status":"500","message":"Undefined binding(s) detected when compiling SELECT. Undefined column(s): [name.name] query: select agents.first_name, agents.last_name, agents.work_phone, agents.work_email, agents.employee_id, agents.last_updatefromagentsinner jointeamsasnameonname.id=agents.team_idwherename.name= ?","stack":"Error: Undefined binding(s) detected when compiling SELECT. Undefined column(s): [name.name] query: selectagents.first_name, agents.last_name, agents.work_phone, agents.work_email, agents.employee_id, agents.last_updatefromagentsinner jointeamsasnameonname.id=agents.team_idwherename.name = ?\n at QueryCompiler_MySQL.toSQL (C:\\Switchley\\nodeJS\\demo\\node_modules\\knex\\lib\\query\\compiler.js:99:13)\n at Builder.toSQL (C:\\Switchley\\nodeJS\\demo\\node_modules\\knex\\lib\\query\\builder.js:72:44)\n at C:\\Switchley\\nodeJS\\demo\\node_modules\\knex\\lib\\runner.js:31:36\n at C:\\Switchley\\nodeJS\\demo\\node_modules\\knex\\lib\\runner.js:260:24\n at processTicksAndRejections (internal/process/task_queues.js:97:5)\n at async App._exec (C:\\Switchley\\nodeJS\\demo\\lib\\core\\app.js:224:30)\n at async App._exec (C:\\Switchley\\nodeJS\\demo\\lib\\core\\app.js:206:17)\n at async App.exec (C:\\Switchley\\nodeJS\\demo\\lib\\core\\app.js:177:9)\n at async App.define (C:\\Switchley\\nodeJS\\demo\\lib\\core\\app.js:160:9)"}

mgaussie · July 13, 2020, 5:58pm

I seem to be getting lots of 500 errors with my server connects in NodeJS, some to do with joined tables, too. So just adding to the importance of this.

Philip_J · July 13, 2020, 9:31pm

Hey @mgaussie Just on this, I’m not having any issues with running queries with multiple joins, as long as the conditions are only from the original table.

So if you’ve got some extra conditions that might be your problem? Worthwhile to test out at least

patrick · July 24, 2020, 1:42pm

I did some testing and the conditions work fine with joins. Problem seems to be when the parameter is not set, then the error is displayed. Will see if we can handle that better.

patrick · July 24, 2020, 2:21pm

Have a small update that should prevent the error. Replace the file in lib/core.

db.zip (1.2 KB)

George · July 30, 2020, 3:49pm

This is fixed in Wappler 3.0.2

Philip_J · August 1, 2020, 2:12am

Hi @George and @patrick

Only just got round to testing this today and I don’t think this has been fixed. At least not on my end.

Here is a sample query with just one condition that uses a column from one of the joined tables:

Here is the error:
{"status":"500","code":"42P01","message":"select count(*) as \"Total\" from \"users\" where \"teams\".\"id\" = $1 limit $2 - missing FROM-clause entry for table \"teams\"","stack":"error: select count(*) as \"Total\" from \"users\" where \"teams\".\"id\" = $1 limit $2 - missing FROM-clause entry for table \"teams\"\n at Parser.parseErrorMessage (S:\\app_development\\dev\\node_modules\\pg-protocol\\dist\\parser.js:241:15)\n at Parser.handlePacket (S:\\app_development\\dev\\node_modules\\pg-protocol\\dist\\parser.js:89:29)\n at Parser.parse (S:\\app_development\\dev\\node_modules\\pg-protocol\\dist\\parser.js:41:38)\n at Socket.<anonymous> (S:\\app_development\\dev\\node_modules\\pg-protocol\\dist\\index.js:8:42)\n at Socket.emit (events.js:310:20)\n at addChunk (_stream_readable.js:286:12)\n at readableAddChunk (_stream_readable.js:268:9)\n at Socket.Readable.push (_stream_readable.js:209:10)\n at TCP.onStreamRead (internal/stream_base_commons.js:186:23)"}

This query runs fine if i run it in Navicat.

Philip_J · August 1, 2020, 7:06am

Ok… so i might need to re-post this as a new bug… i’ll wait till you advise me to do so.

I tested this further, it only fails when it is a PAGED QUERY and it has a condition from a joined table.

The same query above works if i change it to Multiple Records. If i leave it as Paged Records (which is what i want) then it fails as per the above.

Should i repost this as new bug?

@Teodor @george @patrick

George · August 1, 2020, 7:16am

What are your conditions? Seems some are empty or resulting in an empty value.

Philip_J · August 1, 2020, 7:21am

No, just the one condition. If I change the query to Multiple Records it works. Switch it back to Paged Records and it fails with the below error.

{"status":"500","code":"42P01","message":"select count(*) as \"Total\" from \"users\" where \"teams\".\"id\" = $1 limit $2 - missing FROM-clause entry for table \"teams\"","stack":"error: select count(*) as \"Total\" from \"users\" where \"teams\".\"id\" = $1 limit $2 - missing FROM-clause entry for table \"teams\"\n at Parser.parseErrorMessage (S:\\app_development\\dev\\node_modules\\pg-protocol\\dist\\parser.js:241:15)\n at Parser.handlePacket (S:\\app_development\\dev\\node_modules\\pg-protocol\\dist\\parser.js:89:29)\n at Parser.parse (S:\\app_development\\dev\\node_modules\\pg-protocol\\dist\\parser.js:41:38)\n at Socket.<anonymous> (S:\\app_development\\dev\\node_modules\\pg-protocol\\dist\\index.js:8:42)\n at Socket.emit (events.js:310:20)\n at addChunk (_stream_readable.js:286:12)\n at readableAddChunk (_stream_readable.js:268:9)\n at Socket.Readable.push (_stream_readable.js:209:10)\n at TCP.onStreamRead (internal/stream_base_commons.js:186:23)"}

George · August 1, 2020, 7:23am

Then it seems still a bug to me @patrick

Maybe just with static values?

mgaussie · August 3, 2020, 8:04pm

I’m attempting to do the same as Philip, however with a LEFT join, not just Inner - everything works fine on the first table, just not any joined tables.

Philip_J · August 3, 2020, 9:04pm

I think I’ve isolated the issue to be when it’s a paged query it fails, but if multiple records query it works.

Is yours a paged records query too?

mgaussie · August 4, 2020, 1:50am

Mine aren’t paged records, just a number of tables linked together in a multi query

patrick · August 4, 2020, 3:33pm

Test the following update. Place file in lib/core.

db.zip (1.2 KB)

Philip_J · August 4, 2020, 11:01pm

Hey @patrick

Thanks has solved the issues i was having. Can’t speak for others but for the issues i logged this has worked.

I now have multiple where conditions using data from all the different tables attached to the query.

Thanks for the solve!