Forums / Developer / How-to display "Most related contents" ?

How-to display "Most related contents" ?

« 
Previous topic
|
Developer
|
Next topic
 »
Author Message

Mickael Robin

Monday 23 March 2009 2:58:00 am

Hi all,

I can't find a way to fetch the "most related objects" of a given content_class.

Basically, my users are invited to create "selections" ("selection" content_class is based on a object_relations attribute) to select the articles they like.

To display the 5 most selected articles within a certain period, I found 2 ways which are much beyond my developing skills :

1st way :
- fetch all articles (tree fetch)
- foreach article : fetch with "reverse_related_objects_count"
=> how can I store the count for each article, and then sort articles by count?
=> how can I restrict to a certain timeframe?

2nd way :
- fetch all selections (tree fetch)
- foreach selection
- check if selection was created within the timeframe
- get related article node_id and append it to an array
=> how can I sort the node_ids by number of repetitions in this array?

Any help would be highly appreciated :-)

Thanks in advance
Mikrob

Jan Komárek

Tuesday 24 March 2009 9:19:17 am

You can create your own extended attribute filter and fetch waht you need. But its demand some study of database structure.

This is join which enables sorting by comments count. Where ezcontentobject.contentclass_id = '13' is comments classID.

NATURAL LEFT JOIN (SELECT DISTINCT
	parent.contentobject_id AS contentobject_id,
	Count(ezcontentobject.id) AS comments
	FROM
	ezcontentobject_tree AS childTree
	Inner Join ezcontentobject_tree AS parentTree ON rodicTree.node_id = childTree.parent_node_id
	Inner Join ezcontentobject_name AS parent ON rodic.contentobject_id = parentTree.contentobject_id AND parent.content_version = parentTree.contentobject_version
	Inner Join ezcontentobject ON ezcontentobject.id = childTree.contentobject_id AND childTree.contentobject_version = ezcontentobject.current_version
	WHERE
	ezcontentobject.contentclass_id =  '13'
	GROUP BY
	rodic.name,
	rodic.contentobject_id) as comments

If you add ,comments.comments as comm_count to the columns you can sort by count of comments. This is the way to fetch most commented object...

André R.

Wednesday 25 March 2009 2:41:10 am

The sql above will most likely be very slow, I think it would make more sense to do a completely separate sql call when doing advance stuff like this, and not hook into tree / list fetch witch can already be a bit heavy on it's own.

2 examples ( fetchNodeByRating() and getRatingWhere() ):
http://svn.projects.ez.no/ezcore/trunk/ezcore/autoloads/ezrating.php

eZ Online Editor 5: http://projects.ez.no/ezoe || eZJSCore (Ajax): http://projects.ez.no/ezjscore || eZ Publish EE http://ez.no/eZPublish/eZ-Publish-Enterprise-Subscription
@: http://twitter.com/andrerom

Piotrek Karaś

Thursday 26 March 2009 9:36:28 pm

Object-relations is a very complex and multi-level datatype, and so are queries to the eZ content model. I think you should aim at providing a dedicated datatype with its own one database table as a storage. You would simply keep references from an object to an object, with creation date, user ID and other useful information. Even if you have a million article selections, this should still be possible to maintain, query, etc. You can also provide template operator or a fetch for querying it from within templates.

--
Company: mediaSELF Sp. z o.o., http://www.mediaself.pl
eZ references: http://ez.no/partners/worldwide_partners/mediaself
eZ certified developer: http://ez.no/certification/verify/272585
eZ blog: http://ez.ryba.eu

Mickael Robin

Monday 30 March 2009 3:02:36 am

Thanks to the three of you for your constructive answers.

I will definitely try to adapt ezrating to my needs, and come back here to share my code...or difficulties :-)

Besides, I need to implement "if node A and node B are related" :
=> do I have to proceed the same way as above to create a "are related" operator?