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) |
+----------+----------------------+
| 20000181 | 30 |
| 20000182 | 28 |
| 20000183 | 26 |
| 20000184 | 24 |
| 20000185 | 22 |
| 20000186 | 20 |
| 20000187 | 18 |
| 20000188 | 16 |
| 20000189 | 14 |
| 20000190 | 12 |
| 20000191 | 10 |
| 20000192 | 8 |
| 20000193 | 6 |
| 20000194 | 4 |
| 20000195 | 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 [email protected]
<mailto:[email protected]>.
To post to this group, send email to [email protected]
<mailto:[email protected]>.
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
