How can I efficiently query products with many-to-many ingredients and nested products?

Question

I currently have three tables: product, ingredient, and product_ingredient (m2m relationship).

A product has many ingredients and nested products through product_ingredient. The nested products themselves have many ingredients and nested products.

How can I efficiently query these relationships so that a single query can select a product's ingredients through multiple levels of nested relationships?

• The product ingredients should be ordered by their position int, with respect to its product_id.
• Each level of nested ingredients need to be grouped and ordered by the product they're related to.
• The entities are read more frequently than written, so read optimized would be preferred if there are any cache-based approaches (like materialized views).
• The actual structure has foreign keys on the ingredient table which are aggregated in different ways.

For example:

> select * from product;
 id | name
----+------------------------
  1 | chocolate
  2 | chocolate chip cookies
> select * from ingredient;
 id | name
----+--------------
  1 | cocoa
  2 | cocoa butter
  3 | flour
  4 | eggs
  5 | baking soda
> select * from product_ingredient;
 id | product_id | ingredient_id | nested_product_id | position
----+------------+---------------+-------------------+----------
  1 |          1 |             2 |            (null) |        0
  2 |          1 |             1 |            (null) |        1
  3 |          2 |             3 |            (null) |        0
  4 |          2 |             4 |            (null) |        1
  5 |          2 |        (null) |                 1 |        2
  6 |          2 |             5 |            (null) |        3

My ideal query would produce the following output:

> select ...;
 product_id |           product_name | ingredient_id | ingredient_name | position
------------+------------------------+---------------+-----------------+----------
          2 | chocolate chip cookies |             3 | flour           |        0
          2 | chocolate chip cookies |             4 | eggs            |        1
          1 |              chocolate |             2 | cocoa butter    |        0
          1 |              chocolate |             1 | cocoa           |        1
          2 | chocolate chip cookies |             5 | baking soda     |        3

Note that the sort order is maintained using position through the nested relationships.

Additionally, how do I prevent a circular relationships, where a product_ingredient references a nested_product_id that is a parent or grandparent relation? Is there a CONSTRAINT that I can use?