[sqlalchemy] Re: Aggregation funnctions as mapped column_properties

[Bj]

not sure if the example was clear enough.
What I'm trying to do is to convert the following select to a mapped-
class:

select id, usr_id, rating,
   ( select COUNT(*) from ratings as r where r.id = ratings.id ),
   ( select AVG(r.rating) from ratings as r where r.id =
ratings.id)
   from ratings




--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: Aggregation funnctions as mapped column_properties

[Michael Bayer]

Bj wrote:


 not sure if the example was clear enough.
 What I'm trying to do is to convert the following select to a mapped-
 class:

 select id, usr_id, rating,
( select COUNT(*) from ratings as r where r.id = ratings.id ),
( select AVG(r.rating) from ratings as r where r.id =
 ratings.id)
from ratings

you probably want to alias() the inner tables since they are
self-referring to the parent







 



--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: aggregation with count and webhelpers.paginate

[King Simon-NFHD78]

 -Original Message-
 From: sqlalchemy@googlegroups.com 
 [mailto:sqlalch...@googlegroups.com] On Behalf Of Hollister
 Sent: 20 June 2009 02:15
 To: sqlalchemy
 Subject: [sqlalchemy] Re: aggregation with count and 
 webhelpers.paginate
 
 
 Well, that worked great:
 
 q = meta.Session.query(m.Hit.referer, func.count(m.Hit.id))\
 .group_by(m.Hit.referer)\
 .order_by(func.count(m.Hit.id).desc())
 
 Thanks!
 
 ps: Is there a better way to specify the count in the order_by?
 

If it's just that you don't like repeating yourself, you should be able
to save the result of func.count (untested):

 hit_count = func.count(m.Hit.id)
 q = (meta.Session.query(m.Hit.referer, hit_count)
  .group_by(m.Hit.referer)
  .order_by(hit_count.desc())


Simon

--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: aggregation with count and webhelpers.paginate

[Bobby Impollonia]

You could also use a label to avoid the repetition:
from sqlalchemy.sql import desc
meta.Session.query(m.Hit.referer, func.count(m.Hit.id).label('count'))\
  .group_by(m.Hit.referer)\
  .order_by(desc('count'))

On Mon, Jun 22, 2009 at 2:22 AM, King
Simon-NFHD78simon.k...@motorola.com wrote:

 -Original Message-
 From: sqlalchemy@googlegroups.com
 [mailto:sqlalch...@googlegroups.com] On Behalf Of Hollister
 Sent: 20 June 2009 02:15
 To: sqlalchemy
 Subject: [sqlalchemy] Re: aggregation with count and
 webhelpers.paginate


 Well, that worked great:

     q = meta.Session.query(m.Hit.referer, func.count(m.Hit.id))\
         .group_by(m.Hit.referer)\
         .order_by(func.count(m.Hit.id).desc())

 Thanks!

 ps: Is there a better way to specify the count in the order_by?


 If it's just that you don't like repeating yourself, you should be able
 to save the result of func.count (untested):

     hit_count = func.count(m.Hit.id)
     q = (meta.Session.query(m.Hit.referer, hit_count)
          .group_by(m.Hit.referer)
          .order_by(hit_count.desc())


 Simon

 


--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: aggregation with count and webhelpers.paginate

[Hollister]

Update:

If I run:
results = meta.Session.execute(s).fetchall()
and pass that to paginate, it works.

I guess I just needed the ResultProxy, and not a collection of mapped
objects.

Mike, if you have any additional insight for me, I would appreciate
it.


On Jun 19, 11:30 am, Hollister a.hollister.willi...@gmail.com wrote:
 I have a simple query with a group_by and count(), and I'd like to
 pass this to webhelpers.paginate for display:

  def referrers(self):
         s = select([m.hit_table.c.id, m.hit_table.c.referer, func.count
 (m.Hit.referer).label('count')],
                    from_obj = [m.hit_table],
                    group_by = [m.Hit.referer],
                    order_by = 'count desc')

         query = meta.Session.query(m.Hit).from_statement(s)

         c.paginator = paginate.Page(
             query,
             page = int(request.params.get('page', 1)),
             items_per_page = 50,
         )
         return render('/derived/hits/referrer_list.html')

 The sql generated by this is fine:

    SELECT hit.id AS hit_id, hit.referer AS hit_referer, count
 (hit.referer) AS count
    FROM hit GROUP BY hit.referer ORDER BY count desc

 and the results are correct.

 When I run this, I get:

 Module sqlalchemy.orm.query:1956 in setup_context
           context.froms.append(self.selectable)
                if context.order_by is False and self.mapper.order_by:
                    context.order_by = self.mapper.order_by  if 
 context.order_by is False and self.mapper.order_by:

 AttributeError: 'QueryContext' object has no attribute 'order_by'

 This evidently has nothing to do with the query's order_by clause
 since I get the same error if I remove it. If I execute the query and
 pass the results to paginate:

 results = query.all()

         c.paginator = paginate.Page(
             results,
             page = int(request.params.get('page', 1)),
             items_per_page = 50,
         )

 I get:

 AttributeError: 'Hit' object has no attribute 'count'

 Which I guess makes sense, since I don't have that defined in my
 class.

 What am I doing wrong?
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: aggregation with count and webhelpers.paginate

[Hollister]

The bad news with results = meta.Session.execute(s).fetchall() is
that it runs for every page in the paginator, fetching all rows each
time.

Thoughts, anyone?

On Jun 19, 12:17 pm, Hollister a.hollister.willi...@gmail.com wrote:
 Update:

 If I run:
     results = meta.Session.execute(s).fetchall()
 and pass that to paginate, it works.

 I guess I just needed the ResultProxy, and not a collection of mapped
 objects.

 Mike, if you have any additional insight for me, I would appreciate
 it.

 On Jun 19, 11:30 am, Hollister a.hollister.willi...@gmail.com wrote:

  I have a simple query with a group_by and count(), and I'd like to
  pass this to webhelpers.paginate for display:

   def referrers(self):
          s = select([m.hit_table.c.id, m.hit_table.c.referer, func.count
  (m.Hit.referer).label('count')],
                     from_obj = [m.hit_table],
                     group_by = [m.Hit.referer],
                     order_by = 'count desc')

          query = meta.Session.query(m.Hit).from_statement(s)

          c.paginator = paginate.Page(
              query,
              page = int(request.params.get('page', 1)),
              items_per_page = 50,
          )
          return render('/derived/hits/referrer_list.html')

  The sql generated by this is fine:

     SELECT hit.id AS hit_id, hit.referer AS hit_referer, count
  (hit.referer) AS count
     FROM hit GROUP BY hit.referer ORDER BY count desc

  and the results are correct.

  When I run this, I get:

  Module sqlalchemy.orm.query:1956 in setup_context
            context.froms.append(self.selectable)
                 if context.order_by is False and self.mapper.order_by:
                     context.order_by = self.mapper.order_by  if 
  context.order_by is False and self.mapper.order_by:

  AttributeError: 'QueryContext' object has no attribute 'order_by'

  This evidently has nothing to do with the query's order_by clause
  since I get the same error if I remove it. If I execute the query and
  pass the results to paginate:

  results = query.all()

          c.paginator = paginate.Page(
              results,
              page = int(request.params.get('page', 1)),
              items_per_page = 50,
          )

  I get:

  AttributeError: 'Hit' object has no attribute 'count'

  Which I guess makes sense, since I don't have that defined in my
  class.

  What am I doing wrong?


--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: aggregation with count and webhelpers.paginate

[Michael Bayer]

Hollister wrote:

 When I run this, I get:

 Module sqlalchemy.orm.query:1956 in setup_context
           context.froms.append(self.selectable)
                if context.order_by is False and self.mapper.order_by:
                    context.order_by = self.mapper.order_by  if
 context.order_by is False and self.mapper.order_by:

I need a full stack trace on this since once from_statement() is called,
setup_context() should never be called.



 AttributeError: 'QueryContext' object has no attribute 'order_by'

 This evidently has nothing to do with the query's order_by clause
 since I get the same error if I remove it. If I execute the query and
 pass the results to paginate:

 results = query.all()

         c.paginator = paginate.Page(
             results,
             page = int(request.params.get('page', 1)),
             items_per_page = 50,
         )

 I get:

 AttributeError: 'Hit' object has no attribute 'count'

 Which I guess makes sense, since I don't have that defined in my
 class.

 What am I doing wrong?
 



--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: aggregation with count and webhelpers.paginate

[Michael Bayer]

oh.  if the paginator uses slices to provide LIMIT/OFFSET, you need to
construct a Query that can be limited via slice.  don't use select() here,
use Query fully.



Hollister wrote:

 The bad news with results = meta.Session.execute(s).fetchall() is
 that it runs for every page in the paginator, fetching all rows each
 time.

 Thoughts, anyone?

 On Jun 19, 12:17 pm, Hollister a.hollister.willi...@gmail.com wrote:
 Update:

 If I run:
     results = meta.Session.execute(s).fetchall()
 and pass that to paginate, it works.

 I guess I just needed the ResultProxy, and not a collection of mapped
 objects.

 Mike, if you have any additional insight for me, I would appreciate
 it.

 On Jun 19, 11:30 am, Hollister a.hollister.willi...@gmail.com wrote:

  I have a simple query with a group_by and count(), and I'd like to
  pass this to webhelpers.paginate for display:

   def referrers(self):
          s = select([m.hit_table.c.id, m.hit_table.c.referer,
 func.count
  (m.Hit.referer).label('count')],
                     from_obj = [m.hit_table],
                     group_by = [m.Hit.referer],
                     order_by = 'count desc')

          query = meta.Session.query(m.Hit).from_statement(s)

          c.paginator = paginate.Page(
              query,
              page = int(request.params.get('page', 1)),
              items_per_page = 50,
          )
          return render('/derived/hits/referrer_list.html')

  The sql generated by this is fine:

     SELECT hit.id AS hit_id, hit.referer AS hit_referer, count
  (hit.referer) AS count
     FROM hit GROUP BY hit.referer ORDER BY count desc

  and the results are correct.

  When I run this, I get:

  Module sqlalchemy.orm.query:1956 in setup_context
            context.froms.append(self.selectable)
                 if context.order_by is False and self.mapper.order_by:
                     context.order_by = self.mapper.order_by  if
 context.order_by is False and self.mapper.order_by:

  AttributeError: 'QueryContext' object has no attribute 'order_by'

  This evidently has nothing to do with the query's order_by clause
  since I get the same error if I remove it. If I execute the query and
  pass the results to paginate:

  results = query.all()

          c.paginator = paginate.Page(
              results,
              page = int(request.params.get('page', 1)),
              items_per_page = 50,
          )

  I get:

  AttributeError: 'Hit' object has no attribute 'count'

  Which I guess makes sense, since I don't have that defined in my
  class.

  What am I doing wrong?


 



--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: aggregation with count and webhelpers.paginate

[Hollister]

Here you go:

URL: http://dev:5000/hits/referrers
File '/home/aw/venv/dev/lib/python2.6/site-packages/WebError-0.10.1-
py2.6.egg/weberror/evalexception.py', line 431 in respond
  app_iter = self.application(environ, detect_start_response)
File '/home/aw/venv/dev/lib/python2.6/site-packages/Beaker-1.3-
py2.6.egg/beaker/middleware.py', line 81 in __call__
  return self.app(environ, start_response)
File '/home/aw/venv/dev/lib/python2.6/site-packages/Beaker-1.3-
py2.6.egg/beaker/middleware.py', line 160 in __call__
  return self.wrap_app(environ, session_start_response)
File '/home/aw/venv/dev/lib/python2.6/site-packages/Routes-1.10.3-
py2.6.egg/routes/middleware.py', line 130 in __call__
  response = self.app(environ, start_response)
File '/home/aw/venv/dev/lib/python2.6/site-packages/Pylons-0.9.7-
py2.6.egg/pylons/wsgiapp.py', line 125 in __call__
  response = self.dispatch(controller, environ, start_response)
File '/home/aw/venv/dev/lib/python2.6/site-packages/Pylons-0.9.7-
py2.6.egg/pylons/wsgiapp.py', line 324 in dispatch
  return controller(environ, start_response)
File '/home/aw/projects/resonate/corp/corp/lib/base.py', line 18 in
__call__
  return WSGIController.__call__(self, environ, start_response)
File '/home/aw/venv/dev/lib/python2.6/site-packages/Pylons-0.9.7-
py2.6.egg/pylons/controllers/core.py', line 221 in __call__
  response = self._dispatch_call()
File '/home/aw/venv/dev/lib/python2.6/site-packages/Pylons-0.9.7-
py2.6.egg/pylons/controllers/core.py', line 172 in _dispatch_call
  response = self._inspect_call(func)
File '/home/aw/venv/dev/lib/python2.6/site-packages/Pylons-0.9.7-
py2.6.egg/pylons/controllers/core.py', line 107 in _inspect_call
  result = self._perform_call(func, args)
File '/home/aw/venv/dev/lib/python2.6/site-packages/Pylons-0.9.7-
py2.6.egg/pylons/controllers/core.py', line 60 in _perform_call
  return func(**args)
File '/home/aw/projects/resonate/corp/corp/controllers/hits.py', line
67 in referrers
  items_per_page = 50,
File '/home/aw/venv/dev/lib/python2.6/site-packages/WebHelpers-0.6.4-
py2.6.egg/webhelpers/paginate.py', line 333 in __init__
  self.item_count = len(self.collection)
File '/home/aw/venv/dev/lib/python2.6/site-packages/WebHelpers-0.6.4-
py2.6.egg/webhelpers/paginate.py', line 204 in __len__
  return self.obj.count()
File '/home/aw/venv/dev/lib/python2.6/site-packages/SQLAlchemy-0.5.2-
py2.6.egg/sqlalchemy/orm/query.py', line 1465 in count
  should_nest = should_nest[0]
File '/home/aw/venv/dev/lib/python2.6/site-packages/SQLAlchemy-0.5.2-
py2.6.egg/sqlalchemy/orm/query.py', line 1472 in _col_aggregate
  entity.setup_context(self, context)
File '/home/aw/venv/dev/lib/python2.6/site-packages/SQLAlchemy-0.5.2-
py2.6.egg/sqlalchemy/orm/query.py', line 1956 in setup_context
  if context.order_by is False and self.mapper.order_by:
AttributeError: 'QueryContext' object has no attribute 'order_by'

On Jun 19, 12:35 pm, Michael Bayer mike...@zzzcomputing.com wrote:
 Hollister wrote:

  When I run this, I get:

  Module sqlalchemy.orm.query:1956 in setup_context
            context.froms.append(self.selectable)
                 if context.order_by is False and self.mapper.order_by:
                     context.order_by = self.mapper.order_by  if
  context.order_by is False and self.mapper.order_by:

 I need a full stack trace on this since once from_statement() is called,
 setup_context() should never be called.



  AttributeError: 'QueryContext' object has no attribute 'order_by'

  This evidently has nothing to do with the query's order_by clause
  since I get the same error if I remove it. If I execute the query and
  pass the results to paginate:

  results = query.all()

          c.paginator = paginate.Page(
              results,
              page = int(request.params.get('page', 1)),
              items_per_page = 50,
          )

  I get:

  AttributeError: 'Hit' object has no attribute 'count'

  Which I guess makes sense, since I don't have that defined in my
  class.

  What am I doing wrong?


--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: aggregation with count and webhelpers.paginate

[Michael Bayer]

you can't call count() when you've used from_statement, that should be
raising an error.   the bug is that no error is being raised.

Hollister wrote:

 Here you go:

 URL: http://dev:5000/hits/referrers
 File '/home/aw/venv/dev/lib/python2.6/site-packages/WebError-0.10.1-
 py2.6.egg/weberror/evalexception.py', line 431 in respond
   app_iter = self.application(environ, detect_start_response)
 File '/home/aw/venv/dev/lib/python2.6/site-packages/Beaker-1.3-
 py2.6.egg/beaker/middleware.py', line 81 in __call__
   return self.app(environ, start_response)
 File '/home/aw/venv/dev/lib/python2.6/site-packages/Beaker-1.3-
 py2.6.egg/beaker/middleware.py', line 160 in __call__
   return self.wrap_app(environ, session_start_response)
 File '/home/aw/venv/dev/lib/python2.6/site-packages/Routes-1.10.3-
 py2.6.egg/routes/middleware.py', line 130 in __call__
   response = self.app(environ, start_response)
 File '/home/aw/venv/dev/lib/python2.6/site-packages/Pylons-0.9.7-
 py2.6.egg/pylons/wsgiapp.py', line 125 in __call__
   response = self.dispatch(controller, environ, start_response)
 File '/home/aw/venv/dev/lib/python2.6/site-packages/Pylons-0.9.7-
 py2.6.egg/pylons/wsgiapp.py', line 324 in dispatch
   return controller(environ, start_response)
 File '/home/aw/projects/resonate/corp/corp/lib/base.py', line 18 in
 __call__
   return WSGIController.__call__(self, environ, start_response)
 File '/home/aw/venv/dev/lib/python2.6/site-packages/Pylons-0.9.7-
 py2.6.egg/pylons/controllers/core.py', line 221 in __call__
   response = self._dispatch_call()
 File '/home/aw/venv/dev/lib/python2.6/site-packages/Pylons-0.9.7-
 py2.6.egg/pylons/controllers/core.py', line 172 in _dispatch_call
   response = self._inspect_call(func)
 File '/home/aw/venv/dev/lib/python2.6/site-packages/Pylons-0.9.7-
 py2.6.egg/pylons/controllers/core.py', line 107 in _inspect_call
   result = self._perform_call(func, args)
 File '/home/aw/venv/dev/lib/python2.6/site-packages/Pylons-0.9.7-
 py2.6.egg/pylons/controllers/core.py', line 60 in _perform_call
   return func(**args)
 File '/home/aw/projects/resonate/corp/corp/controllers/hits.py', line
 67 in referrers
   items_per_page = 50,
 File '/home/aw/venv/dev/lib/python2.6/site-packages/WebHelpers-0.6.4-
 py2.6.egg/webhelpers/paginate.py', line 333 in __init__
   self.item_count = len(self.collection)
 File '/home/aw/venv/dev/lib/python2.6/site-packages/WebHelpers-0.6.4-
 py2.6.egg/webhelpers/paginate.py', line 204 in __len__
   return self.obj.count()
 File '/home/aw/venv/dev/lib/python2.6/site-packages/SQLAlchemy-0.5.2-
 py2.6.egg/sqlalchemy/orm/query.py', line 1465 in count
   should_nest = should_nest[0]
 File '/home/aw/venv/dev/lib/python2.6/site-packages/SQLAlchemy-0.5.2-
 py2.6.egg/sqlalchemy/orm/query.py', line 1472 in _col_aggregate
   entity.setup_context(self, context)
 File '/home/aw/venv/dev/lib/python2.6/site-packages/SQLAlchemy-0.5.2-
 py2.6.egg/sqlalchemy/orm/query.py', line 1956 in setup_context
   if context.order_by is False and self.mapper.order_by:
 AttributeError: 'QueryContext' object has no attribute 'order_by'

 On Jun 19, 12:35 pm, Michael Bayer mike...@zzzcomputing.com wrote:
 Hollister wrote:

  When I run this, I get:

  Module sqlalchemy.orm.query:1956 in setup_context
            context.froms.append(self.selectable)
                 if context.order_by is False and self.mapper.order_by:
                     context.order_by = self.mapper.order_by  if
  context.order_by is False and self.mapper.order_by:

 I need a full stack trace on this since once from_statement() is called,
 setup_context() should never be called.



  AttributeError: 'QueryContext' object has no attribute 'order_by'

  This evidently has nothing to do with the query's order_by clause
  since I get the same error if I remove it. If I execute the query and
  pass the results to paginate:

  results = query.all()

          c.paginator = paginate.Page(
              results,
              page = int(request.params.get('page', 1)),
              items_per_page = 50,
          )

  I get:

  AttributeError: 'Hit' object has no attribute 'count'

  Which I guess makes sense, since I don't have that defined in my
  class.

  What am I doing wrong?


 



--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: aggregation with count and webhelpers.paginate

[Hollister]

Ok, I see.
So what's the best way for me to construct and execute this query?

On Jun 19, 1:30 pm, Michael Bayer mike...@zzzcomputing.com wrote:
 you can't call count() when you've used from_statement, that should be
 raising an error.   the bug is that no error is being raised.

 Hollister wrote:

  Here you go:

  URL:http://dev:5000/hits/referrers
  File '/home/aw/venv/dev/lib/python2.6/site-packages/WebError-0.10.1-
  py2.6.egg/weberror/evalexception.py', line 431 in respond
    app_iter = self.application(environ, detect_start_response)
  File '/home/aw/venv/dev/lib/python2.6/site-packages/Beaker-1.3-
  py2.6.egg/beaker/middleware.py', line 81 in __call__
    return self.app(environ, start_response)
  File '/home/aw/venv/dev/lib/python2.6/site-packages/Beaker-1.3-
  py2.6.egg/beaker/middleware.py', line 160 in __call__
    return self.wrap_app(environ, session_start_response)
  File '/home/aw/venv/dev/lib/python2.6/site-packages/Routes-1.10.3-
  py2.6.egg/routes/middleware.py', line 130 in __call__
    response = self.app(environ, start_response)
  File '/home/aw/venv/dev/lib/python2.6/site-packages/Pylons-0.9.7-
  py2.6.egg/pylons/wsgiapp.py', line 125 in __call__
    response = self.dispatch(controller, environ, start_response)
  File '/home/aw/venv/dev/lib/python2.6/site-packages/Pylons-0.9.7-
  py2.6.egg/pylons/wsgiapp.py', line 324 in dispatch
    return controller(environ, start_response)
  File '/home/aw/projects/resonate/corp/corp/lib/base.py', line 18 in
  __call__
    return WSGIController.__call__(self, environ, start_response)
  File '/home/aw/venv/dev/lib/python2.6/site-packages/Pylons-0.9.7-
  py2.6.egg/pylons/controllers/core.py', line 221 in __call__
    response = self._dispatch_call()
  File '/home/aw/venv/dev/lib/python2.6/site-packages/Pylons-0.9.7-
  py2.6.egg/pylons/controllers/core.py', line 172 in _dispatch_call
    response = self._inspect_call(func)
  File '/home/aw/venv/dev/lib/python2.6/site-packages/Pylons-0.9.7-
  py2.6.egg/pylons/controllers/core.py', line 107 in _inspect_call
    result = self._perform_call(func, args)
  File '/home/aw/venv/dev/lib/python2.6/site-packages/Pylons-0.9.7-
  py2.6.egg/pylons/controllers/core.py', line 60 in _perform_call
    return func(**args)
  File '/home/aw/projects/resonate/corp/corp/controllers/hits.py', line
  67 in referrers
    items_per_page = 50,
  File '/home/aw/venv/dev/lib/python2.6/site-packages/WebHelpers-0.6.4-
  py2.6.egg/webhelpers/paginate.py', line 333 in __init__
    self.item_count = len(self.collection)
  File '/home/aw/venv/dev/lib/python2.6/site-packages/WebHelpers-0.6.4-
  py2.6.egg/webhelpers/paginate.py', line 204 in __len__
    return self.obj.count()
  File '/home/aw/venv/dev/lib/python2.6/site-packages/SQLAlchemy-0.5.2-
  py2.6.egg/sqlalchemy/orm/query.py', line 1465 in count
    should_nest = should_nest[0]
  File '/home/aw/venv/dev/lib/python2.6/site-packages/SQLAlchemy-0.5.2-
  py2.6.egg/sqlalchemy/orm/query.py', line 1472 in _col_aggregate
    entity.setup_context(self, context)
  File '/home/aw/venv/dev/lib/python2.6/site-packages/SQLAlchemy-0.5.2-
  py2.6.egg/sqlalchemy/orm/query.py', line 1956 in setup_context
    if context.order_by is False and self.mapper.order_by:
  AttributeError: 'QueryContext' object has no attribute 'order_by'

  On Jun 19, 12:35 pm, Michael Bayer mike...@zzzcomputing.com wrote:
  Hollister wrote:

   When I run this, I get:

   Module sqlalchemy.orm.query:1956 in setup_context
         context.froms.append(self.selectable)
                  if context.order_by is False and self.mapper.order_by:
                      context.order_by = self.mapper.order_by  if
   context.order_by is False and self.mapper.order_by:

  I need a full stack trace on this since once from_statement() is called,
  setup_context() should never be called.

   AttributeError: 'QueryContext' object has no attribute 'order_by'

   This evidently has nothing to do with the query's order_by clause
   since I get the same error if I remove it. If I execute the query and
   pass the results to paginate:

   results = query.all()

           c.paginator = paginate.Page(
               results,
               page = int(request.params.get('page', 1)),
               items_per_page = 50,
           )

   I get:

   AttributeError: 'Hit' object has no attribute 'count'

   Which I guess makes sense, since I don't have that defined in my
   class.

   What am I doing wrong?


--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: aggregation with count and webhelpers.paginate

[Michael Bayer]

Hollister wrote:

 Ok, I see.
 So what's the best way for me to construct and execute this query?

use Query:

session.query(MyClass.someid, 
MyClass.somethingelse).filter(..whatever..).order_by(..whatever...)




 On Jun 19, 1:30 pm, Michael Bayer mike...@zzzcomputing.com wrote:
 you can't call count() when you've used from_statement, that should be
 raising an error.   the bug is that no error is being raised.

 Hollister wrote:

  Here you go:

  URL:http://dev:5000/hits/referrers
  File '/home/aw/venv/dev/lib/python2.6/site-packages/WebError-0.10.1-
  py2.6.egg/weberror/evalexception.py', line 431 in respond
    app_iter = self.application(environ, detect_start_response)
  File '/home/aw/venv/dev/lib/python2.6/site-packages/Beaker-1.3-
  py2.6.egg/beaker/middleware.py', line 81 in __call__
    return self.app(environ, start_response)
  File '/home/aw/venv/dev/lib/python2.6/site-packages/Beaker-1.3-
  py2.6.egg/beaker/middleware.py', line 160 in __call__
    return self.wrap_app(environ, session_start_response)
  File '/home/aw/venv/dev/lib/python2.6/site-packages/Routes-1.10.3-
  py2.6.egg/routes/middleware.py', line 130 in __call__
    response = self.app(environ, start_response)
  File '/home/aw/venv/dev/lib/python2.6/site-packages/Pylons-0.9.7-
  py2.6.egg/pylons/wsgiapp.py', line 125 in __call__
    response = self.dispatch(controller, environ, start_response)
  File '/home/aw/venv/dev/lib/python2.6/site-packages/Pylons-0.9.7-
  py2.6.egg/pylons/wsgiapp.py', line 324 in dispatch
    return controller(environ, start_response)
  File '/home/aw/projects/resonate/corp/corp/lib/base.py', line 18 in
  __call__
    return WSGIController.__call__(self, environ, start_response)
  File '/home/aw/venv/dev/lib/python2.6/site-packages/Pylons-0.9.7-
  py2.6.egg/pylons/controllers/core.py', line 221 in __call__
    response = self._dispatch_call()
  File '/home/aw/venv/dev/lib/python2.6/site-packages/Pylons-0.9.7-
  py2.6.egg/pylons/controllers/core.py', line 172 in _dispatch_call
    response = self._inspect_call(func)
  File '/home/aw/venv/dev/lib/python2.6/site-packages/Pylons-0.9.7-
  py2.6.egg/pylons/controllers/core.py', line 107 in _inspect_call
    result = self._perform_call(func, args)
  File '/home/aw/venv/dev/lib/python2.6/site-packages/Pylons-0.9.7-
  py2.6.egg/pylons/controllers/core.py', line 60 in _perform_call
    return func(**args)
  File '/home/aw/projects/resonate/corp/corp/controllers/hits.py', line
  67 in referrers
    items_per_page = 50,
  File '/home/aw/venv/dev/lib/python2.6/site-packages/WebHelpers-0.6.4-
  py2.6.egg/webhelpers/paginate.py', line 333 in __init__
    self.item_count = len(self.collection)
  File '/home/aw/venv/dev/lib/python2.6/site-packages/WebHelpers-0.6.4-
  py2.6.egg/webhelpers/paginate.py', line 204 in __len__
    return self.obj.count()
  File '/home/aw/venv/dev/lib/python2.6/site-packages/SQLAlchemy-0.5.2-
  py2.6.egg/sqlalchemy/orm/query.py', line 1465 in count
    should_nest = should_nest[0]
  File '/home/aw/venv/dev/lib/python2.6/site-packages/SQLAlchemy-0.5.2-
  py2.6.egg/sqlalchemy/orm/query.py', line 1472 in _col_aggregate
    entity.setup_context(self, context)
  File '/home/aw/venv/dev/lib/python2.6/site-packages/SQLAlchemy-0.5.2-
  py2.6.egg/sqlalchemy/orm/query.py', line 1956 in setup_context
    if context.order_by is False and self.mapper.order_by:
  AttributeError: 'QueryContext' object has no attribute 'order_by'

  On Jun 19, 12:35 pm, Michael Bayer mike...@zzzcomputing.com wrote:
  Hollister wrote:

   When I run this, I get:

   Module sqlalchemy.orm.query:1956 in setup_context
         context.froms.append(self.selectable)
                  if context.order_by is False and
 self.mapper.order_by:
                      context.order_by = self.mapper.order_by  if
   context.order_by is False and self.mapper.order_by:

  I need a full stack trace on this since once from_statement() is
 called,
  setup_context() should never be called.

   AttributeError: 'QueryContext' object has no attribute 'order_by'

   This evidently has nothing to do with the query's order_by clause
   since I get the same error if I remove it. If I execute the query
 and
   pass the results to paginate:

   results = query.all()

           c.paginator = paginate.Page(
               results,
               page = int(request.params.get('page', 1)),
               items_per_page = 50,
           )

   I get:

   AttributeError: 'Hit' object has no attribute 'count'

   Which I guess makes sense, since I don't have that defined in my
   class.

   What am I doing wrong?


 



--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en

[sqlalchemy] Re: aggregation with count and webhelpers.paginate

[Hollister]

Well, that worked great:

q = meta.Session.query(m.Hit.referer, func.count(m.Hit.id))\
.group_by(m.Hit.referer)\
.order_by(func.count(m.Hit.id).desc())

Thanks!

ps: Is there a better way to specify the count in the order_by?

On Jun 19, 2:58 pm, Michael Bayer mike...@zzzcomputing.com wrote:
 Hollister wrote:

  Ok, I see.
  So what's the best way for me to construct and execute this query?

 use Query:

 session.query(MyClass.someid,
 MyClass.somethingelse).filter(..whatever..).order_by(..whatever...)



  On Jun 19, 1:30 pm, Michael Bayer mike...@zzzcomputing.com wrote:
  you can't call count() when you've used from_statement, that should be
  raising an error.   the bug is that no error is being raised.

  Hollister wrote:

   Here you go:

   URL:http://dev:5000/hits/referrers
   File '/home/aw/venv/dev/lib/python2.6/site-packages/WebError-0.10.1-
   py2.6.egg/weberror/evalexception.py', line 431 in respond
     app_iter = self.application(environ, detect_start_response)
   File '/home/aw/venv/dev/lib/python2.6/site-packages/Beaker-1.3-
   py2.6.egg/beaker/middleware.py', line 81 in __call__
     return self.app(environ, start_response)
   File '/home/aw/venv/dev/lib/python2.6/site-packages/Beaker-1.3-
   py2.6.egg/beaker/middleware.py', line 160 in __call__
     return self.wrap_app(environ, session_start_response)
   File '/home/aw/venv/dev/lib/python2.6/site-packages/Routes-1.10.3-
   py2.6.egg/routes/middleware.py', line 130 in __call__
     response = self.app(environ, start_response)
   File '/home/aw/venv/dev/lib/python2.6/site-packages/Pylons-0.9.7-
   py2.6.egg/pylons/wsgiapp.py', line 125 in __call__
     response = self.dispatch(controller, environ, start_response)
   File '/home/aw/venv/dev/lib/python2.6/site-packages/Pylons-0.9.7-
   py2.6.egg/pylons/wsgiapp.py', line 324 in dispatch
     return controller(environ, start_response)
   File '/home/aw/projects/resonate/corp/corp/lib/base.py', line 18 in
   __call__
     return WSGIController.__call__(self, environ, start_response)
   File '/home/aw/venv/dev/lib/python2.6/site-packages/Pylons-0.9.7-
   py2.6.egg/pylons/controllers/core.py', line 221 in __call__
     response = self._dispatch_call()
   File '/home/aw/venv/dev/lib/python2.6/site-packages/Pylons-0.9.7-
   py2.6.egg/pylons/controllers/core.py', line 172 in _dispatch_call
     response = self._inspect_call(func)
   File '/home/aw/venv/dev/lib/python2.6/site-packages/Pylons-0.9.7-
   py2.6.egg/pylons/controllers/core.py', line 107 in _inspect_call
     result = self._perform_call(func, args)
   File '/home/aw/venv/dev/lib/python2.6/site-packages/Pylons-0.9.7-
   py2.6.egg/pylons/controllers/core.py', line 60 in _perform_call
     return func(**args)
   File '/home/aw/projects/resonate/corp/corp/controllers/hits.py', line
   67 in referrers
     items_per_page = 50,
   File '/home/aw/venv/dev/lib/python2.6/site-packages/WebHelpers-0.6.4-
   py2.6.egg/webhelpers/paginate.py', line 333 in __init__
     self.item_count = len(self.collection)
   File '/home/aw/venv/dev/lib/python2.6/site-packages/WebHelpers-0.6.4-
   py2.6.egg/webhelpers/paginate.py', line 204 in __len__
     return self.obj.count()
   File '/home/aw/venv/dev/lib/python2.6/site-packages/SQLAlchemy-0.5.2-
   py2.6.egg/sqlalchemy/orm/query.py', line 1465 in count
     should_nest = should_nest[0]
   File '/home/aw/venv/dev/lib/python2.6/site-packages/SQLAlchemy-0.5.2-
   py2.6.egg/sqlalchemy/orm/query.py', line 1472 in _col_aggregate
     entity.setup_context(self, context)
   File '/home/aw/venv/dev/lib/python2.6/site-packages/SQLAlchemy-0.5.2-
   py2.6.egg/sqlalchemy/orm/query.py', line 1956 in setup_context
     if context.order_by is False and self.mapper.order_by:
   AttributeError: 'QueryContext' object has no attribute 'order_by'

   On Jun 19, 12:35 pm, Michael Bayer mike...@zzzcomputing.com wrote:
   Hollister wrote:

When I run this, I get:

Module sqlalchemy.orm.query:1956 in setup_context
          context.froms.append(self.selectable)
               if context.order_by is False and
  self.mapper.order_by:
                   context.order_by = self.mapper.order_by  if
context.order_by is False and self.mapper.order_by:

   I need a full stack trace on this since once from_statement() is
  called,
   setup_context() should never be called.

AttributeError: 'QueryContext' object has no attribute 'order_by'

This evidently has nothing to do with the query's order_by clause
since I get the same error if I remove it. If I execute the query
  and
pass the results to paginate:

results = query.all()

        c.paginator = paginate.Page(
            results,
            page = int(request.params.get('page', 1)),
            items_per_page = 50,
        )

I get:

AttributeError: 'Hit' object has no attribute 'count'

Which I guess makes sense, since I don't have that defined in my
class.

What am I doing wrong?

[sqlalchemy] Re: Aggregation

[Paul Colomiets]

[EMAIL PROTECTED] wrote:
 hi, i have similar idea/need within dbcook, although on a somewhat 
 higher level:
 pre
 cache_results/: (dbcook/SA) add-on for automaticaly-updated database 
 denormalisation caches of intermediate results, each one depending on 
 particular pattern of usage. Wishful syntax:

 class SomeCacheKlas( Base):
 fieldname = cache_aggregator( klas.field, AggrFilterCriteria)
 #e.g.
 #class Cache4averagePerson( Base):
 #age= cache_agregators.Average( Person.age, Filter1 )
 #salary = cache_agregators.Sum( Person.salary, Filter2 )
 /pre
 i was thinking on using triggers and/or sql-functions but then this is 
 just one way to do it - and the idea is to hide the implementation.

   
I've seen that, but found no code.
Is it something only planned for dbcook?
 Can your implementation be extended to use: 
  a) more complex aggregator expressions (e.g. average(), that is 
 sum() / count(), and similar dependencies)
   
Definetly can. Now you can just use property for that:

avg = property(lambda self: self.sum / self.count)

I currently have no idea on how to make it better (less writing?)
Also documenting extension interface is in to do list.
  b) more complex filters - e.g. not max() on all rows, but on some 
 subset/select
   
Yes. I'm looking for syntax for that. Functionality seems
very similar for `relation()` so may be
`a.Max(...,primaryjoin=...)` would do.
 think of generic report calculations/aggregations, and putting those 
 into some cacheing table. 
 Eventualy getting the report being built on-the-run - distributing the 
 big wait over the atomary updates.


--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: Aggregation

[sdobrev]

On Wednesday 08 August 2007 12:18:24 Paul Colomiets wrote:
 [EMAIL PROTECTED] wrote:
  hi, i have similar idea/need within dbcook, although on a
  somewhat higher level:
  pre
  cache_results/: (dbcook/SA) add-on for automaticaly-updated
  database denormalisation caches of intermediate results, each one
  depending on particular pattern of usage. Wishful syntax:
 
  class SomeCacheKlas( Base):
  fieldname = cache_aggregator( klas.field,
  AggrFilterCriteria) #e.g.
  #class Cache4averagePerson( Base):
  #age= cache_agregators.Average( Person.age, Filter1 )
  #salary = cache_agregators.Sum( Person.salary, Filter2 )
  /pre
  i was thinking on using triggers and/or sql-functions but then
  this is just one way to do it - and the idea is to hide the
  implementation.

 I've seen that, but found no code.
 Is it something only planned for dbcook?
yes, to-be-done, as a way to avoid DB to become denormalized in some 
particular unplanned/uncontrolled way, that fits some reports and 
screws all else. It takes me a great fight to convince users that 
denormalization is out of app's bare model... its something u lay 
over it.

  Can your implementation be extended to use:
   a) more complex aggregator expressions (e.g. average(), that is
  sum() / count(), and similar dependencies)

 Definetly can. Now you can just use property for that:
 avg = property(lambda self: self.sum / self.count)
naaah, sorry, that was too easy. i mean more complex aggregation 
functions... i guess it can, if u can do sum=sum+x, then u could do 
sq=sq+x*x/2

   b) more complex filters - e.g. not max() on all rows, but on
  some subset/select

 Yes. I'm looking for syntax for that. Functionality seems
 very similar for `relation()` so may be
 `a.Max(...,primaryjoin=...)` would do.

hmmm... relation... yess, it is like a plural relation but getting a 
singular result out of it, and never ever loading the items. 
Are u using something along PropertyLoader?

ciao
svil

--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: Aggregation

[Paul Colomiets]

Michael Bayer wrote:
 i wonder though if theres some way that could get out of sync with  
 the actual number.  you'd have to be careful to establish this  
 update.  if you wanted to use a mapper extension to do it, then youd  
 have to issue the UPDATE directly, the change to the comment_count  
 attribute wont get picked up if its established within the flush itself.

Hi,

Thank you Michael for help, and for new great docs!

I've finally done first POC implementation of this feature.

Basic usage looks like:

import aggregator as a
mapper(Line, lines,
 extension=a.Quick(a.Count(blocks.c.lines),
a.Max(blocks.c.lastline, lines.c.id)))

(You also need foreign keys)

I've implemented two interfaces one
that counts like cnt = cnt+1 (called Quick)
second does query SELECT count(*) FROM 
at each insert/update/delete (called Accurate)

Well, Quick should be accurate as long as you either don't
update foreign keys involved in process, or have only one
Count aggregation on changable key (*), or use transactions.
So it covers most usecases I think. If you like to recalc you can
use Accurate which can be better in some edge cases.
A bit slower on updates but still fast on reads.

(*) - aggregations on update is not implemented yet, but it's
only a matter of time

For more comprehensive description look here:
http://www.mr-pc.kiev.ua/projects/SQLAlchemyAggregation

I have also two issues.

1. func.if_(...) - tries to sql function if_(...),
but func._if(...) - _i(...)
I believe is typo?

2.  If there a way, to handle functions in cross-database manner?

I've need to write something like this, for max function which
exists on sqlite but absent in mysql (and if() visa-versa):

if aggregator.mapper.local_table.\
metadata.bind.url.drivername == 'mysql':
return if_func(A  B, B, A)
else:
return func.max(A, B)

Is there a better way?
(Well, I will decrease number of dots it no better way exists :) )

--
Paul.


--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: Aggregation

[Michael Bayer]

On Aug 7, 1:20 pm, Paul Colomiets [EMAIL PROTECTED] wrote:

 1. func.if_(...) - tries to sql function if_(...),
 but func._if(...) - _i(...)
 I believe is typo?

er, probably.  func is trying to sidestep various _ underscore
attributes i think.  do you need to say _if(...) ?

 2.  If there a way, to handle functions in cross-database manner?


this is ticket 615 which seems to be becoming very important, so i
think ill move it to 0.4xx milestone and increase priority.


--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: Aggregation

[sdobrev]

 I've finally done first POC implementation of this feature.

 Basic usage looks like:

 import aggregator as a
 mapper(Line, lines,
  extension=a.Quick(a.Count(blocks.c.lines),
   a.Max(blocks.c.lastline, lines.c.id)))

 (You also need foreign keys)


hi, i have similar idea/need within dbcook, although on a somewhat 
higher level:
pre
cache_results/: (dbcook/SA) add-on for automaticaly-updated database 
denormalisation caches of intermediate results, each one depending on 
particular pattern of usage. Wishful syntax:

class SomeCacheKlas( Base):
fieldname = cache_aggregator( klas.field, AggrFilterCriteria)
#e.g.
#class Cache4averagePerson( Base):
#age= cache_agregators.Average( Person.age, Filter1 )
#salary = cache_agregators.Sum( Person.salary, Filter2 )
/pre
i was thinking on using triggers and/or sql-functions but then this is 
just one way to do it - and the idea is to hide the implementation.

Can your implementation be extended to use: 
 a) more complex aggregator expressions (e.g. average(), that is 
sum() / count(), and similar dependencies)
 b) more complex filters - e.g. not max() on all rows, but on some 
subset/select

think of generic report calculations/aggregations, and putting those 
into some cacheing table. 
Eventualy getting the report being built on-the-run - distributing the 
big wait over the atomary updates.


 http://www.mr-pc.kiev.ua/projects/SQLAlchemyAggregation
this gives me 404


--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: Aggregation

[Paul Colomiets]

[EMAIL PROTECTED] wrote:=

   
 http://www.mr-pc.kiev.ua/projects/SQLAlchemyAggregation
 
 this gives me 404
   
Sorry:

http://www.mr-pc.kiev.ua/en/projects/SQLAlchemyAggregator




--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: Aggregation

[Paul Colomiets]

Michael Bayer wrote:

 On Aug 7, 1:20 pm, Paul Colomiets [EMAIL PROTECTED] wrote:
   
 1. func.if_(...) - tries to sql function if_(...),
 but func._if(...) - _i(...)
 I believe is typo?
 

 er, probably.  func is trying to sidestep various _ underscore
 attributes i think.  do you need to say _if(...) ?

No I need to say IF but I can't say func.if() currently I use 
getattr('func','if') but It's ugly.

It strips last char when sees first underscore. But should ether strip 
first char, or check end of string for an underscore.

Patch attached :)

--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

Index: lib/sqlalchemy/sql.py
===
--- lib/sqlalchemy/sql.py	(revision 3197)
+++ lib/sqlalchemy/sql.py	(working copy)
@@ -741,7 +741,7 @@
 except KeyError:
 raise AttributeError(name)
 
-elif name.startswith('_'):
+elif name.endswith('_'):
 name = name[0:-1]
 f = _FunctionGenerator(**self.opts)
 f.__names = list(self.__names) + [name]

[sqlalchemy] Re: Aggregation

[Paul Colomiets]

Michael Bayer wrote:

 Hmmm, do you mean joining relations against a subrelation that uses  
 an aggregate like MAX ?  i'd like to see what you have in mind for this.
Well, I think I've not explained it correctly.

It looks quite like you're saying, but I want that aggregations
to be stored (cached?) in the table.

Suppose you have, a table of articles and comments on them.
I want to have article.last_comment and article.number_of_comments
to be stored in columns and to be updated when I'm saving Comment
object (which e.g. has a foreign key to article).
And I really want that to be atomic updates (update set cnt=cnt+1)
instead of updating counter on Article instance and saving it.


--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: Aggregation

[Michael Bayer]

On Aug 3, 2007, at 10:57 AM, Paul Colomiets wrote:


 Michael Bayer wrote:

 Hmmm, do you mean joining relations against a subrelation that uses
 an aggregate like MAX ?  i'd like to see what you have in mind for  
 this.
 Well, I think I've not explained it correctly.

 It looks quite like you're saying, but I want that aggregations
 to be stored (cached?) in the table.

 Suppose you have, a table of articles and comments on them.
 I want to have article.last_comment and article.number_of_comments
 to be stored in columns and to be updated when I'm saving Comment
 object (which e.g. has a foreign key to article).
 And I really want that to be atomic updates (update set cnt=cnt+1)
 instead of updating counter on Article instance and saving it.

we've just added the atomic update thing to 0.4 (note the uppercase  
Article which produces a column expression):

article.comment_count = Article.comment_count + 1
session.flush()

i wonder though if theres some way that could get out of sync with  
the actual number.  you'd have to be careful to establish this  
update.  if you wanted to use a mapper extension to do it, then youd  
have to issue the UPDATE directly, the change to the comment_count  
attribute wont get picked up if its established within the flush itself.



--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: Aggregation

[Michael Bayer]

On Aug 3, 2007, at 5:57 AM, Paul Colomiets wrote:


 Hi,

 Is there some aggregation ability built-in in the SQLAlchemy?

 I want some simple functions like counting rows and determining the  
 last
 one.
 I know it's something simple to implement with Mapper Extensions,  
 but may be
 there are readily available solutions?

 And is there interest if I'll make some code on that?
 --
 Paul.

Hmmm, do you mean joining relations against a subrelation that uses  
an aggregate like MAX ?  i'd like to see what you have in mind for this.


--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: Aggregation

[Paul Colomiets]

Michael Bayer wrote:
 we've just added the atomic update thing to 0.4 (note the uppercase  
 Article which produces a column expression):

 article.comment_count = Article.comment_count + 1
 session.flush()
   
That's great!
It's quite unconvenient here, but has a lot of good use cases.
 i wonder though if theres some way that could get out of sync with  
 the actual number.  you'd have to be careful to establish this  
 update.  if you wanted to use a mapper extension to do it, then youd  
 have to issue the UPDATE directly, the change to the comment_count  
 attribute wont get picked up if its established within the flush itself.
If you mean out of sync with article instance - it's not
a problem. If you have a highly concurent database you'll
get out of sync instances in any case, if not - field will be
quite up to date.

Well, while writing this e-mail I've realized that there can
be some problems with deletes and updates that probably
can make value out of sync.

I'll just try and give a feedback after that :)
--
Paul.





--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---