with MySQL 5 I get

* user warning: Unknown column 'd.content_id1' in 'where clause' query: SELECT n.title FROM node n
WHERE n.nid=d.content_id1 AND n.uid <> 1 GROUP BY d.content_id1 ORDER BY score DESC in /mounted-storage/home19d/sub001/sc11519-EPMC/www/drupal/includes/database.mysql.inc on line 120.
* user warning: Unknown column 'd.content_id1' in 'where clause' query: SELECT n.title FROM node n WHERE n.nid=d.content_id1 AND n.uid <> 1 AND n.type = 'story' GROUP BY d.content_id1 ORDER BY score DESC in /mounted-storage/home19d/sub001/sc11519-EPMC/www/drupal/includes/database.mysql.inc on line 120.
* user warning: Unknown column 'd.content_id1' in 'where clause' query: SELECT n.title FROM node n WHERE n.nid=d.content_id1 AND n.uid <> 1 GROUP BY d.content_id1 ORDER BY score DESC in /mounted-storage/home19d/sub001/sc11519-EPMC/www/drupal/includes/database.mysql.inc on line 120.

CommentFileSizeAuthor
#11 cre_query_obj.inc3.06 KBScott Reynolds

Comments

Scott Reynolds’s picture

Scott Reynolds commented

Do the tables exist in your database? Seems like the module didn't install.

In your modules directory is there a cre.install file? Did you activate cre module? (node_recommendation requires cre, its included in the download)

Scott Reynolds’s picture

Scott Reynolds commented

did you run the update script? www.mydomain.com/update.php.

Content_id1 was added to the cre_similarity_matrix, so if upgrading from previous version please run update.php

lennart’s picture

lennart commented

Thanks for the quick response :)

I have inspected my database and the columns are present in the cre_similarity_matrix.

If you look at the error it appears that the module is looking in the NODE table for information which it should be looking for in the cre_similarity_matrix table.

Can it have something to du with the MySQL version being 5 ?

Scott Reynolds’s picture

Scott Reynolds commented

Ok ok i think i got it. See your query isnt being fully formed. And the query of course is formed by the cre_query_obj.inc, which is missing from your modules directory. It should be located in the same directory as cre.module. You can of course download it, it comes in the download package.

Please verify.

Query should be this: SELECT d.content_id1 as 'content_id',sum(d.sum+d.count*r.value)/sum(d.count) as 'score',n.title FROM cre_similarity_matrix d,votingapi_vote r,node n WHERE d.content_type1='node' AND r.uid=1 AND d.content_id1<>r.content_id AND d.content_id2=r.content_id AND r.tag = 'vote' AND n.nid=d.content_id1 AND n.uid <> 1 AND n.type = 'story' GROUP BY d.content_id1 ORDER BY score DESC

But only one part is being formed and the part that is not being formed is formed by cre_query_obj.inc

Scott Reynolds’s picture

Scott Reynolds commented

...cvs version now has a check to make sure cre_query_obj.inc is in the same directory

lennart’s picture

lennart commented

cre_query_obj.inc is in modules/cre along with the cre.module and is included just fine so this does not seem to be the problem.

lennart’s picture

lennart commented

I am having this problem with MySQL MySQL 5.0.15
but NOT with 4.0.26

Scott Reynolds’s picture

Scott Reynolds commented

have u redownloaded and tried to start it back up again? I eliminated MySQL as the problem because I too use MySQL 5.

The problem lies in cre_query_obj.inc. either in its .execute() function or its constructor. I sugguest you redownload and copy over at least cre_query_obj.inc into your modules directory.

lennart’s picture

lennart commented
Title: errors when activating blocks » errors when node_recommendation is on

OK. Let us see if we can isolate the problem. First off, the problem only occurs when node_recommendation is activated.

I just downloaded and installed again.

When going to /recommendations I get this error in the log:

Unknown column 'd.content_id1' in 'where clause' query: SELECT n.title FROM node n WHERE n.nid=d.content_id1 AND n.uid <> 1 GROUP BY d.content_id1 ORDER BY score DESC i /mounted-storage/home19d/sub001/sc11519-EPMC/www/drupal/includes/database.mysql.inc på linje 120.

So the query is obviously malformed. Just below the above error message, this following message is also given:

query failed node_recommendation_cre_query

For some reason it seems that the query cannot be constructed properly?

lennart’s picture

lennart commented

I went directly to CVS to get the latest cre.module with the added dependency check for cre_query_obj.inc

the problem remains the same with the same error messages as noted in the post above:

Unknown column 'd.content_id1' in 'where clause' query: SELECT n.title FROM node n WHERE n.nid=d.content_id1 AND n.uid <> 1 GROUP BY d.content_id1 ORDER BY score DESC

can it be that node_recommendation.module does not play well with cre_query_obj.inc ?

Scott Reynolds’s picture

Scott Reynolds commented
StatusFileSize
new cre_query_obj.inc3.06 KB

Ok I will break down on how the modules interact.

node_recommendation calls cre_top function and passes along the name of the query object function (in this case node_recommendation_cre_query). cre_top then creates a query object (which is defined in cre_query_obj.inc) and passes that object along to node_recommendation_cre_query.

node_recommendation_cre_query joins the query object to the node table so that it will recommend only nodes. Then node_recommendation_cre_query, tells cre_top that it is done with the query. cre_top then takes the modified query and calls its execute function. The execute function creates the 'SELECT ... FROM ... WHERE...GROUP BY... ORDER BY' stuff. cre_top takes this string from execute and tells the database to please do this.

So the problem in your setup occurs during the execute. For some reason, when the query object is created it does not contain the default values. And then when it is 'executed' only the modified stuff from node_recommendation_cre_query is part of the query.

SO! I have created a debug enabled query object. Please use only to help us fixed this problem. It is attached to this message

lennart’s picture

lennart commented

I replaced the standard cre_query_obj.inc with the debug one you provided. I still get the exact same error when going to /recommendations

user warning: Unknown column 'd.content_id1' in 'where clause' query: SELECT n.title FROM node n WHERE n.nid=d.content_id1 AND n.uid <> 1 GROUP BY d.content_id1 ORDER BY score DESC in /www/drupal/includes/database.mysql.inc on line 120.

query failed node_recommendation_cre_query

I have tried with both PHP Version 5.1.1 and PHP Version 4.4.2. No difference.

lennart’s picture

lennart commented
Title: errors when node_recommendation is on » errors with node_recommendation Faster query

Aha!

The error only occurs when the cre option is set to

Select a query type:
Accurate query ( )
Faster query (x)

When set to accurate everything works perfectly.

Does this mean that Faster query is not yet meant to work?

lennart’s picture

lennart commented

In other words: the query builds just fine when Accurate query is choosen. This I can see from the debug. But the debug info is *not even shown* when Faster query is choosen.

So there seems to be something wrong with Faster query.

Scott Reynolds’s picture

Scott Reynolds commented

AH HA! and there it is. Fix coming tonite. The problem happens when the user has not placed a vote yet. When that happens (with the faster query) the rest of the query isnt built! I have stuff to do for most of the day here, but i will fix it tonite.

Scott Reynolds’s picture

Scott Reynolds commented

Ok,

Give the latest cvs a try. Cre.module, and cre_query_obj.inc were changed.

It shouldnt give you the error. i was able to reproduce it and then fix it

lennart’s picture

lennart commented

Great ! Thanks a lot ! Really good and fast support here :D

I will give it a try as soon as the changes have been tarballed and report back.

lennart’s picture

lennart commented

Ok!! Thanks a lot. Everthing works perfectly now!

Just one tiny problem. The .install file has one too many } at the end which will stop the update process when going to /update.php

Scott Reynolds’s picture

Scott Reynolds commented
Status: Active » Closed (fixed)

Ok thank you.

This is now closed