Hello Mike, thank you very much for the in-depth reply and providing a solution :)
There is no specific reason that I posted in google groups, I did not visit the support page, this was just something I decided after thinking where to post first between googlegroups and stackoverflow, I will use github discussions when I open a new discussion in the future! Your workaround will help me continue so many many thanks. I thought I tried all variations, but I can only concede after seeing this work that I did not attempt adding the selectinload to the root options(). It might warrant a new thread, but can you also tell me if there is a way to control the options() that sqlalchemy uses for the recursion of next_step ? Ie. I included this bit in the original post: ``` .options( selectinload(StepModel.next_step, recursion_depth=-1), ... ) ``` and it would be helpful to know if I can chain the same options to next_step, so that step also has its actions and their relationships() eagerly available etc. PS FWIW the reason I included `raiseload("*")` in options() is because I am running async queries, and personally the error I will be confronted with trying to access lazy attributes is more helpful, so I've come to add it by default. Without raisedload(*) I would see: ``` sqlalchemy.exc.MissingGreenlet: greenlet_spawn has not been called; can't call await_only() here. Was IO attempted in an unexpected place? (Background on this error at: https://sqlalche.me/e/20/xd2s) ``` With the help of raisedload(*) I get to see: ``` sqlalchemy.exc.InvalidRequestError: 'ServiceActionModel.service' is not available due to lazy='raise``` ``` This helps me tackle those cases more easily one-by-one. On Thursday, 14 September 2023 at 15:30:23 UTC+2 Mike Bayer wrote: > > > On Thu, Sep 14, 2023, at 7:36 AM, Cornelis Poppema wrote: > > Hi all, > > > I am new to sqlalchemy, I think the idea of what I am trying to achieve is > relatively simple, but I can't seem to figure out how to retrieve > `.service` in the same query. I failed to find an example in the 2.0 > documentation for exactly this. > > My attempts have been to simply chain a .selectinload after the > .selectin_polymorphic, ie.: > > ```python > .options( > selectinload(StepModel.next_step, recursion_depth=-1), > selectinload(StepModel.actionbases).selectin_polymorphic( > [ > ServiceActionModel, > ], > ) > .selectinload(ServiceActionModel.service), > raiseload("*"), > ) > ``` > > This gives the error: > > ``` > /usr/local/lib/python3.11/site-packages/sqlalchemy/orm/strategy_options.py:2442: > > in _raise_for_does_not_link > raise sa_exc.ArgumentError( > E sqlalchemy.exc.ArgumentError: ORM mapped entity or attribute > "ServiceActionModel.service" does not link from relationship > "StepModel.actionbases". Did you mean to use > "StepModel.actionbases.of_type(ServiceActionModel)"? > ``` > > > Hi - > > A few up front things, is it possible you can post these questions that > have a lot of code on github discussions? That's at > https://github.com/sqlalchemy/sqlalchemy/discussions . I'm sort of > wondering how new users are still arriving here at the mailing list, did > you find this list via the support page at > https://www.sqlalchemy.org/support.html ? I would want to change the > verbiage there to please refer people to GH discussions instead. > Especially with these advanced inheritance eager loading problems, which in > the majority of cases end up being real bugs in SQLAlchemy, as seems to be > the case here (at least, there is an inconsistency in the API that somehow > needs to be documented, or something). > > As for the question, first off this is really advanced usage and I've > hardly ever seen people using selectin_polymorphic(), much less deep within > a chain of loaders like this. > > The correct form for this load would follow from how it's described at > https://docs.sqlalchemy.org/en/20/orm/queryguide/inheritance.html#combining-additional-loader-options-with-selectin-polymorphic-subclass-loads > > , where the ORM allows the selectin_polymorphic(Target, [TargetSubclassA]) > to be a sibling to the appropriate relationship load, > selectinload(TargetSubclassA.elements). The example there places both of > these options comma-separated within select().options(). This is the > "inconsistent" part because I'm already surprised the ORM is allowing the > selectinload() to be present against TargetSubclassA when that's not one of > the primary entities in the select(). > > However in your case, you are coming off of a parent loader option > already. So following from this, the correct form based on a direct > reading of those docs would, *in theory*, be: > > select().options( > selectinload(Parent.target).options( > selectin_polymorphic(Target, [TargetSubclassA]), > selectinload(TargetSubclassA.elements) > ) > ) > > that is, you can build up sibling options from a parent loader option > using another call to .options(). > > however, this doesn't work; the validation of the loader chain > unsurprisingly notes that TargetSubclass is not linked from Parent.target, > and they'd like you to use of_type() instead. So I've made a bug for > this here: https://github.com/sqlalchemy/sqlalchemy/issues/10348 as > something has to change here, either the docs, or the usage pattern for > selectin_polymorphic(), or the error checks have to get a lot smarter to > figure this out and let this case pass, since it works fine the way you are > expecting if I just have it skip the error checking. > > What you can do now is use with_polymorphic() instead that is more > configurable for this kind of loader chain: > > TT = with_polymorphic(Target, [TargetSubclassA]) > > select().options(selectinload(Parent.target.of_type(TT).selectinload(TT.TargetSubclassA.elements))) > > discussion can continue at the issue above or a new github discussion, > thanks! > > > -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/3e4b053f-e583-4a20-98b5-abfd08479b60n%40googlegroups.com.