Re: [sqlalchemy] Generating Correlated Subqueries
On Sunday, April 19, 2015 at 7:08:41 PM UTC-4, Michael Bayer wrote: If I want *all* of the Thing object, I change it to this: q = s.query(Thing, func.count(Comment.type)).\ filter(Thing.creation = datetime.date(2015, 4, 19)).\ filter(Thing.creation datetime.date(2015, 4, 26)).\ filter(Comment.target_id == Thing.id).\ filter(Comment.creation = datetime.date(2015, 4, 19)).\ filter(Comment.creation datetime.date(2015, 4, 26)).\ filter(Comment.type == 5).\ group_by(Thing.id).\ order_by(func.count(Comment.type).desc()) I get the original problem. Is there a way to accomplish that without specifying the columns by hand? That's a restriction of SQL (unless you are using MySQL with its legacy settings).The bad way is just to group_by(Thing), which will group by all of its columns. This is a poor performer and not considered to be very correct in SQL practice. The better way is to, as perhaps you were suggesting originally, use a subquery (though not correlated here); the form we'd be looking for is explained in terms of SQL in an old article I still like to link to here: http://weblogs.sqlteam.com/jeffs/archive/2005/12/14/8546.aspx. I'm enough of a fan of this form that it is part of the ORM tutorial in this example: http://docs.sqlalchemy.org/en/rel_1_0/orm/tutorial.html#using-subqueries , so you'd be looking to emulate the form seen here. Ah. I though that s.query(Thing,...) was shorthand for s.query( All the properties in Thing mapped into a Thing object..., but it must be something else. Thanks! -- 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 post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Generating Correlated Subqueries
Sorry, sometimes you get so deep into something you forget everyone else isn't familiar with the problem. As an example, here's what I'm looking for: select things.id, count(comments.type) from things things, comments comments where things.creation = 2015-04-19 and things.creation 2015-04-26 and comments.target_id = things.id and comments.type = 5 and comments.creation = 2015-04-19 and comments.creation 2015-04-26 group by things.id order by count(comments.type) desc; This gives the desired result of: +--+--+ | id | count(comments.type) | +--+--+ | 2181 | 30 | | 2182 | 28 | | 2183 | 26 | | 2184 | 24 | | 2185 | 22 | | 2186 | 20 | | 2187 | 18 | | 2188 | 16 | | 2189 | 14 | | 2190 | 12 | | 2191 | 10 | | 2192 |8 | | 2193 |6 | | 2194 |4 | | 2195 |2 | +--+--+ 15 rows in set (0.00 sec) -- 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 post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Generating Correlated Subqueries
On 4/19/15 9:36 AM, ThereMichael wrote:
Sorry, sometimes you get so deep into something you forget
everyone else isn't familiar with the problem.
As an example, here's what I'm looking for:
select things.id, count(comments.type) from things things, comments
comments
where things.creation = 2015-04-19 and things.creation
2015-04-26 and comments.target_id = things.id
and comments.type = 5
and comments.creation = 2015-04-19 and comments.creation 2015-04-26
group by things.id
order by count(comments.type) desc;
OK here is a demonstration of that SQL. I had to include mappings to
match, so figure out what's different in your code vs. this example to
see where it might be going wrong.
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
import datetime
Base = declarative_base()
class Thing(Base):
__tablename__ = 'things'
id = Column(Integer, primary_key=True)
creation = Column(DateTime)
class Comment(Base):
__tablename__ = 'comments'
id = Column(Integer, primary_key=True)
type = Column(String)
target_id = Column(ForeignKey('things.id'))
creation = Column(DateTime)
s = Session()
q = s.query(Thing.id, func.count(Comment.type)).\
filter(Thing.creation = datetime.date(2015, 4, 19)).\
filter(Thing.creation datetime.date(2015, 4, 26)).\
filter(Comment.target_id == Thing.id).\
filter(Comment.creation = datetime.date(2015, 4, 19)).\
filter(Comment.creation datetime.date(2015, 4, 26)).\
group_by(Thing.id).\
order_by(func.count(Comment.type).desc())
print q
e = create_engine(sqlite://, echo=True)
Base.metadata.create_all(e)
q.with_session(Session(e)).all()
This gives the desired result of:
+--+--+
| id | count(comments.type) |
+--+--+
| 2181 | 30 |
| 2182 | 28 |
| 2183 | 26 |
| 2184 | 24 |
| 2185 | 22 |
| 2186 | 20 |
| 2187 | 18 |
| 2188 | 16 |
| 2189 | 14 |
| 2190 | 12 |
| 2191 | 10 |
| 2192 |8 |
| 2193 |6 |
| 2194 |4 |
| 2195 |2 |
+--+--+
15 rows in set (0.00 sec)
--
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
mailto:sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com
mailto:sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Generating Correlated Subqueries
On 4/19/15 10:16 AM, ThereMichael wrote: Ok, that worked perfectly! If I want /all/ of the Thing object, I change it to this: q = s.query(Thing, func.count(Comment.type)).\ filter(Thing.creation = datetime.date(2015, 4, 19)).\ filter(Thing.creation datetime.date(2015, 4, 26)).\ filter(Comment.target_id == Thing.id).\ filter(Comment.creation = datetime.date(2015, 4, 19)).\ filter(Comment.creation datetime.date(2015, 4, 26)).\ filter(Comment.type == 5).\ group_by(Thing.id).\ order_by(func.count(Comment.type).desc()) I get the original problem. Is there a way to accomplish that without specifying the columns by hand? That's a restriction of SQL (unless you are using MySQL with its legacy settings).The bad way is just to group_by(Thing), which will group by all of its columns. This is a poor performer and not considered to be very correct in SQL practice. The better way is to, as perhaps you were suggesting originally, use a subquery (though not correlated here); the form we'd be looking for is explained in terms of SQL in an old article I still like to link to here: http://weblogs.sqlteam.com/jeffs/archive/2005/12/14/8546.aspx. I'm enough of a fan of this form that it is part of the ORM tutorial in this example: http://docs.sqlalchemy.org/en/rel_1_0/orm/tutorial.html#using-subqueries , so you'd be looking to emulate the form seen here. -- 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 post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Generating Correlated Subqueries
On 4/19/15 9:56 AM, Mike Bayer wrote: On 4/19/15 9:36 AM, ThereMichael wrote: Sorry, sometimes you get so deep into something you forget everyone else isn't familiar with the problem. As an example, here's what I'm looking for: select things.id, count(comments.type) from things things, comments comments where things.creation = 2015-04-19 and things.creation 2015-04-26 and comments.target_id = things.id and comments.type = 5 and comments.creation = 2015-04-19 and comments.creation 2015-04-26 group by things.id order by count(comments.type) desc; s = Session() q = s.query(Thing.id, func.count(Comment.type)).\ filter(Thing.creation = datetime.date(2015, 4, 19)).\ filter(Thing.creation datetime.date(2015, 4, 26)).\ filter(Comment.target_id == Thing.id).\ filter(Comment.creation = datetime.date(2015, 4, 19)).\ filter(Comment.creation datetime.date(2015, 4, 26)).\ group_by(Thing.id).\ order_by(func.count(Comment.type).desc()) one more filter for the Comment.type: q = s.query(Thing.id, func.count(Comment.type)).\ filter(Thing.creation = datetime.date(2015, 4, 19)).\ filter(Thing.creation datetime.date(2015, 4, 26)).\ filter(Comment.target_id == Thing.id).\ filter(Comment.creation = datetime.date(2015, 4, 19)).\ filter(Comment.creation datetime.date(2015, 4, 26)).\ filter(Comment.type == 5).\ group_by(Thing.id).\ order_by(func.count(Comment.type).desc()) -- 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 post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Generating Correlated Subqueries
Ok, that worked perfectly! If I want *all* of the Thing object, I change it to this: q = s.query(Thing, func.count(Comment.type)).\ filter(Thing.creation = datetime.date(2015, 4, 19)).\ filter(Thing.creation datetime.date(2015, 4, 26)).\ filter(Comment.target_id == Thing.id).\ filter(Comment.creation = datetime.date(2015, 4, 19)).\ filter(Comment.creation datetime.date(2015, 4, 26)).\ filter(Comment.type == 5).\ group_by(Thing.id).\ order_by(func.count(Comment.type).desc()) I get the original problem. Is there a way to accomplish that without specifying the columns by hand? -- 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 post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Generating Correlated Subqueries
On 4/18/15 7:13 PM, Michael Wilson wrote:
I have the following tables:
things_table = Table(’thing', self.metadata,
Column('id', Integer, primary_key=True),
…
)
comments_table = Table('comments', self.metadata,
Column('id', Integer, primary_key=True), # Unique id for this comment
Column('type', Integer), # Type of comment
(feedback, etc)
…
)
(And the corresponding mapping).
I’m trying to construct a query like this:
clauseList = []
clauseList.append(Look.creation = start_date_rounded)
clauseList.append(Look.creation = end_date)
clauseList.append(Look.like_count 0)
clauseList.append(Comment.creation = start_date_rounded)
clauseList.append(Comment.creation = end_date)
clauseList.append(Comment.type == CommentTypeLike)
clauseList.append(Comment.target_id == Look.id)
condition = and_(*clauseList)
looks = session.query(Look, Comment,
func.count(Comment.type)).\
group_by(Look.id).\
order_by(func.count(Comment.type).desc()).\
filter(condition).\
offset(0).\
limit(count).\
all()
This fails with :
FROM comments, things WHERE comments.target_id = things.id AND
comments.type = :type_1' returned no FROM clauses due to
auto-correlation; specify correlate(tables) to control correlation
manually.
The “comments_table” and “things_table” declaration aren’t visible to
the function generating the query, but even if I make them visible,
and specify :
correlate(things, comments).\
It still fails.
How can I make this work?
by work we'd need to know what SQL you are going for. The
query(Look, Comment, func.count(Comment.type)) seems very odd because if
you are using aggregates in your query, SQL dictates (unless you're
using MySQL's cheater mode) that all the other columns that aren't
aggregates need to be in the GROUP BY. Also I don't see any subqueries
here so nothing that would refer to correlation or produce that message,
don't see what CommentTypeLike is, etc.
--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Generating Correlated Subqueries
I have the following tables:
things_table = Table(’thing', self.metadata,
Column('id', Integer, primary_key=True),
…
)
comments_table = Table('comments', self.metadata,
Column('id', Integer, primary_key=True), # Unique id for this
comment
Column('type', Integer), # Type of comment
(feedback, etc)
…
)
(And the corresponding mapping).
I’m trying to construct a query like this:
clauseList = []
clauseList.append(Look.creation = start_date_rounded)
clauseList.append(Look.creation = end_date)
clauseList.append(Look.like_count 0)
clauseList.append(Comment.creation = start_date_rounded)
clauseList.append(Comment.creation = end_date)
clauseList.append(Comment.type == CommentTypeLike)
clauseList.append(Comment.target_id == Look.id)
condition = and_(*clauseList)
looks = session.query(Look, Comment,
func.count(Comment.type)).\
group_by(Look.id).\
order_by(func.count(Comment.type).desc()).\
filter(condition).\
offset(0).\
limit(count).\
all()
This fails with :
FROM comments, things
WHERE comments.target_id = things.id AND comments.type = :type_1' returned no
FROM clauses due to auto-correlation; specify correlate(tables) to control
correlation manually.
The “comments_table” and “things_table” declaration aren’t visible to the
function generating the query, but even if I make them visible, and specify :
correlate(things, comments).\
It still fails.
How can I make this work?
Thanks!
--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
