Alias for JOIN with subquery #83

New Issue

bandirsen · 2022-03-22T11:35:08Z

bandirsen commented

2022-03-22 11:35:08 +00:00

I want to build this sql statement using xorm builder:

SELECT user.id, user.name, external.street
FROM user
INNER JOIN (
	SELECT id, street from other_table // for simplicity only (usually its more complex)
) AS external ON user.id = external.id

How to add "AS external", at the last line, using xorm query builder ?

without it, postgres will complain "subquery in FROM must have an alias"

Been trying this without no luck :

sb.SELECT("user.id, user.name, external.street").
FROM("user").
INNERJOIN(
  sb.SELECT("id, street").FROM("other_table"), 
  sb.Cond(sb.Eq{"user.id": ??????})
)

DBFiddle

I want to build this sql statement using xorm builder: ``` SELECT user.id, user.name, external.street FROM user INNER JOIN ( SELECT id, street from other_table // for simplicity only (usually its more complex) ) AS external ON user.id = external.id ``` How to add "AS external", at the last line, using xorm query builder ? without it, postgres will complain "subquery in FROM must have an alias" Been trying this without no luck : ``` sb.SELECT("user.id, user.name, external.street"). FROM("user"). INNERJOIN( sb.SELECT("id, street").FROM("other_table"), sb.Cond(sb.Eq{"user.id": ??????}) ) ``` [DBFiddle](https://dbfiddle.uk/?rdbms=postgres_12&fiddle=b0cd54c914921850e8cc696fc286e553)

bandirsen commented

2022-03-22 17:37:07 +00:00

I close this, using separeted query solve the problem,
In case someone has same problem this is the solution (sb is xorm query builder):

subQuery, _ := sb.Dialect("postgres").
	Select("id, street").
    From("other_table").ToBoundSQL()
    
mainQuery, _ := sb.Dialect("postgres").
	sb.Select("user.id, user.name, external.street").
	From("user").
	INNERJOIN(
  		fmt.Sprintf("( %s ) AS external", subQuery), 
  		sb.Expr("user.id = external.id")
    ).ToBoundSQL()

I close this, using separeted query solve the problem, In case someone has same problem this is the solution (sb is xorm query builder): ``` subQuery, _ := sb.Dialect("postgres"). Select("id, street"). From("other_table").ToBoundSQL() mainQuery, _ := sb.Dialect("postgres"). sb.Select("user.id, user.name, external.street"). From("user"). INNERJOIN( fmt.Sprintf("( %s ) AS external", subQuery), sb.Expr("user.id = external.id") ).ToBoundSQL() ```

bandirsen closed this issue

2022-03-22 17:37:11 +00:00

lunny added the

proposed

label 2022-03-23 01:54:10 +00:00

lunny commented

2022-03-23 01:54:51 +00:00

Ah, that's a workround.

bandirsen commented

2022-03-23 03:32:16 +00:00

I think my solution is more a "hack" than a solution :)
I propose to add optional Alias parameter into .Select() method just like existing .From() method, or maybe adding new .As() method which can be used by .Select() and .From() methods

I think my solution is more a "hack" than a solution :) I propose to add optional Alias parameter into .Select() method just like existing .From() method, or maybe adding new .As() method which can be used by .Select() and .From() methods

lunny reopened this issue

2022-03-23 08:28:38 +00:00

imle commented

2022-09-29 17:28:57 +00:00

@bandirsen I put up this PR #94 for this functionality.

Sign in to join this conversation.