Just installed this module today on new 5.1 install. I've used the module on other 4.7 sites and love it. Seems as though something fishy is going on with v5 compatability. I read that some other user was having a similar problem, so I decieded to make it a bug report. Here's the error when trying to go to the main page at drupal/recipe:

user warning: Unknown table 'n' in field list query: SELECT DISTINCT(n.nid), title, sticky, created FROM node WHERE status = 1 AND type = 'recipe' ORDER BY sticky DESC, created DESC LIMIT 0, 10 in /home/hun76org/public_html/camping/drupal/includes/database.mysql.inc on line 172.

Any ideas? Thanks!

Comments

marble’s picture

marble commented

Hmm, I still can't find this query in the recipe module. Could you post the output of:
grep SELECT recipe.module
from the modules/recipe/ directory, so I can see if I can spot where it's coming from on yours please?

hunnikick’s picture

hunnikick commented

root@srv01 [/home/hun76org/www/camping/drupal/modules/recipe]# grep SELECT recipe.module

$recipe = db_fetch_object(db_query('SELECT * FROM {recipe} WHERE nid = %d', $node->nid));

$result = db_query_range(db_rewrite_sql("SELECT n.nid, n.title, n.uid, u.name FROM {node} n INNER JOIN {node_revisions} r ON n.nid = r.nid INNER JOIN {users} u ON n.uid = u.uid WHERE n.type='recipe' AND n.status =1 ORDER BY n.created DESC"), 0, 5);

$unit_rs = db_query('SELECT id,type,name,abbreviation FROM {recipe_unit} ORDER BY type ASC, metric');

$ingredient_id = db_result(db_query("SELECT id FROM {recipe_ingredient} WHERE LOWER(name)='%s'", trim(strtolower($name))));

$ingredient_id = db_result(db_query("SELECT id FROM {recipe_ingredient} WHERE LOWER(name)='%s'", trim(strtolower($name))));

SELECT

$rs = db_query('SELECT id,abbreviation FROM {recipe_unit}');

$rs = db_query('SELECT id,name FROM {recipe_unit}');

$rs = db_query("SELECT name FROM {recipe_ingredient} WHERE LOWER(name) LIKE '%s%%' ORDER BY name LIMIT %d", strtolower($string), $limit);

$rs = db_query('SELECT id,name,abbreviation FROM {recipe_unit}');

$sql = 'SELECT DISTINCT n.nid, n.title, n.sticky, n.created FROM {node} n INNER JOIN {term_node} tn ON n.nid = tn.nid WHERE tn.tid IN ('. $str_tids .') AND n.status = 1 ORDER BY n.sticky DESC, n.created DESC';

$sql_count = 'SELECT COUNT(n.nid) FROM {node} n INNER JOIN {term_node} tn ON n.nid = tn.nid WHERE tn.tid IN ('. $str_tids .') AND n.status = 1';

$sql_count = 'SELECT COUNT(n.nid) FROM {node} n '. $joins .' WHERE n.status = 1 AND '. $wheres;

$sql = "SELECT nid, title, sticky, created FROM {node} WHERE status = 1 AND type = 'recipe' ORDER BY sticky DESC, created DESC";

$sql_count = "SELECT COUNT(nid) FROM {node} WHERE status = 1 AND type = 'recipe'";

$result = db_query("SELECT tid FROM {term_data} WHERE vid = %d", $vid);

$old_ingredients = db_query('SELECT * FROM {recipe_ingredients}');

$old = db_query("SELECT * FROM {recipe_old}");

$ings = db_query("SELECT * FROM {recipe_ingredients}");

$unit_rs = db_query("SELECT id FROM {recipe_unit} WHERE '%s' LIKE CONCAT(LOWER(name),'%%') OR abbreviation='%s'", strtolower($unit), $unit); // allow pints to match pint etc

root@srv01 [/home/hun76org/www/camping/drupal/modules/recipe]#

brasto’s picture

brasto commented

I am experiencing this same issue. Might this be resolved soon?

sdsheridan’s picture

sdsheridan
he/him/his
commented

I've had the same problem. It in fact looks like a problem with the way the valid SQL in the recipe module is being mapped to SQL for MySQL. While that's not in the recipe module itself (but rather in the includes/database.mysql.inc file), being a newbie to Drupal, i'm not quite sure how to fix it. The issue appears to me to be around the use of the "DISTINCT" key-word in the recipe module, and how it's being treated by the database.mysql.inc translation layer. Is this key-word needed? Or is there another way to construct the query so that is is not needed (which would probably be more efficient anyway, since the RDBMS wouldn't have to sort-and-eliminate to make sure there are only distinct items)? Just a thought...

marble’s picture

marble commented

I think the SQL in question is coming from the taxonomy module. Why this happens for recipe nodes is a different question...

babaker’s picture

babaker commented

The query itself is incorrect. in the FROM clause it only says "FROM node". In order for the "SELECT DISTINCT(n.nid)" to work properly the FROM clause would need to be "FROM node n", although most of the drupal queries would look like "FROM {node} n"

it appears like marble said, that the query is coming from the Taxonomy module, possibly around line 1240. Although in the version of the file that I have (1.330.2.2) the query doesn't have the problem you described. what version of the Taxonomy module do you have?

PCanzona’s picture

PCanzona commented

I had the same SQL error, above, when trying to display a sample recipe I posted. But after reading the last reply, I then created a vocabulary for the Recipe module, added one term, edited the sample recipe to include that term and the sample recipe displayed just fine without any database errors. It appears you must create and require the use of vocabulary before the module will work properly on Drupal 5.x.

Pete

brasto’s picture

brasto commented
Title: user warning: Unknown table 'n' in field list query: SELECT DISTINCT(n.nid), title, sticky, created FROM node WHERE status = 1 A » Pete's resolution is likely what solved the issue for me as well

I had thought the issue went away when I created my first recipe. However, I also created the category for that recipe when I first added it. If Pete had a recipe and still had the error, but it went away after creating the category, I believe that must have solved it for me since I did both at once when the issue went away for me. Just FYI.

Perhaps the Recipe install can automatically create a category called Recipes and a couple basic Terms?

marble’s picture

marble commented
Title: Pete's resolution is likely what solved the issue for me as well » SQL Error when no categories defined
Josiah@missions.ritchietribe.net’s picture

Josiah@missions.ritchietribe.net commented

I'm confused. Is this considered a bug or is it something that simply needs to be documented as part of the installation procedure? I'm not expecting lots of recipes so I'd rather not have taxonomy in use. It will only serve to confuse my users. Can I resolve it without taxonomy?

Josiah@missions.ritchietribe.net’s picture

Josiah@missions.ritchietribe.net commented

I'm confused. Is this considered a bug or is it something that simply needs to be documented as part of the installation procedure? I'm not expecting lots of recipes so I'd rather not have taxonomy in use. It will only serve to confuse my users. Can I resolve it without taxonomy?

marble’s picture

marble commented

I think it's a bug, but it seems to be a bug in taxonomy (that's where the error's coming from at least). I think if you don't have the taxonomy stuff enabled you don't see the problem anyway.

Josiah@missions.ritchietribe.net’s picture

Josiah@missions.ritchietribe.net commented

Sorry about the duplicate posts before.

I guess I don't have an option but to setup taxonomy for this because I need to have the Taxonomy module for other modules I use.

scottprive’s picture

scottprive commented
Status: Active » Closed (fixed)

Closing. This error does not originate in Recipe, and there has not been a report in a long time.

If this still happens for anyone, please file against whatever Project seems most likely (which according to several posts, would seem to be somewhere in Taxonomy)