Left joins results in invalid SQL

[VojtaStanek]

When I do call .find with join option: { alias: 'root', leftJoinAndSelect: { orders__0: 'root.orders', items__1: 'orders__0.items' } } it results in this (invalid) SQL - note the nothing between ON and WHERE:

SELECT "root"."id" AS "root_id", [...] LEFT JOIN "order" "orders__0" ON "orders__0"."userId"="root"."id"  LEFT JOIN "order_item" "items__1" ON  WHERE "root"."id"=$1

Posgres + typeorm 0.1.6

[pleerock]

Can you please provide your entities simplified , so I can test your issue? Also can you please try query builder, does it work for you?

[VojtaStanek]

Here it is (a bit different call, but error is the same).

https://github.com/Cactucs/_typeorm-error

After setup it outputs something like this:

> to@1.0.0 start /hdd/public_html/public_html/tests/to
> ts-node src/index.ts

executing query:  START TRANSACTION
executing query:  SELECT * FROM current_schema()
executing query:  SELECT * FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'order_item' OR table_schema = 'public' AND table_name = 'order' OR table_schema = 'public' AND table_name = 'user'
executing query:  SELECT * FROM information_schema.columns WHERE table_schema IN ('public')
executing query:  SELECT t.relname AS table_name, i.relname AS index_name, a.attname AS column_name, ix.indisunique AS is_unique, a.attnum, ix.indkey FROM pg_class t, pg_class i, pg_index ix, pg_attribute a, pg_namespace ns
WHERE t.oid = ix.indrelid AND i.oid = ix.indexrelid AND a.attrelid = t.oid
AND a.attnum = ANY(ix.indkey) AND t.relkind = 'r' AND t.relname IN ('order_item', 'order', 'user') AND t.relnamespace = ns.OID AND ns.nspname IN ('public') ORDER BY t.relname, i.relname
executing query:  SELECT table_name, constraint_name FROM information_schema.table_constraints WHERE table_schema IN ('public') AND constraint_type = 'FOREIGN KEY'
executing query:  SELECT * FROM information_schema.table_constraints WHERE table_schema IN ('public') AND constraint_type = 'UNIQUE'
executing query:  SELECT c.column_name, tc.table_name, tc.constraint_name FROM information_schema.table_constraints tc
JOIN information_schema.constraint_column_usage AS ccu USING (constraint_schema, constraint_name)
JOIN information_schema.columns AS c ON c.table_schema = tc.constraint_schema AND tc.table_name = c.table_name AND ccu.column_name = c.column_name
where constraint_type = 'PRIMARY KEY' AND c.table_schema IN ('public')
executing query:  COMMIT
executing query:  SELECT "Order"."id" AS "Order_id", "Order"."created" AS "Order_created", "items"."id" AS "items_id", "items"."amount" AS "items_amount" FROM "order" "Order" LEFT JOIN "order_item" "items" ON
query failed:  SELECT "Order"."id" AS "Order_id", "Order"."created" AS "Order_created", "items"."id" AS "items_id", "items"."amount" AS "items_amount" FROM "order" "Order" LEFT JOIN "order_item" "items" ON
error:  { error: syntax error at end of input
    at Connection.parseE (/hdd/public_html/public_html/tests/to/node_modules/pg/lib/connection.js:546:11)
    at Connection.parseMessage (/hdd/public_html/public_html/tests/to/node_modules/pg/lib/connection.js:371:19)
    at Socket.<anonymous> (/hdd/public_html/public_html/tests/to/node_modules/pg/lib/connection.js:114:22)
    at emitOne (events.js:115:13)
    at Socket.emit (events.js:210:7)
    at addChunk (_stream_readable.js:266:12)
    at readableAddChunk (_stream_readable.js:253:11)
    at Socket.Readable.push (_stream_readable.js:211:10)
    at TCP.onread (net.js:585:20)
  name: 'error',
  length: 85,
  severity: 'ERROR',
  code: '42601',
  detail: undefined,
  hint: undefined,
  position: '191',
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'scan.l',
  line: '1074',
  routine: 'scanner_yyerror' }
{ QueryFailedError: syntax error at end of input
    at new QueryFailedError (/hdd/public_html/public_html/tests/to/src/error/QueryFailedError.ts:7:9)
    at Query.callback (/hdd/public_html/public_html/tests/to/src/driver/postgres/PostgresQueryRunner.ts:216:26)
    at Query.handleError (/hdd/public_html/public_html/tests/to/node_modules/pg/lib/query.js:143:17)
    at Connection.connectedErrorHandler (/hdd/public_html/public_html/tests/to/node_modules/pg/lib/client.js:132:26)
    at emitOne (events.js:115:13)
    at Connection.emit (events.js:210:7)
    at Socket.<anonymous> (/hdd/public_html/public_html/tests/to/node_modules/pg/lib/connection.js:118:12)
    at emitOne (events.js:115:13)
    at Socket.emit (events.js:210:7)
    at addChunk (_stream_readable.js:266:12)
  name: 'QueryFailedError',
  length: 85,
  severity: 'ERROR',
  code: '42601',
  detail: undefined,
  hint: undefined,
  position: '191',
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'scan.l',
  line: '1074',
  routine: 'scanner_yyerror',
  query: 'SELECT "Order"."id" AS "Order_id", "Order"."created" AS "Order_created", "items"."id" AS "items_id", "items"."amount" AS "items_amount" FROM "order" "Order" LEFT JOIN "order_item" "items" ON',
  parameters: [] }

[pleerock]

You have incorrect relations setup.

In OrderItem entity:

	@OneToMany(() => Order, order => order.items)
	order: Order

must be:

	@ManyToOne(() => Order, order => order.items)
	order: Order

[VojtaStanek]

Oh f***, sorry and thanks.

I would expect some kind of error/warning.