Patch to give Recipe module some Views2 support

patch attached.

and of course, recipe.module needs:

/**
* Implementation of hook_views_api().
*/
function recipe_views_api() {
return array(
'api' => 2,
'path' => drupal_get_path('module', 'recipe'),
);
}

OK the JOINs work without generating user warnings, and can display Fields from all 4 of the recipe tables. I am partly basing this on the multi-table join shown at http://views-help.doc.logrus.com/help/views/api-tables

The 1 remaining major problem is getting > 1 ingredient to display. That is, if you create recipe content and add say 3 ingredients to it, you should be able to query all the interesting bits and then get the whole recipe. Instead you get "just" one of the ingredients used by that recipe NID.

Generated SQL:
SELECT recipe.nid AS nid,
recipe_ingredient.name AS recipe_ingredient_name,
recipe_node_ingredient.quantity AS recipe_node_ingredient_quantity,
recipe_node_ingredient.nid AS recipe_node_ingredient_nid
FROM recipe recipe
LEFT JOIN recipe_node_ingredient recipe_node_ingredient ON recipe.nid = recipe_node_ingredient.id
LEFT JOIN recipe_ingredient recipe_ingredient ON recipe_node_ingredient.ingredient_id = recipe_ingredient.id
WHERE recipe.nid = 2

Example (problem) View:
Recipe NID Ingredient Name Quantity Ingredient NID
2 barley 6 2

Example (what-I-want) View:
Recipe NID Ingredient Name Quantity Ingredient NID
2 barley 6 2
2 hops 6 1
2 water 6 3
(or alternately, the above data but without the duplicated fields.)

At one earlier point in time, I actually GOT multiple rows (one per ingredient). Unfortunately I don't know what I did (and at that time, my code had other errors so I didn't save a copy).

You can demonstrate the problem w/o my database, but here's an example table dump:
mysql> select * from recipe;
+-----+-----------------------------+-------+----------------------------+------------------+----------+
| nid | source | yield | instructions | notes | preptime |
+-----+-----------------------------+-------+----------------------------+------------------+----------+
| 2 | This is an original recipe. | 5 | Instructions go here...... | Notes go here... | 5 |
+-----+-----------------------------+-------+----------------------------+------------------+----------+

mysql> select * from recipe_node_ingredient;
+----+-----+---------+----------+---------------+
| id | nid | unit_id | quantity | ingredient_id |
+----+-----+---------+----------+---------------+
| 1 | 2 | 15 | 1 | 1 |
| 2 | 2 | 14 | 6 | 2 |
| 3 | 2 | 18 | 5 | 3 |
+----+-----+---------+----------+---------------+

mysql> select * from recipe_ingredient;
+----+--------+------+
| id | name | link |
+----+--------+------+
| 1 | hops | 0 |
| 2 | barley | 0 |
| 3 | water | 0 |
+----+--------+------+

mysql> select * from recipe_unit;
+----+---------------------+--------------+--------+--------+
| id | name | abbreviation | metric | type |
+----+---------------------+--------------+--------+--------+
| 1 | Slice | sli | 0 | Unit |
| 2 | Unit | | 0 | Unit |
| 3 | Clove | clv | 0 | Unit |
| 4 | Pinch | pn | 0 | Unit |
| 5 | Package | pk | 0 | Unit |
| 6 | Can | cn | 0 | Unit |
[... truncated, not important...]
| 29 | Unknown | | 0 | Unit |
+----+---------------------+--------------+--------+--------+

update: this is how I think the join should look like, ASCII style:

    /* joins
    +--------------+     +------------------------+     +-------------------+     +--------------------+
    | recipe       |     | recipe_node_ingredient |     | recipe_ingredient |     | recipe_unit        |
    +--------------+     +------------------------+     +-------------------+     +--------------------+
    | nid          |<--  | id                     |   +-| id                |  +--| id                 |
    | source       |  +--| nid                    |   | | name              |  |  | name               |
    | yield        |     | unit_id                |<-+| | link              |  |  | abbreviation       |
    | instructions |     | quantity               |  || +-------------------+  |  | metric             |
    | notes        |     | ingredient_id          |<-|+                        |  | type               |
    | preptime     |     +------------------------+  +-------------------------+  +--------------------+
    +--------------+   */

Edit: Added <code> tags to make chart readable.

Edit: recipe has 1 row per recipe. There can be 'many' recipe_node_ingredient rows per recipe. Each recipe_node_ingredient row has a 1:1 relationship to recipe_ingredient. Each recipe_node_ingredient row has a 1:1 relationship to recipe_unit. The way the join is done currently (see above patch) I get just 1 of every field, so specifically I only get "one ingredient" (from recipe_node_ingredient), instead of all ingredients per nid.

Per advice from Earl, I made 'node' the base table and it all centers around node.nid instead of recipe.nid, which should let the system manage things more for me. (That's also what the original patch looked like, when I actually managed to get 1 row per ingredient, meaning I actually got all the data for a recipe [but there were other problems, possibly unrelated])

Revolving around node.nid in this patch... my current code breaks (see new attachment):
1) Content type does not appear in /admin/build/views/add
2) views->list shows 7 instances of "warning: preg_match() expects parameter 2 to be string, array given in /var/www/html/includes/bootstrap.inc on line 777."

What I am trying for at this point is to just get the prior functionality working with node.nid as the center. There's still other stuff that needs adding (prerender_list for example) but my understanding is that isn't required at this stage, just to get the joins working again.

I'll keep plugging on this, but any pointers would be appreciated.

OK, with lots of help from Earl/merlinofchaos, this Views2 is working here now. Recipe is also CCK aware, so you can bring recipe content into CCK and add an ImageField. Recipes with PICTURES... yum!

Still lots of cleanup to do, and I still suffer multiple repeated fields (a 1:many side effect issue)

Hi jwells. Yes, Recipe needs some development love.

People use Recipe in production (I don't have any production site, yet).

You should be able to apply my patch against the stable release, or dev.

Be sure this does what you want/need, as again it's not moving forward at the moment and it is what it is. You can improve it via traditional forms API + new features if you can... I am focused on learning Views better (I'm an API newbie). After Views, I plan to extend it to support homebrew beer recipes (my true itch here).

I think the few checkin maintainers with access to Recipe are all busy on D7, so until then, this is the pace of development. D7 might offer a better platform for a Recipe rewrite.

Guy, all - To "install" this:

I plan to fully integrate a real release, and make this a seamless upgrade. That will happen after I solve an advanced views issue (any views coders out there interested in helping? PM me).

INSTALL:
1) place recipe.views.inc in the same directory as the main recipe files went.
On my system this was in /var/www/html/sites/all/modules/recipe/

2) Edit recipe.module, and paste in the hook _views_api() function from #1

3) Run a Drupal db update (admin/modules, and run update.php)

For now, the suggested way to display this content is using the built-in Recipe content type. Just use CCK to add on interesting fields (images, video etc) and have fun.

You can also make Views of recipe, but the current patch has a couple of SERIOUS limitations:

1) Some Fields should not be used in a View, and will raise a SQL JOIN error if you try.
For example, selecting field "Ingredient ID" does this.
Fortunately the fields you see this on tend to be "ID" fields.. things you would not normally choose to include in most Views.

I can't remove the field as it is needed for the table joins.
This is a learning curve issue on my part - there must be a way to do this without allowing for possible user error.
(suggestions from Views coders welcome).

2) The other issue is non-Ingredients fields get "repeated" - by however many times as the number of ingredients.
So a recipe with 3 ingredients, and using those fields, you will get 3 rows each containing 1 ingredient... and all of the other fields also (recipe name, instructions, etc). It is a well known "one and many" fields issue when doing SQL JOINs. Try it to see (it will not hurt anything).

( The solution is for me to write a Views field handler, and add it as a new Field. A handler could fetch "all ingredients" based on the node ide. This would get one of everything else you asked for, with no repeats anywhere. I am learning views handlers now [help welcome, if anyone knows this aspect of views programming])

Until #2 is solved, the only View I would create and expose to the public is one which is an "index" of your recipes.
Maybe add an image icon or something.. its views, have fun.
Then that views "table of contents" you made could link to the (non-views, module-native content type) recipe the user selects.
When I get #2 resolved, you could link to actual Views of the inside of a recipe..

This looks helpful towards solving the problem... http://capellic.com/blog/cure-duplicate-nodes-in-a-view

Initial Views support checked in (to -dev). It's still considered experimental because it does NOT remove duplicated ingredients (caused by the 1:N join).
You could still use the current Views for a custom Recipe Index or Teasers though.

Hi izmeez,

Yes that is very similar of a problem, thanks. I saw one of the earlier problem/solutions which looked similar to this.

If it was just for my own website (or in your case, it's your decision) go ahead and try it. If you get stuck I'll try to help. I suspect it will work fine and if so you could post it here as a workaround/patch.

From an Drupal coding perspective, this type of solution shouldn't go into contrib modules. The reason is it introduces DB-specific code (which is discouraged in D6 and probably more frowned on in D7).

On that note, another quick hack is you can "clean up" the duplicated fields inside of a views-fields .tpl.php. I see how to do that in theming, but as with subselects, it's not clean enough for CVS.

I'm confident from conversations with Earl that "custom Views Handlers" is the best solution. Basically it involves making a custom field handler, which when selected as a Field it will select the entire "row" of an ingredient group (qty, unit, ingredient-name). Several modules have solved the problem in their own way... I'd look at their code and get the problem partially solved, and run out of time. I have not looked at this since February, so it's possible someone's written up a tutorial or well documented solution.

EDIT: This is a solution to a different-but-very-similar problem: http://himerus.com/blog/himerus/fixing-duplicate-taxonomy-terms-node-vie...
The prerender() stuff I have partially working, but I ran out of time (work crunch, home projects). I'll get back to it but I'll document progress here in case someone else wants to run with it (besides this article, it's not a well documented problem or at least not well indexed in Google).

Views support for displaying a full Recipe (as opposed to just index pages) is not being worked on at all, so this is a looong ways off.

There are a few users using the Views support to draw indexes/listing/sorting tables, and the current code works fine for them (so long as let the actual full--page display of Recipe be taken care of by node display). This hybrid approach works.