Issue 1:
Migrated issue, originally created by Anonymous

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.

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!

For the majority (if not all) of these examples, I am assuming the following tables for my examples.

user: id

name

pass

email

post: id

user_id

subject

text

JOIN is too hard to do with a partial select

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.

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?

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.

I feel as if setting a from_obj ruins the "cleanliness" of the code.

Solution 1: Auto-joins

# 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.

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?

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)

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!

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 !

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.

Solution 2: select_join?

This wouldn't require any relationship data to be stored.

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!

ORM issues

I like the idea of object -> 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.

Partial Selects?

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!)

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.

Sessions and Query

Too verbose and unnecessary for projects who just want a simple row -> 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.)

use_label doesn't group tables.

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:

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!

In conclusion

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.

Issue 2:
Migrated issue, originally created by jmagnusson (@jmagnusson)

As we all know contains_eager and joinedload queries can be very expensive to run as the amount of columns and rows in the result set start to grow. subqueryload helps a lot in these cases, but unfortunately there's no contains_eager version for it which could allow filtering of the related collections. There is kind of a way to get around this by using the DisjointEagerLoading recipe. But could this perhaps be achieved in a more generic way which could make it into the library?

One idea I have is to pass in a new option sa.orm.enable_eagerloads_filtering() to .options(), like this:

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(), # <-- This triggers filtering
sa.orm.subqueryload('variants')
.subqueryload('skus')
)
)

Imagine that the commented line would change the behavior of how subqueryloads generates it's queries, and perhaps this could even be a replacement to contains_eager if both subqueryload and joinedload 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.

Main query

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'
)

Variant subquery

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'
)

SKU subquery

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'
)

SQLAlchemy then bakes in the SKU and Variant results into the main query results like it normally would.

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?