Re: [web2py] Re: SQL Query Help Using DAL

[David J.]

Massimo;

Thanks, this sort of works; The numbers seem off but this gives a good 
starting point;

One other thing I would like to extend the table to include on other 
field 'mtype'

which would be a value of 'text' or 'media',


If I wanted to extend the query to use the previous query and then for 
each type show the results.

So my view would look like this

Text
user, messages sent, messages received
Joe User,15,20
User 2,20,15

Media
user, messages sent, messages received
Joe User,100,200
User 2,220,150

How can I pass the type as part of the query?

Thanks again.

David.




On 6/24/11 3:27 PM, Massimo Di Pierro wrote:
I am guessing, I have not tried it

mfrom = db.messages.with_alias('mfrom')
mto = db.messages.with_alias('mfrom')

db(db.auth_user).select(db.auth.user.first_name,
db.auth_user.last_name,
      mfrom.id.count(), mto.id.count(),

left=[mfrom.on(mfrom.from==db.auth_user.id),mto.on(mto.to==db.auth_user.id)],
      groupby = db.auth_user.id)

On Jun 24, 2:18 pm, "David J."<da...@styleflare.com>  wrote:
I am trying to display chat message information;

I have 2 tables

The first table is the auth table define in web2py

The second is this

db.define_table('messages',
      Field('from',db.auth_user),
      Field('to',db.auth_user),
      Field('msg','string'),
      Field('ts','datetime',default=request.now)
)

What I want to do do now is create a report on how many messages where
sent and received by each user;

I want the output to look like this

user, messages sent, messages received
Joe User,15,20
User 2,20,15

I am trying to figure it out using DAL and grouping but this is way over
my head;

Any help would be appreciated.

Thanks.

David.