MySQL statements eZ Publish - howto?

MySQL statements eZ Publish - howto?

Thursday 11 December 2008 2:54:17 am - 5 replies

Modified on Monday 15 December 2008 4:01:26 pm by Clemens T

Author Message

André R.

Thursday 11 December 2008 4:04:55 am

custom sql..

On the "$node_id = $relation["node_id"];" line, get the path_string for the node so you can use it in where statment of your custom sql to get the email attributes, also use class id and class attribute id instead of identifiers in the sql where / join statments.
You can get this with eZContentObjectTreeNode::classAttributeIDByIdentifier and ::classIDByIdentifier before the loops.

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

Clemens T

Thursday 11 December 2008 5:42:29 am

Thanks Andre, I've considered that option... but I believe it could make my code fail in the future, so that's why I've done the following changes (change foreach for a array key lookup). It's much faster now!

		//Reject if this object does not have a valid main node
		if(is_null($mainNodeID))
			return EZ_WORKFLOW_TYPE_STATUS_REJECTED;			
		
		$dataMap = $object->dataMap(); 

		if(array_key_exists( 'groups', $dataMap ) )
		{		
			$content = $dataMap['groups']->content();
			foreach( $content['relation_list'] as $relation)
			{
				//eZDebug::writeNotice($relation["contentclass_identifier"], "internal");		
				//filter the pr_groups from the related list
				if($relation["contentclass_identifier"] == "pr_group")
				{
					$contentobject_id = $relation["contentobject_id"];
					$node_id = $relation["node_id"];
					/*
					 * Fetch all the PR_MAIL class ID=29 objects from the related node
					 */
					$subtreeFetchParams=array(
						'ClassFilterType' => 'include',
						'ClassFilterArray' => array(29),
						'status' => EZ_CONTENT_OBJECT_STATUS_PUBLISHED);
					
					$childNodes =& eZContentObjectTreeNode::subTree($subtreeFetchParams,$node_id);				
					foreach( $childNodes as $child )
					{								
						//Child is of type eZContentObjectTreeNode			
						if($child->hasContentObject())
						{
							//Fetch the eZContentObject of this eZContentObjectTreeNode
							$childContentObject =& $child->object();
							$childDataMap = $childContentObject->dataMap(); 

							//eZDebug::writeNotice("childDataMap :".print_r($childDataMap, true), "internal");	
							if(array_key_exists( 'email', $childDataMap ) )
							{
								eZDebug::writeNotice("Un-uniqueified emailOfReceiver: ".$childDataMap['email']->content(),"Workflow smInternalPressReleaseType");									
								$emailsOfReceivers[] = $childDataMap['email']->content();
							}//array_key_exists (email)
						} //child->hasContentObject		
					}//foreach childnodes
				}//if relation_list == pr_group				
			}//foreach relation_list
		}//if array key exists (groups)

Clemens T

Monday 15 December 2008 4:02:41 pm

The peformance increase I got from editing the code didn't contribute enough to make the system stable enough. Could anyone help me build the SQL that this code produces, since it is the first time I'm attempting that. Or just some pointers on how to work with the MySQL database layer eZ Publish offers.

Thanks!

André R.

Wednesday 17 December 2008 9:10:03 am

This might help:

$node_id = $relation["node_id"];
$subtreeFetchParams = array(
        'ClassFilterType' => 'include',
        'ClassFilterArray' => array(29)
        );

$childNodes =& eZContentObjectTreeNode::subTree($subtreeFetchParams, $node_id);                              
foreach( $childNodes as $child )
{                                                               
    $emailAttribute =& $child->object()->fetchAttributesByIdentifier( array('email') );
    $emailsOfReceivers[] = $emailAttribute->content();
      
}//foreach childnodes

Remove the & after = if your on php5.
Note: If you don't need to check permission, then you can also set 'Limitation' => array() on subtreeFetchParams.

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

Clemens T

Thursday 18 December 2008 3:54:59 am

Andre,

Thanks again for putting effort in helping me find a solution.

It most certainly increase the speed of the application, the final code:


					$node_id = $relation["node_id"];
					$subtreeFetchParams = array(
						'ClassFilterType' => 'include',
						'ClassFilterArray' => array(29),
						'Limitation' => array(),
					);
					$childNodes =& eZContentObjectTreeNode::subTree($subtreeFetchParams, $node_id);                              					
					foreach( $childNodes as $child )
					{                                                               
					   $chContentObject =& $child->object();
					   $datamap = $chContentObject->dataMap();
					   $emailsOfReceivers[] = $datamap['email']->content();
					}//foreach childnodes

Unfortunately, the $chContentObject had to be stored in between, before I could get the datamap, but this is already a great improvement. Thanks also for introducing the 'Limitation' = array() to me, because I was already logging a dedicated user in via:

		/*
		 * Log the current logged in user, in order to make sure that we are able to log
		 * him/her in after this event is done. Needed because we change users in the middle.
		 */
		$eZUser = eZUser::currentUser();
		$userID = eZUser::currentUserID();
		
		/*
		 * Login the user that is used for sending the press releases 
		 * (and will be able to read the newsitems / images)
		 *
		 * This is needed because this is being executed by a workflow.
		 */
		$loginResult = eZUser::loginUser("x_read","x");
		if(!$loginResult)
		{
			return $module->handleError( EZ_ERROR_KERNEL_ACCESS_DENIED, 'kernel' );
		}

and then logging out and re-setting the currently loggged in user, so this is a great contribution!

Thanks again :),
Clemens

You must be logged in to post messages in this topic!

Powered by eZ Publish™ CMS Open Source Web Content Management. Copyright © 1999-2014 eZ Systems AS (except where otherwise noted). All rights reserved.