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." <[email protected]> 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.
