Connector::getConnectedEntitiesQuery

static function getConnectedEntitiesQuery($locationPrimary, $linkType = 'id', $parameters = array()) // // getConnectedEntitiesSql
{
	if($linkType == 'id')
		$locationPrimary = Assert::expectIntegerPositive($locationPrimary, '$locationPrimary');
	else
		$locationPrimary = Assert::expectStringNotNull($locationPrimary, '$locationPrimary');

	$useGroups = 			GroupTable::checkGroupUsage() && static::getUseGroups(); // check if we have groups in project and entity uses groups
	$useCodes = 			static::getUseCodes(); // this entity uses codes
	$groupUseCodes =		GroupLocationTable::getUseCodes(); // group entity uses codes

	$typeFld = 				static::getTypeField();/*LOCATION_TYPE*/
	$linkFld = 				static::getLinkField();/*DELIVERY_ID*/
	$locationLinkFld = 		static::getLocationLinkField();/*LOCATION_ID*/
	$targetPrimaryFld = 	static::getTargetEntityPrimaryField();/*ID*/
	$groupLocationLinkFld = GroupLocationTable::getLocationLinkField();/*LOCATION_ID*/
	$groupLinkFld = 		GroupLocationTable::getLinkField();/*LOCATION_GROUP_ID*/

	$seachById = $linkType == 'id';

	$dbConnection = MainHttpApplication::getConnection();

	if(!is_array($parameters))
		$parameters = array();

	if(is_array($parameters['runtime']))
		Assert::announceNotImplemented('Sorry, runtime clause is not implemented currently.');

	$order = array();
	if(is_array($parameters['order']))
		Assert::announceNotImplemented('Sorry, order-over-union clause is not implemented currently.');

	$filter = array();
	if(is_array($parameters['filter']) && !empty($parameters['filter']))
		$filter = $parameters['filter'];

	$select = array('*');
	if(is_array($parameters['select']) && !empty($parameters['select']))
		$select = $parameters['select'];

	/*
	query example when working with delivery:

	select distinct D.* from b_sale_delivery D
		inner join b_sale_delivery2location DL on D.ID = DL.DELIVERY_ID and DL.LOCATION_TYPE = 'L'
		inner join b_sale_location L1 on L1.CODE = DL.LOCATION_ID
		inner join b_sale_location L2 on L2.ID(there will be CODE, if we search by code) = 65683 and L2.LEFT_MARGIN >= L1.LEFT_MARGIN and L2.RIGHT_MARGIN <= L1.RIGHT_MARGIN;
	*/

	$query = new EntityQuery(static::getTargetEntityName());

	$DLCondition = array(
		'=this.'.$targetPrimaryFld/*ID*/ => 'ref.'.$linkFld/*DELIVERY_ID*/
	);
	if($useGroups)
		$DLCondition['=ref.'.$typeFld/*LOCATION_TYPE*/] = array('?', static::DB_LOCATION_FLAG);

	$query
		->registerRuntimeField(
			'DL',
			array(
				'data_type' => get_called_class(),
				'reference' => $DLCondition,
				'join_type' => 'inner'
			)
		)
		->registerRuntimeField(
			'L1',
			array(
				'data_type' => 'BitrixSaleLocationLocation',
				'reference' => array(
					'=this.DL.'.$locationLinkFld/*LOCATION_ID*/ => 'ref.'.($useCodes ? 'CODE' : 'ID'),
				),
				'join_type' => 'inner'
			)
		)
		->registerRuntimeField(
			'L2',
			array(
				'data_type' => 'BitrixSaleLocationLocation',
				'reference' => array(
					'=ref.'.($seachById ? 'ID' : 'CODE') =>  array('?', $locationPrimary), // among parents we have element with ID or CODE is equal to $locationPrimary
					'>=ref.LEFT_MARGIN' => 'this.L1.LEFT_MARGIN', // and its left_margin
					'<=ref.RIGHT_MARGIN' => 'this.L1.RIGHT_MARGIN' // and right_margin fit our restrictions
				),
				'join_type' => 'inner'
			)
		)
		->setSelect($select)
		->setFilter($filter)
		->setOrder($order);

	if(!$useGroups)
	{
		// emulate "select distinct"
		$query->setGroup($select);

		return $query->getQuery();
	}
	else
	{
		$sqls = array($query->getQuery());

		$query = new EntityQuery(static::getTargetEntityName());

		/*
		query example when working with delivery:

		select D.* from b_sale_delivery D
			inner join b_sale_delivery2location DL on D.ID = DL.DELIVERY_ID and DL.LOCATION_TYPE = 'G'
			inner join b_sale_location_group G on G.CODE = DL.LOCATION_ID (if this entity uses ID, skip this join)
			inner join b_sale_grouplocation GL on GL.LOCATION_GROUP_ID = G.ID (if this entity uses ID, there will be DL.LOCATION_ID)
			inner join b_sale_location L1 on L1.ID (there will be CODE, if grouplocation entity uses CODE) = GL.LOCATION_ID
			inner join b_sale_location L2 on L2.ID (there will be CODE, if we seach by code) = 65683 and L2.LEFT_MARGIN >= L1.LEFT_MARGIN and L2.RIGHT_MARGIN <= L1.RIGHT_MARGIN;
		*/

		$query
			->registerRuntimeField(
				'DL',
				array(
					'data_type' => get_called_class(),
					'reference' => array(
						'=this.'.$targetPrimaryFld/*ID*/ => 'ref.'.$linkFld/*DELIVERY_ID*/,
						'=ref.'.$typeFld/*LOCATION_TYPE*/ => array('?', static::DB_GROUP_FLAG)
					),
					'join_type' => 'inner'
				)
			);

		if($useCodes)
		{
			$query
				->registerRuntimeField(
					'G',
					array(
						'data_type' => 'BitrixSaleLocationGroup',
						'reference' => array(
							'=this.DL.'.$locationLinkFld/*LOCATION_ID*/ => 'ref.CODE', //$useCodes == true here, so always CODE
						),
						'join_type' => 'inner'
					)
				);
		}

		$query
			->registerRuntimeField(
				'GL',
				array(
					'data_type' => 'BitrixSaleLocationGroupLocation',
					'reference' => array(
						($useCodes ? '=this.G.ID' : '=this.DL.'.$locationLinkFld/*LOCATION_ID*/) => 'ref.'.$groupLinkFld/*LOCATION_GROUP_ID*/
					),
					'join_type' => 'inner'
				)
			)
			->registerRuntimeField(
				'L1',
				array(
					'data_type' => 'BitrixSaleLocationLocation',
					'reference' => array(
						'=this.GL.'.$groupLocationLinkFld/*LOCATION_ID*/ => 'ref.'.($groupUseCodes ? 'CODE' : 'ID'),
					),
					'join_type' => 'inner'
				)
			)
			->registerRuntimeField(
				'L2',
				array(
					'data_type' => 'BitrixSaleLocationLocation',
					'reference' => array(
						'=ref.'.($seachById ? 'ID' : 'CODE') =>  array('?', $locationPrimary),
						'>=ref.LEFT_MARGIN' => 'this.L1.LEFT_MARGIN',
						'<=ref.RIGHT_MARGIN' => 'this.L1.RIGHT_MARGIN'
					),
					'join_type' => 'inner'
				)
			)
			->setSelect($select)
			->setFilter($filter)
			->setOrder($order);

		$sqls[] = $query->getQuery();

		return static::unionize($sqls);
	}
}