[SQLObject] Inefficient MultipleJoin

[Andrey L. <an...@mi...>]

Hi, 

I'm evaluating SQLObject and it MultipleJoin functionality works in
highly inefficient way for me. I have the following simple postgresql
database:

CREATE TABLE public.customer
(
  id serial NOT NULL,
  dn varchar(255) NOT NULL,
  title varchar(255),
  discount int2,
  pricing int4,
  balance float8 NOT NULL DEFAULT 0,
  CONSTRAINT customer_pkey PRIMARY KEY (id),
  CONSTRAINT "$1" FOREIGN KEY (pricing) REFERENCES public.pricing (id) ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
  CONSTRAINT customer_dn_key UNIQUE (dn)
) 

CREATE TABLE public.pricing
(
  id serial NOT NULL,
  date date NOT NULL,
  description text,
  CONSTRAINT pricing_pkey PRIMARY KEY (id)
) 

And theese SQLObject classes:

class Customer(SQLObject):
    dn = StringCol()
    balance = FloatCol()
    pricing = ForeignKey('Pricing', dbName='pricing')

class Pricing(SQLObject):
    date = DateCol()
    description = StringCol()
    customers = MultipleJoin('Customer', joinColumn='pricing')


So, now the problem:

When I do:
    pricings = Pricing.select()
    for prsg in pricings:
	print prsg.customers

SQLObjects first fetches list of ids in 'customer' table and then executes
additional queries for EACH found id, like this:

 2/QueryAll:  SELECT id FROM customer WHERE pricing = 4
 2/COMMIT  :  auto
 2/QueryOne:  SELECT dn, discount, balance, pricing FROM customer WHERE id = 28
 2/COMMIT  :  auto
 2/QueryOne:  SELECT dn, discount, balance, pricing FROM customer WHERE id = 2
 2/COMMIT  :  auto
 2/QueryOne:  SELECT dn, discount, balance, pricing FROM customer WHERE id = 187
 2/COMMIT  :  auto
 2/QueryOne:  SELECT dn, discount, balance, pricing FROM customer WHERE id = 186
 2/COMMIT  :  auto
 2/QueryOne:  SELECT dn, discount, balance, pricing FROM customer WHERE id = 170
 2/COMMIT  :  auto
 2/QueryOne:  SELECT dn, discount, balance, pricing FROM customer WHERE id = 161
 ...
 ...

I must admit this way of doing things is terribly unscalable. Isn't it
better to do single "SELECT id, dn, discount, ... FROM customer WHERE
pricing = 4"? Or am I doing something wrong?

Thanks in advance.


-- 
Andrey Lebedev aka -.- . -.. -.. . .-.
Software engineer at UAB Mikromarketingas (http://micro.lt)
Homepage: http://micro.lt/~andrey/
Jabber ID: ke...@ja...