Problem with join

[Leonid Dubov]

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

Could you help to figure out the problem?

[Leonid Dubov]

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]

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]

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]

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]

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.

To view this discussion on the web visit https://groups.google.com/d/msgid/peewee-orm/72a43b1c-9a60-4727-be71-9228401f0d57%40googlegroups.com.