DAL SQL exercise of the day

[DenesL]

Using the person & dog tables, from the examples in the book, create a
query to find which persons have less than 4 dogs (0-3).

It seems deceivingly simple, but it requires you to apply several
concepts and constructs, some only documented in this user group (not
in the book yet).

Massimo, please let the others sweat it out.

Additional points for describing why each piece is needed.
Have fun!

[Thadeus Burgess]

Also, reply only to DenesL, so you don't give away the answer!
--
Thadeus

> --
> To unsubscribe, reply using "remove me" as the subject.
>

[DenesL]

One answer in and while warm it uses for/if ...
it can be done with just DAL.

[DenesL]

One correct answer in, with explanation!.
I apologize for not replying to the emails but my account has been
blocked for some reason, the matter has been reported and hopefully
will be resolved soon.

[DenesL]

Nobody else? Need more time?
Speak up or else... I will will post the answer.

[Kuba Kucharski]

I don't think so, please post :)

--
Kuba

On Thu, Apr 15, 2010 at 4:49 AM, DenesL <dene...@yahoo.ca> wrote:
> Nobody else? Need more time?
> Speak up or else... I will will post the answer.
>
>

[DenesL]

Time to reveal the solution.
Lets start with the simple table definitions:

db.define_table('person',
Field('name'),
)

db.define_table('dog',
Field('name'),
Field('owner', 'reference person'),
)

which creates a one-to-many relation, since a person can own many
dogs.
To illustrate what goes on I loaded them with:

>>> print db(db.person.id>0).select()
person.id,person.name
1,Ana
2,Ben
3,Cid
4,Don
5,Eve

>>> print db(db.dog.id>0).select()
dog.id,dog.name,dog.owner
1,Rex,2
2,Fifi,1
3,Max,2
4,Ringo,4
5,Lassie,5
6,Rover,2

Now, to find persons that don't own a dog we need a left join:

>>> print db().select(db.person.ALL, db.dog.ALL, left=db.dog.on( db.person.id == db.dog.owner ))
person.id,person.name,dog.id,dog.name,dog.owner
1,Ana,2,Fifi,1
2,Ben,1,Rex,2
2,Ben,3,Max,2
2,Ben,6,Rover,2
3,Cid,<NULL>,<NULL>,<NULL>
4,Don,4,Ringo,4
5,Eve,5,Lassie,5

as you can see persons without a dog will have dog.id, dog.name, and
dog.owner as <NULL>
Now we need to count. One of the proposed solutions used:

count=db.person.id.count()

which at first might seem correct but it actually counts people not
dogs, our target.
So the count statement has to use the dog table.

count=db.dog.id.count()

Since we don't want to count how many rows are in the left join but
how many times each person appears with a dog we have to group the
rows by person, using any of its fields.

>>> print db().select(db.person.ALL, db.dog.ALL, count, left=db.dog.on( db.person.id == db.dog.owner ), groupby=db.person.id)
person.id,person.name,dog.id,dog.name,dog.owner,COUNT(dog.owner)
1,Ana,2,Fifi,1,1
2,Ben,6,Rover,2,3
3,Cid,<NULL>,<NULL>,<NULL>,0
4,Don,4,Ringo,4,1
5,Eve,5,Lassie,5,1

Things to note:
- count is now a field in the rows.
- using groupby=db.person.name produces the exact same results.
- you can use dog.name or dog.owner to count and get the same result.

Since we have the person name and the dog count we can drop all the
other fields.

>>> print db().select(db.person.name, count, left=db.dog.on( db.person.id == db.dog.owner ), groupby=db.person.id)
person.name,COUNT(dog.id)
Ana,1
Ben,3
Cid,0
Don,1
Eve,1

Looking good, but how to limit this to a certain number of dogs?.

That is where 'having' comes into play, this is the undocumented (in
the book) construct I was referring to on the initial post.
It is not even on the "New features not documented in PDF book (2 ed)"
pinned post (omission that shall be fixed soon) but fortunately the
search mentioned in "web2py help & resources" post
(http://groups.google.com/groups/advanced_search?
safe=off&q=group:web2py)
with argument 'having' does produce the announcement post "DAL has
having"
http://groups.google.com/group/web2py/msg/4cd49ce7e2f64329

I changed the restriction to less than 3 dogs just to see the effect
on the current sample data, for the original problem just replace the
3 with a 4:

>>> print db().select(db.person.name, count, left=db.dog.on( db.person.id == db.dog.owner ), groupby=db.person.id, having=count<3)
person.name,COUNT(dog.id)
Ana,1
Cid,0
Don,1
Eve,1

and there you have it.
I hope the participants enjoyed the challenge.

Denes.