Issue: LEFT OUTER JOIN not match item exception
Author: serkandaglioglu
State: closed
Created: 2019-01-31 10:42:26

I use JOIN.LEFT_OUTER in my query. When I print x.intern_request I see two runned query in debug console.

**My Query**
```
items = (AppInternPreference
         	.select(AppInternPreference, AppInternRequest)
         	.join(AppInternRequest, peewee.JOIN.LEFT_OUTER)
    	)
for x in items:
    print x.intern_request
```


**In Debug Console First Query is** 

`('SELECT t1.id, t1.semester_id, t1.company_id, t1.student_id, t1.company_address_id, t1.company_quota_id, t1.insert_date, t1.description, t1.preference_order, t2.id, t2.school_id, t2.semester_id, t2.company_id, t2.department_id, t2.inserter_id, t2.student_id, t2.company_address_id, t2.company_department, t2.description, t2.insert_date, t2.pay_status_id, t2.student_gender_id, t2.student_quota, t2.creator_in_company FROM app_intern_preference AS t1 LEFT OUTER JOIN app_intern_request AS t2 ON (t1.company_quota_id = t2.id)', [])`

In Debug Console Second Query is : 
`('SELECT t1.id, t1.school_id, t1.semester_id, t1.company_id, t1.department_id, t1.inserter_id, t1.student_id, t1.company_address_id, t1.company_department, t1.description, t1.insert_date, t1.pay_status_id, t1.student_gender_id, t1.student_quota, t1.creator_in_company FROM app_intern_request AS t1 WHERE (t1.id = %s) LIMIT %s OFFSET %s', [79, 1, 0])`

In second query peewee trying to do select intern_request. But I already joined in my query. It shouldnt try to get intern request again. It must print None with the code is 'print x.intern_request'.

What is wrong with my query?

It's joining on the company quota id field. Please share your model definitions, as I can't tell what "intern_request" is, and how it may differ from the company_quota_id field.

```
class AppInternRequest(BaseModel):
	id = AutoField()

	school = ForeignKeyField(AppSchool,column_name="school_id",to_field="school_id",backref="school_quotas")
	semester = ForeignKeyField(AppSemester,column_name="semester_id",to_field="semester_id")
	company = ForeignKeyField(AppCompany,column_name="company_id",to_field="company_id")
	department = ForeignKeyField(AppSchoolToDepartment,column_name="department_id",to_field="department_id")
	inserter = ForeignKeyField(User,column_name="inserter_id",to_field="user_id",backref="inserter_com_stu_quotas")
	student = ForeignKeyField(AppSchoolToStudent,column_name="student_id",to_field="student_id",backref="student_com_stu_quotas")
	company_address = ForeignKeyField(AppAddress,column_name="company_address_id",to_field="address_id",backref="address_quotas")


	company_department = TextField(null=True)
	description = TextField(null=True)
	insert_date = DateTimeField(null=True,default=peeweeDateTimeFieldNow)
	pay_status_id = IntegerField(column_name='pay_status_id', null=True)
	student_gender_id = IntegerField(column_name='student_gender_id', null=True)
	student_quota = IntegerField(null=False,default=1)
	creator_in_company = TextField(null=True)
	
	class Meta:
		table_name = 'app_intern_request'

class AppInternPreference(BaseModel):
	id = AutoField()

	semester = ForeignKeyField(AppSemester,column_name="semester_id",to_field="semester_id")
	company = ForeignKeyField(AppCompany,column_name="company_id",to_field="company_id")
	student = ForeignKeyField(AppSchoolToStudent,column_name="student_id",to_field="student_id")
	company_address = ForeignKeyField(AppAddress,column_name="company_address_id",to_field="address_id",backref="address_student_preferences")
	company_quota = ForeignKeyField(AppInternRequest, column_name="company_quota_id", to_field="id", backref="quota_student_preferences")

	insert_date = DateTimeField(null=True,default=peeweeDateTimeFieldNow)
	description = TextField(null=True)
	preference_order = IntegerField(null=False,default=10)

	class Meta:
		table_name = 'app_intern_preference'
		

```

Something's not right. The foreign key from `AppInternPreference` to `AppInternRequest` is named "company_quota", but in your example code you're using "x.intern_request".

That is the problem. I have no idea what "intern_request" is (a property? a typo?).

Another thing -- the "company_quota" foreign-key is not nullable, so why are you using a LEFT OUTER join? There's no possibility of the foreign-key being null, so you should be using an INNER join.

Garbage ticket, by the way. Put an ounce of effort into it next time.

x.intern_request should be x.company_quota it is my mistake on copy board. Sorry for that.
I created two test tables for explain the problem.

DATABASE TABLE SCRIPT

```SQL
CREATE TABLE `test_tweet` (
  `tweet_id` int(11) NOT NULL AUTO_INCREMENT,
  `tweet_content` varchar(255) NOT NULL,
  PRIMARY KEY (`tweet_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE `test_user` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) NOT NULL,
  `wall_tweet_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`user_id`),
  KEY `wall_tweet` (`wall_tweet_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
```

NOTE : I didnt define foreignkey in database but test_user.wall_tweet_id takes test_tweet.tweet_id or null. I dont want to define foreign key in database.

**MODELS**

```Python
class TestTweet(BaseModel):
    tweet_id = AutoField()
    tweet_content = CharField(null=True)

    class Meta:
        table_name = 'test_tweet'

class TestUser(BaseModel):
    user_id = AutoField()
    username = CharField(null=True)
    wall_tweet = ForeignKeyField(TestTweet, column_name="wall_tweet_id", to_field="tweet_id")

    class Meta:
        table_name = 'test_user'
```

When I run below code it throws TestTweetDoesNotExist . wall_tweet_id can be null so I use JOIN.LEFT_OUTER.

```
users = (TestUser
        .select(TestUser,TestTweet)
        .join(TestTweet,peewee.JOIN.LEFT_OUTER)
    )
for x in users:
    print x.wall_tweet
```

x.wall_street : should not write 'null' instead of raise exception?


You say "wall_tweet_id" can be NULL, but you do not have it marked as such in your model.

```python
wall_tweet = ForeignKeyField(TestTweet, null=True)
```

This is the issue with your code. Changing `null=True` will enable it to work as expected.

> You say "wall_tweet_id" can be NULL, but you do not have it marked as such in your model.
> 
> ```python
> wall_tweet = ForeignKeyField(TestTweet, null=True)
> ```
> 
> This is the issue with your code. Changing `null=True` will enable it to work as expected.

I did but still same error

False...

```python
from peewee import *

db = SqliteDatabase(':memory:')

class Base(Model):
    class Meta:
        database = db

class Tweet(Base):
    content = TextField()

class User(Base):
    username = TextField()
    favorite_tweet = ForeignKeyField(Tweet, null=True)

db.create_tables([User, Tweet])

t1 = Tweet.create(content='t1')
t2 = Tweet.create(content='t2')
User.create(username='u1', favorite_tweet=t1)
User.create(username='u2')

import logging
logger = logging.getLogger('peewee')
logger.addHandler(logging.StreamHandler())
logger.setLevel(logging.DEBUG)

query = (User
         .select(User, Tweet)
         .join(Tweet, JOIN.LEFT_OUTER)
         .order_by(User.username))
for user in query:
    print(user.username, user.favorite_tweet)
```

PRINTS:

```
('SELECT "t1"."id", "t1"."username", "t1"."favorite_tweet_id", "t2"."id", "t2"."content" FROM "user" AS "t1" LEFT OUTER JOIN "tweet" AS "t2" ON ("t1"."favorite_tweet_id" = "t2"."id") ORDER BY "t1"."username"', [])
(u'u1', <Tweet: 1>)
(u'u2', None)
```

I delete everything and use your tutorial with Mysql. It worked. Thanks a lot.