<p dir="auto"><strong>Migrated issue, originally created by Anonymous</strong></p>
<p dir="auto">Hello, I am writing a next-generation forum package in Python (somebody needs to beat out vBulletin and those other PHP ones, they dominate the market!) and instead of writing another SQL query builder, I decided to check out SQLAlchemy because of its good reputation and seeming power.</p>
<p dir="auto">While I am very pleased with what it can do, I feel there is room for improvement, which is why I am bringing up these issues. If you want, I can write the patches to do this stuff but I figured I'd see what the consensus is on these ideas before I spent time writing code just to see patches rejected!</p>
<p dir="auto">For the majority (if not all) of these examples, I am assuming the following tables for my examples.</p>
<p dir="auto">user: id</p>
<ul dir="auto">
<li>name</li>
<li>pass</li>
<li>email</li>
</ul>
<p dir="auto">post: id</p>
<ul dir="auto">
<li>user_id</li>
<li>subject</li>
<li>text</li>
</ul>
<h1 dir="auto">JOIN is too hard to do with a partial select</h1>
<p dir="auto">Partial selects seem to be overlooked in SQLAlchemy - which is odd, because they are extremely important for those of us who try to write extremely efficient code for redistribution.</p>
<p dir="auto">Let's take the following example: I want to select all the posts we have and the corresponding username... but not the email and password because they are irrelevant for the task at hand. How would we do this?</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="  select([user.c.name, post](user.c.id,), user.c.id == post.c.user_id) 
  # Oh come on, we have the foreign key established. This is a waste of typing, especially with huge multi-table joins!
  select([user.c.name, post](user.c.id,)).join(user, post) 
  # Nah, this creates an incorrect query. Not only will it not execute, but it tries to add the join after the cartesian product.
  join(user,post).select([user.c.name, post](user.c.id,))
  # What? selects on a join don't let you define what columns you want to retrieve. Weird...
  select([user.c.name, post](user.c.id,), from_obj=[join(user,post)](join(user,post))) 
  # Here is how you do it, but man that's ugly."><pre class="notranslate"><code class="notranslate">  select([user.c.name, post](user.c.id,), user.c.id == post.c.user_id) 
  # Oh come on, we have the foreign key established. This is a waste of typing, especially with huge multi-table joins!
  select([user.c.name, post](user.c.id,)).join(user, post) 
  # Nah, this creates an incorrect query. Not only will it not execute, but it tries to add the join after the cartesian product.
  join(user,post).select([user.c.name, post](user.c.id,))
  # What? selects on a join don't let you define what columns you want to retrieve. Weird...
  select([user.c.name, post](user.c.id,), from_obj=[join(user,post)](join(user,post))) 
  # Here is how you do it, but man that's ugly.
</code></pre></div>
<p dir="auto">I feel as if setting a from_obj ruins the "cleanliness" of the code.</p>
<h2 dir="auto">Solution 1: Auto-joins</h2>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="   # Hypothetical. Syntax is up for debate - idea is what counts.
   select([post.r.user.name, post](post.r.user.id,))

   # SQLAlchemy recognizes that you are trying to join because you are using the user table as a property of post, so it creates a JOIN instead of a cartesian product."><pre class="notranslate"><code class="notranslate">   # Hypothetical. Syntax is up for debate - idea is what counts.
   select([post.r.user.name, post](post.r.user.id,))

   # SQLAlchemy recognizes that you are trying to join because you are using the user table as a property of post, so it creates a JOIN instead of a cartesian product.
</code></pre></div>
<p dir="auto">What on earth is post.r? Basically, it would stand for "relations", or what not - name isn't important. Of course, this is all related to table metadata... while we could detect basic joins with foreign keys, what if the default behavior of X is to do a LEFT JOIN?</p>
<p dir="auto">I propose that relationships be added to the table metadata itself instead of an arbitrary ORM. Regardless of whether I use ORM, there IS a relationship between the tables; it doesn't go away when "business objects" are removed from the picture. Adding relationship data to the table metadata itself would allow built queries and partial selects to enjoy the power of relationships without being tied to using an ORM, which can be undesired. (I bring up ORMs later in this ticket)</p>
<p dir="auto">Full example: Select all posts with the corresponding user id and user name. We had a few users who had alternate accounts and we deleted them but left the posts because they were informative. We can't use a regular JOIN here - it will skip over those posts!</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="   user = Table ( 'user', ... )
   post = Table ( 'post', ..., maybe_has ('user')) # Create a &quot;maybe&quot; link with all the defaults - i.e, the relation entry in post is called 'user', link using the foreign keys defined to the table 'user'.

  select([post.r.user.name, post](post.r.user.id,)).execute() # specifying post by itself only specifies post's columns - not it's relation tables. You could get all of posts' relation-joins by selecting post.r !"><pre class="notranslate"><code class="notranslate">   user = Table ( 'user', ... )
   post = Table ( 'post', ..., maybe_has ('user')) # Create a "maybe" link with all the defaults - i.e, the relation entry in post is called 'user', link using the foreign keys defined to the table 'user'.

  select([post.r.user.name, post](post.r.user.id,)).execute() # specifying post by itself only specifies post's columns - not it's relation tables. You could get all of posts' relation-joins by selecting post.r !
</code></pre></div>
<p dir="auto">I don't think this is too high level for the query builder. As I said earlier, the relation exists regardless of whether I am using an ORM or not.</p>
<h2 dir="auto">Solution 2: select_join?</h2>
<p dir="auto">This wouldn't require any relationship data to be stored.</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="   select_join([user.c.name, post](user.c.id,), join=[post](user,)) # Assume all columns matching with user and post are going to be joined with each other.
   select_join([user.c.name, post](user.c.id,), outerjoin=[post](user,)) # Oh my!"><pre class="notranslate"><code class="notranslate">   select_join([user.c.name, post](user.c.id,), join=[post](user,)) # Assume all columns matching with user and post are going to be joined with each other.
   select_join([user.c.name, post](user.c.id,), outerjoin=[post](user,)) # Oh my!
</code></pre></div>
<h1 dir="auto">ORM issues</h1>
<p dir="auto">I like the idea of object -&gt; relation mapping, I really do. However, I believe the one present in SQLAlchemy is way too bulky for its own good. It needs to be further partitioned.</p>
<h2 dir="auto">Partial Selects?</h2>
<p dir="auto">You cannot partial select with an object in any sort of convenient way. This makes no sense whatsoever. It's not possible through the objects select(), it's not possible through providing a custom select() to the objects select() (SQLAlchemy will complain about missing fields!)</p>
<p dir="auto">Complaining about missing fields is nonsense. It's commonplace to work on subsets of data - I don't need every possible field every time I use an object. If a field isn't there, don't set it. Big deal.</p>
<h2 dir="auto">Sessions and Query</h2>
<p dir="auto">Too verbose and unnecessary for projects who just want a simple row -&gt; object map. I should be able to get the benefits of relating rows to objects without having to jump through hurdles (I believe caching of objects is completely unnecessary for a well designed application that uses SQL efficiently... this should be delegated outside the scope of the basic ORM.)</p>
<h1 dir="auto">use_label doesn't group tables.</h1>
<p dir="auto">Even if I don't use an ORM, I should be able to have separation of tables within my queries. There should be an option to segregate the result set into tables, IE:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="   row = select([user](post,), use_labels=True).execute().fetchone()
   # { post_id, post_user_id, post_subject, post_text, user_id, user_name, user_pass, user_email }
   # Oh shit! I have a function that just operates on the post. Why can't I just pass the post table of the result to my function, so it will work regardless of use_labels or aliases?

   row = select([post,user](post,user), segregate_tables=True).execute().fetchone()
   { post : { id, user_id, subject, text}, user : { id, name, pass, email } }

   my_func(row.post) # Alright! Cool!"><pre class="notranslate"><code class="notranslate">   row = select([user](post,), use_labels=True).execute().fetchone()
   # { post_id, post_user_id, post_subject, post_text, user_id, user_name, user_pass, user_email }
   # Oh shit! I have a function that just operates on the post. Why can't I just pass the post table of the result to my function, so it will work regardless of use_labels or aliases?

   row = select([post,user](post,user), segregate_tables=True).execute().fetchone()
   { post : { id, user_id, subject, text}, user : { id, name, pass, email } }

   my_func(row.post) # Alright! Cool!
</code></pre></div>
<h2 dir="auto">In conclusion</h2>
<p dir="auto">I hope you all find my ideas interesting. I would be willing to help implement any one of these ideas. I don't know the SQLAlchemy code by heart, but I'm a very experienced programmer and DBA and would love to be apart of this project in any way I can. I mean, nobody wants me to write my own SQL package... right? Better to help others.</p>
<p dir="auto"><strong>Migrated issue, originally created by jmagnusson (<a href="https://github.com/jmagnusson">@jmagnusson</a>)</strong></p>
<p dir="auto">As we all know <code class="notranslate">contains_eager</code> and <code class="notranslate">joinedload</code> queries can be very expensive to run as the amount of columns and rows in the result set start to grow. <code class="notranslate">subqueryload</code> helps a lot in these cases, but unfortunately there's no <code class="notranslate">contains_eager</code> version for it which could allow filtering of the related collections. There is kind of a way to get around this by using the <a href="https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/DisjointEagerLoading" rel="nofollow">DisjointEagerLoading</a> recipe. But could this perhaps be achieved in a more generic way which could make it into the library?</p>
<p dir="auto">One idea I have is to pass in a new option <code class="notranslate">sa.orm.enable_eagerloads_filtering()</code> to <code class="notranslate">.options()</code>, like this:</p>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="import sqlalchemy as sa
import sqlalchemy.orm  # noqa
from sqlalchemy.ext import declarative

Base = declarative.declarative_base()
Session = sa.orm.sessionmaker()


class Product(Base):
    __tablename__ = 'product'
    id = sa.Column(sa.Integer, primary_key=True)
    artno = sa.Column(sa.Text)
    name = sa.Column(sa.Text)


class Variant(Base):
    __tablename__ = 'variant'
    id = sa.Column(sa.Integer, primary_key=True)
    color = sa.Column(sa.Text)
    product_id = sa.Column(sa.Integer, sa.ForeignKey(Product.id))
    product = sa.orm.relationship(Product, backref='variants')


class SKU(Base):
    __tablename__ = 'sku'
    id = sa.Column(sa.Integer, primary_key=True)
    size = sa.Column(sa.Text)
    variant_id = sa.Column(sa.Integer, sa.ForeignKey(Variant.id))
    variant = sa.orm.relationship(Variant, backref='skus')


product = Product(name='T-shirt', artno='12345')
variant = Variant(product=product, color='Black')
sku2 = SKU(variant=variant, size='S')
sku = SKU(variant=variant, size='M')
sku = SKU(variant=variant, size='L')


engine = sa.create_engine('sqlite://', echo=True)
Session.configure(bind=engine)
Base.metadata.create_all(bind=engine)
session = Session()
session.add_all([sku, sku2])
session.flush()

query = (
    session.query(Product)
    .outerjoin(Product.variants)
    .outerjoin(Variant.skus)
    .filter(Product.name == 'T-shirt')
    .filter(Variant.color == 'Black')
    .filter(SKU.size == 'M')
    .options(
        sa.orm.enable_eagerloads_filtering(),  # &lt;-- This triggers filtering
        sa.orm.subqueryload('variants')
        .subqueryload('skus')
    )
)"><pre class="notranslate"><span class="pl-k">import</span> <span class="pl-s1">sqlalchemy</span> <span class="pl-k">as</span> <span class="pl-s1">sa</span>
<span class="pl-k">import</span> <span class="pl-s1">sqlalchemy</span>.<span class="pl-s1">orm</span>  <span class="pl-c"># noqa</span>
<span class="pl-k">from</span> <span class="pl-s1">sqlalchemy</span>.<span class="pl-s1">ext</span> <span class="pl-k">import</span> <span class="pl-s1">declarative</span>

<span class="pl-v">Base</span> <span class="pl-c1">=</span> <span class="pl-s1">declarative</span>.<span class="pl-en">declarative_base</span>()
<span class="pl-v">Session</span> <span class="pl-c1">=</span> <span class="pl-s1">sa</span>.<span class="pl-s1">orm</span>.<span class="pl-en">sessionmaker</span>()


<span class="pl-k">class</span> <span class="pl-v">Product</span>(<span class="pl-v">Base</span>):
    <span class="pl-s1">__tablename__</span> <span class="pl-c1">=</span> <span class="pl-s">'product'</span>
    <span class="pl-s1">id</span> <span class="pl-c1">=</span> <span class="pl-s1">sa</span>.<span class="pl-v">Column</span>(<span class="pl-s1">sa</span>.<span class="pl-v">Integer</span>, <span class="pl-s1">primary_key</span><span class="pl-c1">=</span><span class="pl-c1">True</span>)
    <span class="pl-s1">artno</span> <span class="pl-c1">=</span> <span class="pl-s1">sa</span>.<span class="pl-v">Column</span>(<span class="pl-s1">sa</span>.<span class="pl-v">Text</span>)
    <span class="pl-s1">name</span> <span class="pl-c1">=</span> <span class="pl-s1">sa</span>.<span class="pl-v">Column</span>(<span class="pl-s1">sa</span>.<span class="pl-v">Text</span>)


<span class="pl-k">class</span> <span class="pl-v">Variant</span>(<span class="pl-v">Base</span>):
    <span class="pl-s1">__tablename__</span> <span class="pl-c1">=</span> <span class="pl-s">'variant'</span>
    <span class="pl-s1">id</span> <span class="pl-c1">=</span> <span class="pl-s1">sa</span>.<span class="pl-v">Column</span>(<span class="pl-s1">sa</span>.<span class="pl-v">Integer</span>, <span class="pl-s1">primary_key</span><span class="pl-c1">=</span><span class="pl-c1">True</span>)
    <span class="pl-s1">color</span> <span class="pl-c1">=</span> <span class="pl-s1">sa</span>.<span class="pl-v">Column</span>(<span class="pl-s1">sa</span>.<span class="pl-v">Text</span>)
    <span class="pl-s1">product_id</span> <span class="pl-c1">=</span> <span class="pl-s1">sa</span>.<span class="pl-v">Column</span>(<span class="pl-s1">sa</span>.<span class="pl-v">Integer</span>, <span class="pl-s1">sa</span>.<span class="pl-v">ForeignKey</span>(<span class="pl-v">Product</span>.<span class="pl-s1">id</span>))
    <span class="pl-s1">product</span> <span class="pl-c1">=</span> <span class="pl-s1">sa</span>.<span class="pl-s1">orm</span>.<span class="pl-en">relationship</span>(<span class="pl-v">Product</span>, <span class="pl-s1">backref</span><span class="pl-c1">=</span><span class="pl-s">'variants'</span>)


<span class="pl-k">class</span> <span class="pl-v">SKU</span>(<span class="pl-v">Base</span>):
    <span class="pl-s1">__tablename__</span> <span class="pl-c1">=</span> <span class="pl-s">'sku'</span>
    <span class="pl-s1">id</span> <span class="pl-c1">=</span> <span class="pl-s1">sa</span>.<span class="pl-v">Column</span>(<span class="pl-s1">sa</span>.<span class="pl-v">Integer</span>, <span class="pl-s1">primary_key</span><span class="pl-c1">=</span><span class="pl-c1">True</span>)
    <span class="pl-s1">size</span> <span class="pl-c1">=</span> <span class="pl-s1">sa</span>.<span class="pl-v">Column</span>(<span class="pl-s1">sa</span>.<span class="pl-v">Text</span>)
    <span class="pl-s1">variant_id</span> <span class="pl-c1">=</span> <span class="pl-s1">sa</span>.<span class="pl-v">Column</span>(<span class="pl-s1">sa</span>.<span class="pl-v">Integer</span>, <span class="pl-s1">sa</span>.<span class="pl-v">ForeignKey</span>(<span class="pl-v">Variant</span>.<span class="pl-s1">id</span>))
    <span class="pl-s1">variant</span> <span class="pl-c1">=</span> <span class="pl-s1">sa</span>.<span class="pl-s1">orm</span>.<span class="pl-en">relationship</span>(<span class="pl-v">Variant</span>, <span class="pl-s1">backref</span><span class="pl-c1">=</span><span class="pl-s">'skus'</span>)


<span class="pl-s1">product</span> <span class="pl-c1">=</span> <span class="pl-v">Product</span>(<span class="pl-s1">name</span><span class="pl-c1">=</span><span class="pl-s">'T-shirt'</span>, <span class="pl-s1">artno</span><span class="pl-c1">=</span><span class="pl-s">'12345'</span>)
<span class="pl-s1">variant</span> <span class="pl-c1">=</span> <span class="pl-v">Variant</span>(<span class="pl-s1">product</span><span class="pl-c1">=</span><span class="pl-s1">product</span>, <span class="pl-s1">color</span><span class="pl-c1">=</span><span class="pl-s">'Black'</span>)
<span class="pl-s1">sku2</span> <span class="pl-c1">=</span> <span class="pl-v">SKU</span>(<span class="pl-s1">variant</span><span class="pl-c1">=</span><span class="pl-s1">variant</span>, <span class="pl-s1">size</span><span class="pl-c1">=</span><span class="pl-s">'S'</span>)
<span class="pl-s1">sku</span> <span class="pl-c1">=</span> <span class="pl-v">SKU</span>(<span class="pl-s1">variant</span><span class="pl-c1">=</span><span class="pl-s1">variant</span>, <span class="pl-s1">size</span><span class="pl-c1">=</span><span class="pl-s">'M'</span>)
<span class="pl-s1">sku</span> <span class="pl-c1">=</span> <span class="pl-v">SKU</span>(<span class="pl-s1">variant</span><span class="pl-c1">=</span><span class="pl-s1">variant</span>, <span class="pl-s1">size</span><span class="pl-c1">=</span><span class="pl-s">'L'</span>)


<span class="pl-s1">engine</span> <span class="pl-c1">=</span> <span class="pl-s1">sa</span>.<span class="pl-en">create_engine</span>(<span class="pl-s">'sqlite://'</span>, <span class="pl-s1">echo</span><span class="pl-c1">=</span><span class="pl-c1">True</span>)
<span class="pl-v">Session</span>.<span class="pl-en">configure</span>(<span class="pl-s1">bind</span><span class="pl-c1">=</span><span class="pl-s1">engine</span>)
<span class="pl-v">Base</span>.<span class="pl-s1">metadata</span>.<span class="pl-en">create_all</span>(<span class="pl-s1">bind</span><span class="pl-c1">=</span><span class="pl-s1">engine</span>)
<span class="pl-s1">session</span> <span class="pl-c1">=</span> <span class="pl-v">Session</span>()
<span class="pl-s1">session</span>.<span class="pl-en">add_all</span>([<span class="pl-s1">sku</span>, <span class="pl-s1">sku2</span>])
<span class="pl-s1">session</span>.<span class="pl-en">flush</span>()

<span class="pl-s1">query</span> <span class="pl-c1">=</span> (
    <span class="pl-s1">session</span>.<span class="pl-en">query</span>(<span class="pl-v">Product</span>)
    .<span class="pl-en">outerjoin</span>(<span class="pl-v">Product</span>.<span class="pl-s1">variants</span>)
    .<span class="pl-en">outerjoin</span>(<span class="pl-v">Variant</span>.<span class="pl-s1">skus</span>)
    .<span class="pl-en">filter</span>(<span class="pl-v">Product</span>.<span class="pl-s1">name</span> <span class="pl-c1">==</span> <span class="pl-s">'T-shirt'</span>)
    .<span class="pl-en">filter</span>(<span class="pl-v">Variant</span>.<span class="pl-s1">color</span> <span class="pl-c1">==</span> <span class="pl-s">'Black'</span>)
    .<span class="pl-en">filter</span>(<span class="pl-v">SKU</span>.<span class="pl-s1">size</span> <span class="pl-c1">==</span> <span class="pl-s">'M'</span>)
    .<span class="pl-en">options</span>(
        <span class="pl-s1">sa</span>.<span class="pl-s1">orm</span>.<span class="pl-en">enable_eagerloads_filtering</span>(),  <span class="pl-c"># &lt;-- This triggers filtering</span>
        <span class="pl-s1">sa</span>.<span class="pl-s1">orm</span>.<span class="pl-en">subqueryload</span>(<span class="pl-s">'variants'</span>)
        .<span class="pl-en">subqueryload</span>(<span class="pl-s">'skus'</span>)
    )
)</pre></div>
<p dir="auto">Imagine that the commented line would change the behavior of how <code class="notranslate">subqueryloads</code> generates it's queries, and perhaps this could even be a replacement to <code class="notranslate">contains_eager</code> if both <code class="notranslate">subqueryload</code> and <code class="notranslate">joinedload</code> can be changed in this way. I imagine the generated queries to look something like what follows. Note how the inner queries look almost exactly the same.</p>
<p dir="auto"><em>Main query</em></p>
<div class="highlight highlight-source-sql notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="SELECT
    product.id,
    product.artno,
    product.name
FROM product
WHERE product.id IN (
    SELECT DISTINCT product.id
    FROM product
    LEFT OUTER JOIN variant ON
        variant.product_id = product.id
    LEFT OUTER JOIN sku ON
        sku.variant_id = variant.id
    WHERE
        product.name = 'T-shirt' AND
        variant.color = 'Black' AND
        sku.size = 'M'
)"><pre class="notranslate"><span class="pl-k">SELECT</span>
    <span class="pl-c1">product</span>.<span class="pl-c1">id</span>,
    <span class="pl-c1">product</span>.<span class="pl-c1">artno</span>,
    <span class="pl-c1">product</span>.<span class="pl-c1">name</span>
<span class="pl-k">FROM</span> product
<span class="pl-k">WHERE</span> <span class="pl-c1">product</span>.<span class="pl-c1">id</span> <span class="pl-k">IN</span> (
    <span class="pl-k">SELECT DISTINCT</span> <span class="pl-c1">product</span>.<span class="pl-c1">id</span>
    <span class="pl-k">FROM</span> product
    <span class="pl-k">LEFT OUTER JOIN</span> variant <span class="pl-k">ON</span>
        <span class="pl-c1">variant</span>.<span class="pl-c1">product_id</span> <span class="pl-k">=</span> <span class="pl-c1">product</span>.<span class="pl-c1">id</span>
    <span class="pl-k">LEFT OUTER JOIN</span> sku <span class="pl-k">ON</span>
        <span class="pl-c1">sku</span>.<span class="pl-c1">variant_id</span> <span class="pl-k">=</span> <span class="pl-c1">variant</span>.<span class="pl-c1">id</span>
    <span class="pl-k">WHERE</span>
        <span class="pl-c1">product</span>.<span class="pl-c1">name</span> <span class="pl-k">=</span> <span class="pl-s"><span class="pl-pds">'</span>T-shirt<span class="pl-pds">'</span></span> <span class="pl-k">AND</span>
        <span class="pl-c1">variant</span>.<span class="pl-c1">color</span> <span class="pl-k">=</span> <span class="pl-s"><span class="pl-pds">'</span>Black<span class="pl-pds">'</span></span> <span class="pl-k">AND</span>
        <span class="pl-c1">sku</span>.<span class="pl-c1">size</span> <span class="pl-k">=</span> <span class="pl-s"><span class="pl-pds">'</span>M<span class="pl-pds">'</span></span>
)</pre></div>
<p dir="auto"><em>Variant subquery</em></p>
<div class="highlight highlight-source-sql notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="SELECT
    variant.id,
    variant.product_id,
    variant.color
FROM variant
WHERE variant.id IN (
    SELECT DISTINCT variant.id
    FROM product
    LEFT OUTER JOIN variant ON
        variant.product_id = product.id
    LEFT OUTER JOIN sku ON
        sku.variant_id = variant.id
    WHERE
        product.name = 'T-shirt' AND
        variant.color = 'Black' AND
        sku.size = 'M'
)"><pre class="notranslate"><span class="pl-k">SELECT</span>
    <span class="pl-c1">variant</span>.<span class="pl-c1">id</span>,
    <span class="pl-c1">variant</span>.<span class="pl-c1">product_id</span>,
    <span class="pl-c1">variant</span>.<span class="pl-c1">color</span>
<span class="pl-k">FROM</span> variant
<span class="pl-k">WHERE</span> <span class="pl-c1">variant</span>.<span class="pl-c1">id</span> <span class="pl-k">IN</span> (
    <span class="pl-k">SELECT DISTINCT</span> <span class="pl-c1">variant</span>.<span class="pl-c1">id</span>
    <span class="pl-k">FROM</span> product
    <span class="pl-k">LEFT OUTER JOIN</span> variant <span class="pl-k">ON</span>
        <span class="pl-c1">variant</span>.<span class="pl-c1">product_id</span> <span class="pl-k">=</span> <span class="pl-c1">product</span>.<span class="pl-c1">id</span>
    <span class="pl-k">LEFT OUTER JOIN</span> sku <span class="pl-k">ON</span>
        <span class="pl-c1">sku</span>.<span class="pl-c1">variant_id</span> <span class="pl-k">=</span> <span class="pl-c1">variant</span>.<span class="pl-c1">id</span>
    <span class="pl-k">WHERE</span>
        <span class="pl-c1">product</span>.<span class="pl-c1">name</span> <span class="pl-k">=</span> <span class="pl-s"><span class="pl-pds">'</span>T-shirt<span class="pl-pds">'</span></span> <span class="pl-k">AND</span>
        <span class="pl-c1">variant</span>.<span class="pl-c1">color</span> <span class="pl-k">=</span> <span class="pl-s"><span class="pl-pds">'</span>Black<span class="pl-pds">'</span></span> <span class="pl-k">AND</span>
        <span class="pl-c1">sku</span>.<span class="pl-c1">size</span> <span class="pl-k">=</span> <span class="pl-s"><span class="pl-pds">'</span>M<span class="pl-pds">'</span></span>
)</pre></div>
<p dir="auto"><em>SKU subquery</em></p>
<div class="highlight highlight-source-sql notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="SELECT
    sku.id,
    sku.variant_id,
    sku.size
FROM sku
WHERE sku.id IN (
    SELECT DISTINCT sku.id
    FROM product
    LEFT OUTER JOIN variant ON
        variant.product_id = product.id
    LEFT OUTER JOIN sku ON
        sku.variant_id = variant.id
    WHERE
        product.name = 'T-shirt' AND
        variant.color = 'Black' AND
        sku.size = 'M'
)"><pre class="notranslate"><span class="pl-k">SELECT</span>
    <span class="pl-c1">sku</span>.<span class="pl-c1">id</span>,
    <span class="pl-c1">sku</span>.<span class="pl-c1">variant_id</span>,
    <span class="pl-c1">sku</span>.<span class="pl-c1">size</span>
<span class="pl-k">FROM</span> sku
<span class="pl-k">WHERE</span> <span class="pl-c1">sku</span>.<span class="pl-c1">id</span> <span class="pl-k">IN</span> (
    <span class="pl-k">SELECT DISTINCT</span> <span class="pl-c1">sku</span>.<span class="pl-c1">id</span>
    <span class="pl-k">FROM</span> product
    <span class="pl-k">LEFT OUTER JOIN</span> variant <span class="pl-k">ON</span>
        <span class="pl-c1">variant</span>.<span class="pl-c1">product_id</span> <span class="pl-k">=</span> <span class="pl-c1">product</span>.<span class="pl-c1">id</span>
    <span class="pl-k">LEFT OUTER JOIN</span> sku <span class="pl-k">ON</span>
        <span class="pl-c1">sku</span>.<span class="pl-c1">variant_id</span> <span class="pl-k">=</span> <span class="pl-c1">variant</span>.<span class="pl-c1">id</span>
    <span class="pl-k">WHERE</span>
        <span class="pl-c1">product</span>.<span class="pl-c1">name</span> <span class="pl-k">=</span> <span class="pl-s"><span class="pl-pds">'</span>T-shirt<span class="pl-pds">'</span></span> <span class="pl-k">AND</span>
        <span class="pl-c1">variant</span>.<span class="pl-c1">color</span> <span class="pl-k">=</span> <span class="pl-s"><span class="pl-pds">'</span>Black<span class="pl-pds">'</span></span> <span class="pl-k">AND</span>
        <span class="pl-c1">sku</span>.<span class="pl-c1">size</span> <span class="pl-k">=</span> <span class="pl-s"><span class="pl-pds">'</span>M<span class="pl-pds">'</span></span>
)</pre></div>
<p dir="auto">SQLAlchemy then bakes in the <code class="notranslate">SKU</code> and <code class="notranslate">Variant</code> results into the main query results like it normally would.</p>
<p dir="auto">I'm not very familiar at all with the internals of SQLAlchemy so I can't say anything about the feasibility of this method. Could it be achieved? And is it a good idea?</p>