DAL problem

[TheDude]

def index():
rows = (db.mb_posts.board_id==db.mb_boards.id)&
(db.mb_posts.reply_to==0)
rows2 = (db.mb_posts.board_id==db.mb_boards.id)
mb_posts2 = db.mb_posts.with_alias("mb_posts2")
query = db().select(db.mb_boards.ALL, db.mb_posts.id.count(),
db.mb_posts2.id.count(), left=(db.mb_posts.on(rows),db.mb_posts2.on
(rows2)), groupby=db.mb_boards.id)
return dict(query=query)

gives me
OperationalError: (1066, "Not unique table/alias: 'mb_posts'")

However, should mb_posts2 show itself as LEFT JOIN mb_posts AS
mb_posts2 ?

[mdipierro]

should be

def index():
rows = (db.mb_posts.board_id==db.mb_boards.id)&
(db.mb_posts.reply_to==0)

mb_posts2 = db.mb_posts.with_alias("mb_posts2")

rows2 = (db.mb_posts2.board_id==db.mb_boards.id)

query = db().select(db.mb_boards.ALL, db.mb_posts.id.count(),
db.mb_posts2.id.count(), left=(db.mb_posts.on(rows),db.mb_posts2.on
(rows2)), groupby=db.mb_boards.id)
return dict(query=query)

[TheDude]

Massimo,
I still get the error:
Traceback (most recent call last):
File "/home/daniel/web2py/gluon/restricted.py", line 98, in
restricted
exec ccode in environment
File "/home/daniel/web2py/applications/community/controllers/
forum.py", line 9, in <module>
File "/home/daniel/web2py/gluon/globals.py", line 75, in <lambda>
self._caller = lambda f: f()
File "/home/daniel/web2py/applications/community/controllers/
forum.py", line 5, in index

query = db().select(db.mb_boards.ALL, db.mb_posts.id.count(),
db.mb_posts2.id.count(), left=(db.mb_posts.on(rows),db.mb_posts2.on
(rows2)), groupby=db.mb_boards.id)

File "/home/daniel/web2py/gluon/sql.py", line 1902, in select
r = response(query)
File "/home/daniel/web2py/gluon/sql.py", line 1897, in response
self._db._execute(query)
File "/home/daniel/web2py/gluon/sql.py", line 634, in <lambda>
**b)
File "/var/lib/python-support/python2.5/MySQLdb/cursors.py", line
166, in execute
self.errorhandler(self, exc, value)
File "/var/lib/python-support/python2.5/MySQLdb/connections.py",
line 35, in defaulterrorhandler
raise errorclass, errorvalue

OperationalError: (1066, "Not unique table/alias: 'mb_posts'")

forum.py:

def index():
rows = (db.mb_posts.board_id==db.mb_boards.id)&
(db.mb_posts.reply_to==0)
mb_posts2 = db.mb_posts.with_alias("mb_posts2")
rows2 = (db.mb_posts2.board_id==db.mb_boards.id)
query = db().select(db.mb_boards.ALL, db.mb_posts.id.count(),
db.mb_posts2.id.count(), left=(db.mb_posts.on(rows),db.mb_posts2.on
(rows2)), groupby=db.mb_boards.id)
return dict(query=query)

response._vars=response._caller(index)

[mdipierro]

ry

def index():
rows = (db.mb_posts.board_id==db.mb_boards.id)&
(db.mb_posts.reply_to==0)
mb_posts2 = db.mb_posts.with_alias("mb_posts2")

rows2 = (mb_posts2.board_id==db.mb_boards.id)
query = db().select(db.mb_boards.ALL, db.mb_posts.id.count(),
mb_posts2.id.count(), left=(db.mb_posts.on(rows),mb_posts2.on
(rows2)), groupby=db.mb_boards.id)
return dict(query=query)

[TheDude]

SQL Works! However...

{{=board.mb_boards.keys()}}
brings up:
['update_record', 'mb_posts', 'description', 'id', 'name']

As you can see, no mb_posts2 (mb_posts does not contain mb_posts2
either). So, I said to myself may be it's because of the .ALL within
the select. Still no go.

[mdipierro]

what is "board"?

[TheDude]

Sorry, it's in my views :)

{{extend 'layout.html'}}
<h2>Message Boards</h2>
<table align="center" width="90%" cellspacing="1" cellpadding="3"
class="data">
<tr>
<th>Board</th>
<th>Threads</th>
<th>Posts</th>
<th>Last Topic</th>
</tr>
{{for board in query:}}
<tr>
<td>{{=A(board.mb_boards.name, _href=URL(r=request, f='view',
args=board.mb_boards.id))}}<br />{{=board.mb_boards.description}}</td>
<td>{{=board.mb_boards.mb_posts.count()}}</td>
<td>{{=board.mb_boards.keys()}}</td>
<td></td>
</tr>
{{pass}}
</table>

[TheDude]

So, any suggestions?

[mdipierro]

try:
{{=board.mb_boards.keys()}}
{{=board._extra}}

On Apr 2, 5:20 pm, TheDude <officed...@gmail.com> wrote:

[TheDude]

RIght tried those already...
.keys() brings up ['update_record', 'mb_posts', 'description', 'id',
'name']
board._extra = no xml error

I tried listing keys() and dir() mb_posts (see if it was somehow
placed in there but it isn't).

However, {{=query}} brings this...
mb_boards.id mb_boards.name mb_boards.description COUNT(mb_posts.id)
COUNT(mb_posts2.id)
1 Announcements All important... 1 1
2 General Discu... General talk ... 1 1
3 Suggestions/B... Report any bu... 0 0
4 Off-Topic Not everythin... 0 0

[mdipierro]

{{=board._extra[mb_posts2.id.count()]}} ?

[TheDude]

THat didn't work but..

{{=board._extra.keys()}} brings up ['COUNT(mb_posts.id)', 'COUNT
(mb_posts2.id)']

So I did {{=board._extra['COUNT(mb_posts2.id)']}} and it works!

Now, the only problem I have with this, is that it doesn't make to
much logical sense to do things this way (it doesn't seem like a
framework way more like a quick hack). Will there be any modifications
within this area in 1.6?

[mdipierro]

There is a logic and these two

{{=board._extra['COUNT(mb_posts2.id)']}}
{{=board._extra[mb_posts2.id.count()]}}
should be equivalent, in fact {{=mb_posts2.id.count()}} should be
'COUNT(mb_posts2.id)'.

The rationale is that rows=db(...).select
(table.field,table.field.aggregate())

table field goes in rows[i].table.field
while aggregates are not fields so they go in rows[i]._extra
[table.field.aggregate()]

Massimo

[TheDude]

Massimo, makes sense now that you've explained it a bit. Thanks. :)