Re: How can I reduce the number of queries to my PostgreSQL database?

Martin Christensen said:

SR> Scenario: I have a python script which creates web page listing
SR> all books in the database, and all authors for each book. My
SR> python script does essentially three things:

SR> 1. retrieve a list of all book_ids and book_titles.

SR> 2. for each book_id, query the bookauthors table and retrieve all
SR> author names for that book_id.

SR> 3. display it all out as an html table on a web page.

That's one query, if you're willing to make it advanced enough,
although you need to make an aggregate to enable PostgreSQL to
concatenate and comma separate author names. However, this aggregate
will typically need more than one database function. Such an aggregate
could be as follows:

<SNIP>

This is the solution that I would use after working nearly a decade
with databases. It is neither simple nor obvious to the novice, but
it's the Right Way To Do It. For a learning exercise, this is way over
the top, but I thought you might benefit from seeing that - so long as
you only need information that would reasonably fit in one table on a
web page or the equivalent - one query is always enough. Or perhaps
that should be One Query Is Always Enough. :-) Learn at your own pace,
though, but you might want to keep this in mind for future reference.

Thanks for that... I'm not going to argue with a decade's experience!
I'd never heard of aggregates before, but I'll look into them. Perhaps
I'll be able to impress my friends with them one day.

The reason for keeping the authors separate was to wrap them with an
appropriate HTML href, but presumably your solution could be adapted
for this purpose?

Cheers,

Shay

.

Relevant Pages

  • Re: Internet class differences...
    ... The database and web interface to the server is produced by a different ... Initially a WebClient class was used. ... This worked fine for submitting the query. ... retrieve the results but, instead, is sent to a login page from which it ...
    (microsoft.public.dotnet.general)
  • Re: Drilltrough Question
    ... not query the underlying relational database, ... that in AS2000 drilllthrough queries retrieved data from the underlying ... I could retrieve any column from any table in the ...
    (microsoft.public.sqlserver.olap)
  • Re: How can I reduce the number of queries to my PostgreSQL database?
    ... SR> all books in the database, and all authors for each book. ... That's one query, if you're willing to make it advanced enough, ... although you need to make an aggregate to enable PostgreSQL to ...
    (comp.lang.python)
  • Re: Datetime within past week
    ... Prior to adding a new record to the database, I want to run a query to ... retrieve all of the records uploaded in the last 7 days. ...
    (comp.lang.php)
  • Re: Error 3420 Object invalid or no longer set under Vista
    ... Database is local, nothing across a network. ... Problem is in the ORDER BY statement of the problem query below ... fact that the Points field is an aggregate field in the subquery, ... draws from 2 other queries, the problem could be lower down. ...
    (microsoft.public.access.queries)