idea: select from multiple tables

[prev] [thread] [next] [Date index for 2004/07/27]

Hi,

suppose following tables (Pg syntax):

----------
create table customer {
   id serial,
   address text
};

create table goods {
   id serial,
   name text
}

create table orders {
   id serial,
   customer int references customer,
   goods int references goods,
   time_order int
};

----------

# snipped CDBI def

Orders->has_a (customer => 'Customer');
Orders->has_a (goods => 'Goods');

Customer->has_many (orders_list => Orders => 'customer');

Goods->has_many (orders_list => Orders => 'goods');

----------

Is there any way in CDBI, how to load two (or more) objects in one row ?
Idea:
my $order_list = Orders->search
  ( goods => Goods->search (name => 'something')->next,
    -with => { customer => 'All',
               orders => 'All',
             }
  );

It would:
  - return Orders, with forced load of all columns (not only essential)
  - also, all related Customer is loaded, with essential columns

In sql (maybe not functional, it's only idea):
select orders.*, customer.* from orders, customer where orders.goods = ? 
and orders.customer = customer.id;

Another possible task:
my $customer_list = Customer->retrieve_all
   (-with => { orders_list => 'count',
               customer => 'All',
             });

It:
  - load Customer with all columns
  - also, load count of orders (possible creation of 'orders_list_count' 
TEMP column)


I would ask you for any comment, if it is usable for you, for wishes and 
so ...

barney