LEFT JOIN doesn't work as expected for inherited entites when foreign key is None #182

Closed
Vitalium opened this Issue May 20, 2016 · 1 comment

Projects

None yet

2 participants

@Vitalium

Hello!

For example we have some inherited entities:

class User(db.Entity):
    name = Required(str)
    servers = Set("Server")

class Worker(db.User):
    pass

class Admin(db.Worker):
    pass

# And M:1 relationship with another entity
class Server(db.Entity):
    name = Required(str)
    user = Optional(User)

We can add 3 sample servers (one of them without user) and try to get server's and user's names:

    Server(name='s1.example.com', user=User(name="Alex"))
    Server(name='s2.example.com', user=Worker(name="John"))
    Server(name='free.example.com', user=None)

    left_join((s.name, s.user.name) for s in Server).show()

We get output:

s.name        |s.user.name
--------------+-----------
s1.example.com|Alex       
s2.example.com|John       

There is missing

free.example.com|None

because we have such SQL-query:

SELECT DISTINCT "s"."name", "user-1"."name"
FROM "Server" "s"
  LEFT JOIN "User" "user-1"
    ON "s"."user" = "user-1"."id"
WHERE "user-1"."classtype" IN ('Admin', 'Worker', 'User')

To get full list with None users we need to move WHERE clause into LEFT JOIN, like this:

SELECT DISTINCT "s"."name", "user-1"."name"
FROM "Server" "s"
  LEFT JOIN "User" "user-1"
    ON "s"."user" = "user-1"."id" AND "user-1"."classtype" IN ('Admin', 'Worker', 'User')

Is there a workaround to get behavior like in last SQL?

@kozlovsky kozlovsky added the bug label May 20, 2016
@kozlovsky kozlovsky added this to the 0.6.6 milestone May 20, 2016
@kozlovsky kozlovsky self-assigned this May 20, 2016
@kozlovsky
Contributor
kozlovsky commented May 20, 2016 edited

Thanks for reporting! This is the bug and I'll fix it.

@kozlovsky kozlovsky added a commit that closed this issue Jun 21, 2016
@abetkin @kozlovsky abetkin + kozlovsky Fixes #182 - LEFT JOIN doesn't work as expected for inherited entitie…
…s when foreign key is None
3f46189
@kozlovsky kozlovsky closed this in 3f46189 Jun 21, 2016
@kozlovsky kozlovsky added a commit that referenced this issue Aug 22, 2016
@kozlovsky kozlovsky PonyORM Release 0.6.6 (2016-08-22)
# New features

* Added native JSON data type support in all supported databases: https://docs.ponyorm.com/json.html

# Backward incompatible changes

* Dropped Python 2.6 support

# Improvements

* #179 Added the compatibility with PYPY using psycopg2cffi
* Added an experimental @db_session `strict` parameter: https://docs.ponyorm.com/transactions.html#strict

# Bugfixes

* #182 - LEFT JOIN doesn't work as expected for inherited entities when foreign key is None
* Some small bugs were fixed
5f98491
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment