How-to display "Most related contents" ?

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?

eZ debug

Timing:	Jan 18 2025 05:15:27
Script start
Timing:	Jan 18 2025 05:15:27
Module start 'layout'
Timing:	Jan 18 2025 05:15:27
Module start 'content'
Timing:	Jan 18 2025 05:15:27
Module end 'content'
Timing:	Jan 18 2025 05:15:27
Script end

Main resources:

Total runtime	0.6776 sec
Peak memory usage	4,096.0000 KB
Database Queries	67

Timing points:

Checkpoint	Start (sec)	Duration (sec)	Memory at start (KB)	Memory used (KB)
Script start	0.0000	0.0065	587.9375	152.6250
Module start 'layout'	0.0065	0.0024	740.5625	39.4453
Module start 'content'	0.0090	0.6673	780.0078	654.3438
Module end 'content'	0.6762	0.0014	1,434.3516	16.1406
Script end	0.6776		1,450.4922

Time accumulators:

Accumulator	Duration (sec)	Duration (%)	Count	Average (sec)
Ini load
Load cache	0.0035	0.5191	16	0.0002
Check MTime	0.0014	0.2021	16	0.0001
Mysql Total
Database connection	0.0011	0.1609	1	0.0011
Mysqli_queries	0.6185	91.2805	67	0.0092
Looping result	0.0007	0.1068	65	0.0000
Template Total	0.6472	95.5	2	0.3236
Template load	0.0021	0.3055	2	0.0010
Template processing	0.6451	95.2081	2	0.3226
Template load and register function	0.0002	0.0263	1	0.0002
states
state_id_array	0.0014	0.2019	1	0.0014
state_identifier_array	0.0008	0.1120	2	0.0004
Override
Cache load	0.0018	0.2614	40	0.0000
Sytem overhead
Fetch class attribute can translate value	0.0009	0.1349	4	0.0002
Fetch class attribute name	0.0010	0.1430	8	0.0001
XML
Image XML parsing	0.0017	0.2476	4	0.0004
class_abstraction
Instantiating content class attribute	0.0000	0.0021	8	0.0000
General
dbfile	0.0011	0.1671	29	0.0000
String conversion	0.0000	0.0012	4	0.0000
Note: percentages do not add up to 100% because some accumulators overlap

Templates used to render the page:

Usage	Requested template	Template	Template loaded
1	node/view/full.tpl	full/forum_topic.tpl	extension/sevenx/design/simple/override/templates/full/forum_topic.tpl
5	content/datatype/view/ezxmltext.tpl	<No override>	extension/community_design/design/suncana/templates/content/datatype/view/ezxmltext.tpl
11	content/datatype/view/ezxmltags/paragraph.tpl	<No override>	extension/ezwebin/design/ezwebin/templates/content/datatype/view/ezxmltags/paragraph.tpl
5	content/datatype/view/ezxmltags/line.tpl	<No override>	design/standard/templates/content/datatype/view/ezxmltags/line.tpl
3	content/datatype/view/ezimage.tpl	<No override>	extension/sevenx/design/simple/templates/content/datatype/view/ezimage.tpl
1	content/datatype/view/ezxmltags/literal.tpl	<No override>	extension/community/design/standard/templates/content/datatype/view/ezxmltags/literal.tpl
1	print_pagelayout.tpl	<No override>	extension/community/design/community/templates/print_pagelayout.tpl
Number of times templates used: 27 Number of unique templates used: 7

Time used to render debug report: 0.0001 secs