Questions on specifying table relationships

Поиск
Список
Период
Сортировка
От Patrick Bakker
Тема Questions on specifying table relationships
Дата
Msg-id A9CE1D556F89DD4FBA4CF797215DF61A02F364@20svbl1.vanbelle.local
обсуждение исходный текст
Ответы Re: Questions on specifying table relationships  (Richard Huxton <dev@archonet.com>)
Список pgsql-general

Hi all.
My question follows eventually but requires some explanation. I'm not entirely sure which list this should go to so general seems appropriate.

Suppose I want to find out which items a customer purchased from which orders using the following tables:
  customer, order, order_line, item.

When making joins for a query I've always written them as:

FROM
  (item INNER JOIN
    (order_line INNER JOIN
      (order INNER JOIN
                customer
      ON order.fk_customer = customer.pk_customer)
    ON order_line.fk_order = order.pk_order)
  ON item.pk_item = order_line.fk_item)

Currently, I'm in the middle of an Java/EJB-style application where I'd like to autogenerate queries. The user constructs a query by choosing options regarding which fields they want to show, how they want to sort them, how they want to group them, and how they want to limit the selection. Essentially all of the basics of an SQL query. However, they only see the fields using nice names and depending on their choices the query may result in varying joins.

So if I follow my hand-coding practise I need to arrange the tables in an order so that the joins are always 1 table apart. This feels unnecessary somehow. Also, I sometimes get confused exactly how to write queries when a single table requires multiple joins and they're not all necessarily INNER JOINs. For example, suppose the item table also links to a UPC table and a price table but the item may not have a UPC or a price:

FROM
  (item_upc RIGHT JOIN
    (item INNER JOIN
      (item_price LEFT JOIN
        (order_line ...

I'm fairly sure I've done this before but I feel like I should order the joins so that the actual table relationships are 1 line apart. ie. the item INNER JOIN should always be immediately before order_line ... Do I understand it correctly that order_line will effectively be 'inner joined' with the entire "meta upc-item-price table" in this case?

But back to the automatic query generation (I'm having trouble finding the words to say exactly what my question is - perhaps somebody can suggest what my question is if they can see what I'm getting at but not quite saying ...) - I'd like to avoid trying to automatically generate this series of joins given only a list of tables and the fields which join the tables together. My thought was to just dump these down into the WHERE clause of the query but in that case won't I loose all ability to do anything but an INNER JOIN? Also, is some method of specifying joins more efficient or better suited to PostgreSQL?

Having said that, now I'm also wondering how I would know what type of join should exist between tables (ie. when I manually form the query I know an item doesn't always have a UPC).

I'm not entirely clear what I'm asking here but I think it comes down to confirming the following:
 (Q) What do I need to autogenerate the relationships in a query?
 (A)
        - tables used in the query
        - fields connecting the tables together
        - what type of join exists between each table

 (Q) Since the user can choose fields from tables which are not directly connected, what is the best way to
     determine a relationship/join path to connect the tables? ie. which items did this customer buy?
     would require item -> order_line -> order -> customer given only item and customer ...

        - something is missing here: because item -> purchase_line -> purchase_order -> customer could also
        match (unless purchase_order people are listed in vendor instead of customer ...)

 (Q) What's the best way of writing the join in PostgreSQL for this purpose?

Sorry for the brain dump but I would appreciate it somebody can clarify anything.
Patrick

В списке pgsql-general по дате отправления:

Предыдущее
От: Stuardo Rodriguez
Дата:
Сообщение: testing
Следующее
От: "Paul Ottar Tornes"
Дата:
Сообщение: I cant add a new user to pgSQL.. What is wrong?