Problem with join

23 views
Skip to first unread message

Leonid Dubov

unread,
Apr 24, 2020, 8:36:19 PM4/24/20
to peewee-orm
Hello,

Sorry for this question, looks like it's a problem on my side, but I couldn't understand how to do it in correct way.

I have simple database with 2 tables:

class Brand(BaseModel):
    name = CharField()

    class Meta:
        table_name = 't_brand'

class Bin(BaseModel):
    id = AutoField(column_name='ID')
    bank_name = CharField()
    bin = CharField()
    brand = ForeignKeyField(column_name='brand_id', model=Brand)
    cobrand_id = IntegerField(null=True)
    product = CharField(null=True)

    class Meta:
        table_name = 't_bin'

I try to get all records with fields from Bin and Brands

from models import Bin, Brand

query = Bin.select(Bin.bin, Bin.bank_name, Brand.name.alias('brand')).join(Brand)

for q in query:
print(q.bin, q.bank_name, q.brand)

But there is nothing  the result set for q.brand 
('SELECT "t1"."bin", "t1"."bank_name", "t2"."name" AS "brand" FROM "t_bin" AS "t1" INNER JOIN "t_brand" AS "t2" ON ("t1"."brand_id" = "t2"."id")', [])
626247 Aloqabank None
626248 Qishloq qurilish ban None
626249 Ipak Yo`li Bank None

But if I run generated SQL on the database - it works fine

Screen Shot 2020-04-25 at 3.34.36 AM.png


Could you help to figure out the problem?

Leonid Dubov

unread,
Apr 24, 2020, 8:41:27 PM4/24/20
to peewee-orm
models.py was generated by pwiz and looks like that

rom peewee import *

database = SqliteDatabase('binbase.db')

class UnknownField(object):
    def __init__(self, *_, **__): pass

class BaseModel(Model):
    class Meta:
        database = database

class SqliteSequence(BaseModel):
    name = BareField(null=True)
    seq = BareField(null=True)

    class Meta:
        table_name = 'sqlite_sequence'
        primary_key = False

Charles Leifer

unread,
Apr 25, 2020, 9:25:29 AM4/25/20
to peewe...@googlegroups.com
Peewee resolves your models into a graph, so:

query = Bin.select(Bin.bin, Bin.bank_name, Brand.name)).join(Brand)

for q in query:
print(q.bin, q.bank_name, q.brand.name)

You can use the ".objects()" method to disable this:

query = Bin.select(Bin.bin, Bin.bank_name, Brand.name.alias('brand'))).join(Brand)

for q in query.objects():
print(q.bin, q.bank_name, q.brand)

--
You received this message because you are subscribed to the Google Groups "peewee-orm" group.
To unsubscribe from this group and stop receiving emails from it, send an email to peewee-orm+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/peewee-orm/1329498b-45ce-40cd-bae9-e26374319912%40googlegroups.com.

Leonid Dubov

unread,
Apr 26, 2020, 4:33:27 PM4/26/20
to peewee-orm
Thanks a lot for your assistance.

Could you explain me a one more thing:

If I try to join Bin with Brand twice. In models.py I have that:
class Bin(BaseModel):
    id = AutoField(column_name='ID')
    bank_name = CharField()
    bin = CharField()
    brand = ForeignKeyField(column_name='brand_id', model=Brand)
    cobrand = IntegerField(column_name='cobrand_id',null=True)
    country = ForeignKeyField(column_name='country_iso2_code', field='alpha_2_code', model=Country)
    product = CharField(null=True)

    class Meta:
        table_name = 't_bin'

I query it like that:
query = (Bin
.select(Bin.bin, 
Bin.bank_name, 
Brand.name,
Cobrand.name,
Country.name
)
.join(Brand)
.switch(Bin)
.join(Cobrand, JOIN.LEFT_OUTER, on=cobrand_predicate)
.switch(Bin)
.join(Country))

But when I try to iterate over the result 
print(q.bin, q.bank_name, q.brand.name, q.country.name)

I have
626247 Aloqabank UNION PAY Uzbekistan

but in raw sql it looks like that:
626296,Kapital bank,UZCARD,UNION PAY,Uzbekistan
860002,NBU,UZCARD,null,Uzbekistan


If I understand correctly there kind of NVL function which have cobrand.name when it isn't a null and brand.name when it is null. How to do it in proper way?
Big thanks in advance!


суббота, 25 апреля 2020 г., 16:25:29 UTC+3 пользователь Charles написал:
Peewee resolves your models into a graph, so:

query = Bin.select(Bin.bin, Bin.bank_name, Brand.name)).join(Brand)

for q in query:
print(q.bin, q.bank_name, q.brand.name)

You can use the ".objects()" method to disable this:

query = Bin.select(Bin.bin, Bin.bank_name, Brand.name.alias('brand'))).join(Brand)

for q in query.objects():
print(q.bin, q.bank_name, q.brand)

To unsubscribe from this group and stop receiving emails from it, send an email to peewe...@googlegroups.com.

Charles Leifer

unread,
Apr 26, 2020, 4:57:21 PM4/26/20
to peewe...@googlegroups.com
In your join() call you'll want to specify an attr to patch the Cobrand stuff onto:

# For example:
.join(Cobrand, JOIN.LEFT_OUTER, on=cobrand_predicate, attr='cobrand')

Then the Cobrand data will be available on the "cobrand" attr.

To unsubscribe from this group and stop receiving emails from it, send an email to peewee-orm+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/peewee-orm/72a43b1c-9a60-4727-be71-9228401f0d57%40googlegroups.com.

Leonid Dubov

unread,
Apr 27, 2020, 6:53:08 PM4/27/20
to peewee-orm
Thanks a lot, perfectly works.

воскресенье, 26 апреля 2020 г., 23:57:21 UTC+3 пользователь Charles написал:
In your join() call you'll want to specify an attr to patch the Cobrand stuff onto:

# For example:
.join(Cobrand, JOIN.LEFT_OUTER, on=cobrand_predicate, attr='cobrand')

Then the Cobrand data will be available on the "cobrand" attr.

Reply all
Reply to author
Forward
0 new messages