Issue: One-to-one relationships returned as ModelSelect
Author: astrilchuk
State: closed
Created: 2018-07-13 11:15:09

Hi,

I have a number of one-to-one relationships in my schema.  Each related table has a foreign key/primary key back to it's "parent" table.  The one-to-one tables are selected in the SQL but "parent" model's backref properties are instances of ModelSelect.

I'm happy to provide more information if required.

Thanks

If you want to do something like that, why not just add a property?

```python

class Person(Model):
    name = TextField()
  
    @property
    def account(self):
        return self.accounts.get()

    @account.setter
    def account(self, account_obj):
        account_obj.person = self
        account_obj.save(only=[Account.person])

class Account(Model):
    person = ForeignKeyField(Person, backref='accounts', unique=True)
```

Why was this ticket closed?  Is discussion not an option?

I would expect an ORM to be able to load both records at the same time.  I have to run queries against +50,000 records.  I will have 6-12 1:1 tables.  This will require 100,000s of more queries when all the related row data could be loaded in a single query.


Sorry, I assumed I had answered your question. Have you read these documents:

* http://docs.peewee-orm.com/en/latest/peewee/relationships.html#selecting-from-multiple-sources
* http://docs.peewee-orm.com/en/latest/peewee/relationships.html#avoiding-the-n-1-problem

Assuming you have, you could then modify your code, using my example models:

```python
class Person(Model):
    name = TextField()

class Account(Model):
    person = ForeignKeyField(Person, backref='accounts', unique=True)
    account_data = TextField()

query = (Person
         .select(Person, Account)
         .join(Account, JOIN.LEFT_OUTER, attr='account')  # To get people w/no account
         .order_by(Person.name))
# This is efficiently done in a single query:
for person in query:
    print(person.name, person.account.account_data)
```

> I would expect an ORM to be able to load both records at the same time

Yes, the docs explain this. Check out the links.

If I switch unique=True to primary_key=True for the person ForeignKeyField in the Account model in your example I get the behaviour I mentioned originally.

> If I switch unique=True to primary_key=True for the person ForeignKeyField in the Account model in your example I get the behaviour I mentioned originally.

I'd be careful about that, you may want to [enable query logging](http://docs.peewee-orm.com/en/latest/peewee/database.html#logging-queries) to make sure your assumptions are indeed correct.

```python
# Print all queries to stderr.
import logging
logger = logging.getLogger('peewee')
logger.addHandler(logging.StreamHandler())
logger.setLevel(logging.DEBUG)
```

I'm not sure what assumptions I would be making here.  Using your example, except with primary_key=True instead of unique=True I get the following error:

`AttributeError: 'ModelSelect' object has no attribute 'account_data'`

Which makes sense since account is returned as a ModelSelect instead of the model itself.

When you access the related object, you should not be using the `backref` attribute -- which is a `ModelSelect` -- but assign the related object to a different attribute as I did in my example.

Please look closely at the example:

```python
class Person(Model):
    name = TextField()

class Account(Model):
    person = ForeignKeyField(Person, backref='accounts', unique=True)
    account_data = TextField()

query = (Person
         .select(Person, Account)
         .join(Account, JOIN.LEFT_OUTER, attr='account')  # To get people w/no account
         .order_by(Person.name))
# This is efficiently done in a single query:
for person in query:
    print(person.name, person.account.account_data)
```

In the above, `person.accounts` (with an "s") is a select query. But `person.account`, which is the attribute we specified in the `.join(..., attr='account')`, should hold the joined `Account` instance.

Here is a complete example. This should hopefully clarify whatever it is you're confused by:

```python
from peewee import *

db = SqliteDatabase(':memory:')

class BaseModel(Model):
    class Meta:
        database = db

class Person(BaseModel):
    name = TextField()

class Account(BaseModel):
    person = ForeignKeyField(Person, backref='accounts', primary_key=True)
    account_data = TextField()

db.create_tables([Person, Account])

p1 = Person.create(name='person-1')
Account.create(person=p1, account_data='p1 account data')

p2 = Person.create(name='person-2')
Account.create(person=p2, account_data='p2 account data')

query = (Person
         .select(Person, Account)
         .join(Account, attr='account')
         .order_by(Person.name))
for person in query:
    print(person.name, person.account.account_data)
```

Prints:

```
person-1 p1 account data
person-2 p2 account data
```

Ok, thanks for that.  The issue I was having was that my `backref` property was named "account" instead of "accounts".  This caused the error regardless of whether or not I had `attr` specified in the join.

Here's the frustration I'm having regarding this (using your example):

1. I have explicitly defined a one-to-one relationship between 2 tables - the foreign key with primary_key attribute set.  Assigning the backref as "account" instead of "accounts" seams obvious to me.

`person = ForeignKeyField(Person, backref='account', primary_key=True)`

2. I have explicitly stated in the select query that I wish to retrieve Person *and* Account:

`select([Person, Account])`

3. Finally, I have explicitly specified the join for the select query.

`.join(Account, JOIN.LEFT_OUTER)`

I think I made a reasonable assumption that this would work.  It is not obvious to me that adding `attr` to the join will enable eager loading - the query is already returning account so why not use it?    I understand there is a learning curve to every ORM but I also expect features to be 'discoverable'.  Perhaps that is my failing here.

> I have explicitly defined a one-to-one relationship between 2 tables - the foreign key with primary_key attribute set. Assigning the backref as "account" instead of "accounts" seams obvious to me.

Peewee does not support the special-case of a one-to-one backref. Thus, the backref should always be the plural, e.g. "account**s**".

If you want to expose a singular account reference, then you could:

```python
class Person(...):
    @property
    def account(self):
        return self.accounts.get()
```

> I have explicitly stated in the select query that I wish to retrieve Person and Account

This is only necessary if you are selecting the person and wish to also grab their account details in a single query.

> Finally, I have explicitly specified the join for the select query.

This is always how peewee works?

> Perhaps that is my failing here.

I think the only failing is that you haven't read the docs on [relationships](http://docs.peewee-orm.com/en/latest/peewee/relationships.html). They are thorough and explain all of the behaviors you seem to be confused about.

foreignkey doesn't support query expression?
i've tried to use `self.accounts.get(Account.name=='abc')` but it doesn't work

@dzpt - it works like this:

```python

self.accounts.where(Account.name == 'abc').get()
```

The backref is just a select query, and can be chained like anything else.