You can replace
mfrom.on(mfrom.from==db.auth_user.id)
with
mfrom.on((mfrom.from==db.auth_user.id)&(mfrom.type=='text'))
and the same with the mto.
On Jun 24, 3:06 pm, "David J." <da...@styleflare.com> wrote:
> 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.
