Issue: Bug in mapping to class?
Author: ruby99
State: closed
Created: 2019-05-02 20:33:19

Hello,

```
>>> peewee.__version__
'3.9.5'
```
```

Back = R.alias()
BackOU = OU.alias()

R.select(BackOU).join(OU).switch(R).join(Back, on=R.back).join(BackOU).where(OU.name=="G-CK-p1").group_by(BackOU)
```

while SQL seems about right:

```sql
SELECT "t1"."id", "t1"."name" 
FROM "r" AS "t2" 
INNER JOIN "ou" AS "t3" 
  ON ("t2"."ou_id" = "t3"."id") 
INNER JOIN "r" AS "t4" 
  ON ("t2"."back_id" = "t4"."id") 
INNER JOIN "ou" AS "t1" 
  ON ("t4"."ou_id" = "t1"."id") 
WHERE ("t3"."name" = ?) 
GROUP BY "t1"', ['G-CK-p1'])
```

traceback:
```
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/home/fback/python/peewee.py", line 5809, in <lambda>
    cls.__name__, self.__str__()))
  File "/home/fback/Apps/rel/models.py", line 30, in __str__
    return self.ou.name + " " + self.lbl
  File "/home/fback/python/peewee.py", line 4142, in __get__
    return self.get_rel_instance(instance)
  File "/home/fback/python/peewee.py", line 4137, in get_rel_instance
    raise self.rel_model.DoesNotExist
models.OUDoesNotExist
```

indicates that ORM maps to R, instead of OU.

Regards,
b.

Can you use readable names here? Wtf is r, ou, backr.

Oh, also: is this a regression? If so, What is the last version in which this worked?

regarding 2nd comment, IDK. I'm evaluating if peewee can replace django's orm for one of my projects.

regarding 1st comment:

```
class OU(peewee.Model):
    name = peewee.CharField(max_length=32)

```
```
class R(peewee.Model):
    ou = peewee.ForeignKeyField(OU, null=False, backref='r_set')
    back = peewee.ForeignKeyField('self', null=True)
    front = peewee.ForeignKeyField('self', null=True)
    idx = peewee.IntegerField()
    lbl = peewee.CharField(null=True,max_length=8)
    
    def __str__(self):
    	"""
    	WARNING!
    	This is costly, at least with peewee. With others ORMs probably too
    	"""
    	return self.ou.name + " " + self.lbl

    class Meta:
    	# Make ou.id + idx UNIQUE together
    	indexes = (
    		(('ou', 'idx'), True),
    	)
```

I want to get all distinct r.back.ous for given r.ou.name. dicts() and tuples() works as expected, eg:
```
>>> print(list(R.select(BackOU).join(OU).switch(R).join(Back, on=R.back).join(BackOU).where(OU.name=="G-CK-p1").group_by(BackOU).dicts()))
[{'id': 2, 'name': 'G-ELE-p1'}]
```


First off, you've written your query in the most ass-backward way conceivable. Try writing your query so you are selecting first from OU, since that's what you're after anyways:

```python

# Should be equivalent and is much more clear.
# Because you're interested in data from the OU table, start there.
query = (OU
         .select(OU)
         .join_from(OU, R)
         .join_from(R, Back, on=(Back.back == R.id))
         .join_from(Back, BackOU)
         .where(BackOU.name == 'G-CK-p1')
         .group_by(OU))

# Works as expected.
for ou_obj in query:
    print(ou_obj.name)
```

--------------------------

More in-depth explanation...

> dicts() and tuples() works as expected, eg:

If this is the case, then the problem is likely that you are running into Peewee's logic that, when dealing with model graphs (which is what any foreign-key relationship forms), will reconstruct that graph when iterating the cursor.

The `dicts()` and `tuples()` (and `namedtuples()` and `objects()`) do not do this graph processing.

The fact is, you're starting "from" the `R` model, but you're interested in the fields on the `BackOU` model. When peewee builds the graph, it sees that a) the starting model is R and b) the selected data is coming from BackOU, so you probably want to do something like this:

```python

# Iterating over the query, because you start from "R", yields R() instances.
for r_instance in query:
    # The backou data you're interested in is *probably* accessible like this, but the
    # query is honestly so fucked, I'm not sure.
    print(r_instance.back.ou.name)
```

**Lastly**: if you want to avoid all the model graph logic, just use the ".objects()" method of the query. This will patch all attributes from the cursor directly onto the model instance:

```python

for r_instance in query.objects():
    print(r_instance.name)  # Should be backou.name here.
```

-------------------------

Questions go on stackoverflow or IRC, by the way.

**First:** I knew diving deep into models will distract from my point. Peewee produces valid SQL (the one included is from debug log, and not something I've conceived myself...), and orm is not able to map it back to model, which *is* passed to .select(). It feels at least inconsistent, and that's why I've decided to open the ticket.

> First off, you've written your query in the most ass-backward way conceivable. 

Thanks, :-P

OUs group Rs. I'm looking for all groups related to the one I know. So the way I wrote this query feels more natural to me.

Also, there are 100s of thousands of individual Rs, and one group can point to 10s of other groups. I feel (maybe that's even what they taught me 20+ years ago) it's proper to limit rows as soon as possible before going on with another joins. But SQL is not my area of expertise, so this may be irrelevant nowadays.

> More in-depth explanation...

[...]

> The fact is, you're starting "from" the `R` model, but you're interested in the fields on the `BackOU` model. When peewee builds the graph, it sees that a) the starting model is R and b) the selected data is coming from BackOU, so you probably want to do something like this:
> 
> ```python
> # Iterating over the query, because you start from "R", yields R() instances.
> for r_instance in query:
>     # The backou data you're interested in is *probably* accessible like this, but the
>     # query is honestly so fucked, I'm not sure.
>     print(r_instance.back.ou.name)
> ```

```python
q = (R
     .select(R,OU,Back,BackOU)
     .join(OU)
     .switch(R)
     .join(Back,on=R.back)
     .join(BackOU)
     .where(OU.name=="G-CK-p1")
     .group_by(BackOU)
)
```
will do this without N+1 problem.

`.select()` will work, but with additional query.

`.select(BackOU)` will fail.
 
> **Lastly**: if you want to avoid all the model graph logic, just use the ".objects()" method of the query. This will patch all attributes from the cursor directly onto the model instance:
> 
> ```python
> for r_instance in query.objects():
>     print(r_instance.name)  # Should be backou.name here.
> ```

Which way is the peewee way?

And now back to the docs.
Regards,
b.

In your example you've got an OU name. And it seemed you were interested in getting the OU id/name via a couple hops. So:

* I've shared an equivalent query that you can use.
* Or you can tack ".objects()" onto the query you shared and access the name directly.