Ok,
First off, sorry if this is the wrong place to post this question. But this is the one time so far that i have really struggled with Drupal and Im seeking help.
I am struggling with how to develop a views intergration with my Content Recommendation Engine [http://groups.drupal.org]. And the simple reason is that my table holds really no new information. It holds content_id's of two different content type and the sum and count of all votes made on this. (ACK! i know makes little sense....)
My scoring is done by taking this data and derving the score. Here is the db query for top function
$db_result = db_query("SELECT d.content_id1 as 'content_id', sum(d.sum+d.count*r.value)/sum(d.count) as 'score'
FROM {votingapi_vote} r, {cre_similarity_matrix} d
WHERE r.uid=%d AND d.content_id1<>r.content_id AND d.content_id2=r.content_id AND d.content_type1='%s' AND
d.content_type2=d.content_type1 AND r.tag='%s'
GROUP BY d.content_id1 ORDER BY score DESC",$uid,$target_type,$tag);I would like to filter on score...but as you can see it is a calculated score. Any ideas...
Every score for each node object is dependent on the current user so storing the score would take up considerablly more space for no performance benefit or ease of coding....
I would also like to be able to filter on the 'average' difference between nodes. As you can see in the table structure below, im storing the sum and count. So the average is sum/count...
As with any post, I try to include the table structure for reference. It holds the voting similarity average between two pieces of content (easier to think of two nodes, but it is ANY piece of content).
db_query("CREATE TABLE {cre_similarity_matrix}(
content_id1 int(10) NOT NULL DEFAULT 0,
content_id2 int(10) NOT NULL DEFAULT 0,
content_type1 varchar(20) default NULL,
content_type2 varchar(20) default NULL,
count int(10) NOT NULL DEFAULT 0,
sum int(10) NOT NULL DEFAULT 0,
PRIMARY KEY (content_id1, content_id2)
) /*!40100 DEFAULT CHARACTER SET utf8 */;");
Comments
anyone
any progress here scott? anyone done this before?
No progress at all. My plan
No progress at all. My plan is to push something through here in August when I get a nice break between job and school. there is some interest in this. its been the gorilla in the room for awhile now
solved - complex views fields
this works for me. might not be desireable in all situations ...
you add an element to your field defintion called 'query handler'. this element is described at . then you create the actual handler function. here is mine. the gist is that you alter the $query object as needed. you add a field, and optionally add WHERE and GROUP BY clauses. in the example below, I add a field that does an AVG().
<?php
function clan_buddy_avg_score_query_handler($fielddata, $fieldinfo, &$query) {
// add field that gets aliased back to its field definition key using $fielddata['queryname']
$table = $fielddata['tablename'];
$query->ensure_table($table);
$query->add_field("AVG($table.value)", NULL, $fielddata['queryname']);
// add any where clause
$str_buddies = implode(',', array_keys(buddylist_get_buddies()));
$clause = $table. ".uid IN ($str_buddies)";
$query->add_where($clause);
// add any group by clause
$query->add_groupby("$table.content_id");
}
?>
oh, simper
i reread yor post and you are doing something much simpler than what i posted. you just want to declare a new field that had a 'handler' which performs the calculation. search for 'notafield' in views package for examples.