Issue with alias

[Hiteshm01]

While using alias I got a query like below one,

SELECT user.name, subposts.contentFROMuserASuser_testINNER JOIN (SELECTpostsss.content, MAX(postsss.userId) AS userId_maxFROMpostASpostsss) subposts ON (user.id = ?)

Shouldn't user.name be replaced with user_test.name?

[danrzeppa]

Please provide source code with a simple a sample that reproduces the issue.

[Hiteshm01]

Uploaded my test file (test/dialects/aggregate-tests.js) to https://gist.github.com/Hiteshm01/a0a20ac87642d0bbdc56

[danrzeppa]

When I run that query (not in the test harness because I'm running on Windows right now), I get this result:

SELECT `user`.`name`, `subposts`.`content`
FROM `user` AS `aasd`
  INNER JOIN (SELECT `postsss`.`content`, MAX(`postsss`.`userId`) AS `userId_max` FROM `post` AS `postsss`) subposts ON (`user`.`id` = ?)

I'm running the latest version from npm. What are you seeing or not seeing?

[Hiteshm01]

@danrzeppa : correct, as you can see, user table is aliased as aasd (sorry for such senseless name), shoudn't select statement be

SELECT `aasd`.`name`, `subposts`.`content` '

instead of

SELECT `user`.`name`, `subposts`.`content`

[danrzeppa]

I see what you're saying. And yes, I agree with you. There does seem to be an issue there. For anyone else reading this the sample can really be broken down to

var query=user.select(user.name).from(user.as("a"))

Currently generates:

SELECT `user`.`name` FROM `user` AS `a`

But should generate:

SELECT `a`.`name` FROM `user` AS `a`

[Hiteshm01]

Figured out an alternative as

customer.select(customer.as('temp_name').name).from(customer.as("temp_name")),

generated

SELECT `temp_name`.`name` FROM `customer` AS `temp_name`

But I still feel it should be handled in example suggested by @danrzeppa

[spion]

I disagree. When doing self-joins, how will it know which name to use?

You can just put the alias in a var:

var mentor = user.as('mentor')
user.from(user.join(mentor).on(user.mentor_id.equals(mentor.id))
  .where(mentor.active.equals(false).and(user.active.equals(true)))

var temp_name = customer.as('temp_name')
customer.select(temp_name.name).from(temp_name);

[Hiteshm01]

Agreed. Missed this case altogether and spent last hour just to place a fix for this. My bad.

[danrzeppa]

@spion disagree with what exactly?

[spion]

@danrzeppa sorry, forgot to elaborate. I disagree that the alias should change how the original table behaves

var query=user.select(user.name).from(user.as("a"))

This is not how it should work, mostly because of the self-join issue above, but also, it shouldn't be too much trouble to just put the alias in a variable - its less prone to surprises.

I'm not even sure its possible without major changes (in the example above user.as will be executed after user.name)

@Hiteshm01 happens a lot to me as well :) the best thing about query builders is that you get the whole (JS) language are your disposal so there is enough power to find a solution for many issues, but the solution isn't always obvious.

[danrzeppa]

My concern is that

var query=user.select(user.name).from(user.as("a"))

Generates invalid SQL:

SELECT `user`.`name` FROM `user` AS `a`

If this did generate working sql:

SELECT `a`.`name` FROM `user` AS `a`

It wouldn't break any self joins, since you've already aliased the other table as well.

Regarding whether it's actually possible to implement this, that I don't know. I haven't dug into the code surrounding aliases enough to know yet.

[spion]

I don't understand. There is no other table, its the same table instance. How will

user.select(user.name).from(user.as('student').join(user.as('mentor'))
  .on(user.id.equals(user.mentor_id)))

know when the alias should be student and when it should be mentor? :D Also, can we even tell whether we want the student's name or the mentor's name?

However you're right that this generates invalid SQL. There are many other ways to generate similarly invalid SQL from node-sql, e.g.

var query = user.select(user.name).from(post)

Perhaps it might be worthwhile to check whether parameters passed to select are a part of one of the tables/aliases that are listed in .from and simply throw an error if they're not present when generating the query?

[danrzeppa]

@spion I agree with you. I'm not sure it could even be implemented correctly the way I'm suggesting. I see how there can be some confusion, if you come at the problem how you would write it in SQL. The

var query=user.select(user.name).from(user.as("a"))

syntax maps very closely to how it would be written in SQL

SELECT a.name FROM user as a

Which even I initially thought would work.

In conclusion, I don't think there is anything that needs to be done here. In order to alias a table it needs to be done ahead of where the table is being used. I'd recommend we close this issue unless anyone else disagrees.