from sqlalchemy import Column, Integer, String, ForeignKey, Enum
from sqlalchemy.orm import Session, relationship, backref
from sqlalchemy.ext.declarative import declarative_base, declared_attr
from sqlalchemy.ext.hybrid import hybrid_property
class Base(object):
@declared_attr
def __tablename__(cls):
return cls.__name__.lower()
Base = declarative_base(cls=Base)
class SurrogatePK(object):
id = Column(Integer, primary_key=True)
class Article(SurrogatePK, Base):
headline = Column(String)
@property
def users(self):
return self.collaborations.join("user").with_entities(User)
class User(SurrogatePK, Base):
name = Column(String)
class Collaboration(Base):
article_id = Column(ForeignKey('article.id'),
primary_key=True)
user_id = Column(ForeignKey('user.id'),
primary_key=True)
role = Column(Enum('editor', 'author'))
user = relationship("User", backref="collaborations")
article = relationship("Article",
backref=backref("collaborations", lazy="dynamic"))
@hybrid_property
def editorial(self):
return self.role == 'editor'
sess = Session()
some_article = Article(id=5)
sess.add(some_article)
print some_article.users.filter(Collaboration.editorial)
SELECT "user".id AS user_id, "user".name AS user_name
FROM collaboration JOIN "user" ON "user".id = collaboration.user_id
WHERE :param_1 = collaboration.article_id AND collaboration.role = :role_1Anyhow, it's a matter of taste, but what may appear at first as hieroglyphics actually is straightforward. It's just that concision here means some packed meaning and some assumed knowledge, so you have to know how to read it. In this case, that's an easy trade-off for me.
I'm not familiar with SQLAlchemy, so I find your example equally hard to read, compounded by there being much more code to spelunk through to understand.
Different strokes and all that, though. There's room for plenty of frameworks :)
If you remove few column definition and setup code, it actually boils down to just:
class Article(ArticleColumns):
@property
def users(self)
return self.collaborations.join("user").with_entities(User)
class User(UserColumns):
pass
class Collaboration(CollaborationColumns):
user = relationship("User", backref="collaborations")
article = relationship("Article", backref=backref("collaborations", lazy="dynamic"))
@hybrid_property
def editorial(self):
return self.role == 'editor'
some_article.users.filter(Collaboration.editorial)
I mostly wanted to demonstrate that the functionality of Rails' merge() can be considered in other ways that are just as succinct.
There is a database toolkit in ruby-land that allows you do just that! It's called Sequel.
I've built an example, which I deliberately kept as bare-bones and explicit as possible: https://gist.github.com/1aa977a63b7d6a727497
You can also build something similar on top of ARel which backs ActiveRecord.
[1]: http://docs.sqlalchemy.org/en/latest/core/tutorial.html
@dev_configs = DeviceConfig.
joins("join (select device_id, max(updated_at) as max_updated_at
from device_configs group by device_id) dc2
on dc2.device_id = device_configs.device_id and
dc2.max_updated_at = device_configs.updated_at").
includes("device").order("devices.updated_at desc")
as a note - you shouldn't use includes and joins, includes hammers joins.
Simplest tidy up though is a scope for the join, something like
DeviceConfig.joins("#{DeviceConfig.last_updated.to_sql} dc2
on dc2.device_id = device_configs.device_id
and dc2.max_updated_at = device_configs.updated_at).
order(...)
dc_t = Arel::Table::new :device_configs
dc2_t = Arel::Table::new :device_configs
inner = dc_t.group.(dc_t[:device_id]).
project(dc_t[:device_id].as("device_id"),
dc_t[:updated_at].maximum.as("maximum_updated_at"))
dc2_t.join(inner.join_sources).
on(dc2_t[:device_id].eq(inner[:device_id])).
on(dc2_t[:updated_at].eq(inner[:max_updated_at])).
order(...)
Atleast, those are the two solutions I have though personally - i think it reads better as straight SQL with select_values. That way, you know what is happening.
Nitpicky, but ActiveRecord::Relation is not ARel. ARel is the relational algebra library that underpins ActiveRecord (>=3.0). See http://erniemiller.org/2010/05/11/activerecord-relation-vs-a... for more info.
The specific example he used to demonstrate worked transparently because of the has_many :through relationship for users on Article, which requires the collaborations table to filter the users, so joins it. Otherwise, you would need to do the join yourself before merging the scope, and things get messy pretty quickly, especially if you end up with table aliases (which the merged relation knows nothing about -- it will still query against the collaborations table).
I added "sifters" to Squeel (http://github.com/ernie/squeel) in order to address the need for a set of reusable conditions on a specific model that could work through an association. I'm not saying Squeel is the solution you're definitely looking for, but I just want to let people know about the things they'll need to look out for when using Relation#merge.
The whole point of a relational algebra is that it's closed under all the relevant operations. But Arel's implementation mostly ignores this fact, and you get back different types of objects with incompatible APIs depending on what operations you use and even what order you apply them in.
Concrete example one: "foo.union(bar).union(baz)" explodes because the union operation is not composable.
Concrete example two: you can compose joins from the right but not the left. So "(foo.join(bar)).join(baz)" works but baz.join(foo.join(bar))" explodes -- but not until later when you try to dump it to sql, at which point you get an obscure exception.
When you look under the hood, you see that it's having a hard time with this stuff because it doesn't really implement relational algebra. It's mostly just an abstract syntax tree for SQL.
I've been working on a relational algebra library for ruby (tentatively) called veritas [2] where the sets are closed under all operations.
Given that it's a higher level abstraction than ARel the trade off is that there's not a 1:1 mapping between it's pure RA ops and SQL operations. I err on the side of producing SQL that will returns the correct results, which means some of the queries are a bit verbose. I consider that only a temporary problem though; I believe I can get to the point where most common queries are identical to what you'd write by hand. I'm focused on correctness before performance.
I've also written an optimizer [3] that takes the RA AST and rewrites it to be smaller and more efficient. It handles lots of the low hanging fruit, but there's still room for improvement. The advantage to this approach is that it simplifies the AST for all targets, not just SQL. There's even room to do per-target optimizations, which is nice because there is often multiple ways to form a query, and some approaches may be more efficient than others on different backends.
[1] https://github.com/sconover/knit-js#footnotes [2] https://github.com/dkubb/veritas [3] https://github.com/dkubb/veritas-optimizer
I'd have great usage of union (which doesn't exist, or doesn't exist in the version of rails I'm stuck in) though.
Edit: Instead of #CCC try #EAEAEA. It made a good difference for me.