--
-- Структура таблицы `relation`
--

CREATE TABLE `relation` (
  `rid` int(10) UNSIGNED NOT NULL COMMENT 'Unique relation id (entity id).',
  `relation_type` varchar(255) NOT NULL DEFAULT '' COMMENT 'Relation type (see relation_type table).',
  `vid` int(10) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'The current relation_revision.vid version identifier.',
  `uid` int(11) NOT NULL DEFAULT '0' COMMENT 'The _lk_users.uid that owns this relation; initially, this is the user that created it.',
  `created` int(11) NOT NULL DEFAULT '0' COMMENT 'The Unix timestamp when the relation was created.',
  `changed` int(11) NOT NULL DEFAULT '0' COMMENT 'The Unix timestamp when the relation was most recently saved.',
  `arity` int(10) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'The number rows in this relation. Cannot exceed max_arity, or be less than min_arity in relation_type table.'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Keeps track of relation entities.';

--
-- Дамп данных таблицы `relation`
--

INSERT INTO `relation` (`rid`, `relation_type`, `vid`, `uid`, `created`, `changed`, `arity`) VALUES
(5, 'employee', 5, 1, 1504626335, 1504626335, 2),
(7, 'employee', 7, 1, 1504629428, 1504629428, 2),
......
--
-- Структура таблицы `field_data_endpoints`
--

CREATE TABLE `field_data_endpoints` (
  `entity_type` varchar(128) NOT NULL DEFAULT '' COMMENT 'The entity type this data is attached to',
  `bundle` varchar(128) NOT NULL DEFAULT '' COMMENT 'The field instance bundle to which this row belongs, used when deleting a field instance',
  `deleted` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'A boolean indicating whether this data item has been deleted',
  `entity_id` int(10) UNSIGNED NOT NULL COMMENT 'The entity id this data is attached to',
  `revision_id` int(10) UNSIGNED DEFAULT NULL COMMENT 'The entity revision id this data is attached to, or NULL if the entity type is not versioned',
  `language` varchar(32) NOT NULL DEFAULT '' COMMENT 'The language for this data item.',
  `delta` int(10) UNSIGNED NOT NULL COMMENT 'The sequence number for this data item, used for multi-value fields',
  `endpoints_entity_type` varchar(255) NOT NULL DEFAULT '' COMMENT 'Entity_type of this relation end-point.',
  `endpoints_entity_id` int(10) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'Entity_id of this relation end-point.',
  `endpoints_r_index` int(10) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'The index of this row in this relation. The highest index in the relation is stored as "arity" in the relation table.'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Data storage for field 6 (endpoints)';

--
-- Дамп данных таблицы `field_data_endpoints`
--

INSERT INTO `field_data_endpoints` (`entity_type`, `bundle`, `deleted`, `entity_id`, `revision_id`, `language`, `delta`, `endpoints_entity_type`, `endpoints_entity_id`, `endpoints_r_index`) VALUES
('relation', 'employee', 0, 5, 5, 'und', 0, 'contact', 1, 0),
('relation', 'employee', 0, 5, 5, 'und', 1, 'contact', 6, 1),
('relation', 'employee', 0, 7, 7, 'und', 0, 'contact', 7, 0),
('relation', 'employee', 0, 7, 7, 'und', 1, 'contact', 3, 1),
('relation', 'employee', 0, 11, 11, 'und', 0, 'contact', 6, 0),
('relation', 'employee', 0, 12, 12, 'und', 1, 'contact', 3, 1),
....

$query = "SELECT endp.entity_id AS entity_id, endp.endpoints_entity_id AS endpoints_entity_id, c.contact_id AS contact_id
FROM 
{field_data_endpoints} endp
LEFT OUTER JOIN {contact} c ON endp.endpoints_entity_id = c.contact_id AND (c.type = 'organization')
WHERE  (endp.entity_id IN  (SELECT relation.rid AS rid
FROM 
{relation} relation
INNER JOIN {field_data_relationship_status} b ON relation.rid = b.entity_id AND
      (b.entity_type = 'relation' AND b.deleted = '0')
LEFT OUTER JOIN {field_data_endpoints} n ON relation.rid = n.entity_id AND (n.entity_type = 'relation' AND n.deleted = '0')
WHERE  (n.endpoints_entity_id = '21') AND (relation.relation_type = 'employee') AND (b.relationship_status_value = '1') )) AND (endp.endpoints_entity_id != '21')"



    $result = $query->execute()->fetchAllAssoc('endpoints_entity_id');
    $ids_contact = array_keys($result);
    $rids_relation = array();
    foreach ($result as $key => $rel) {
      if(!in_array($rel->entity_id,  $rids_relation)){
         $rids_relation[] = $rel->entity_id;
      }
    }

/*
$query = " SELECT endp.entity_id AS entity_id, endp.endpoints_entity_id AS endpoints_entity_id, c.contact_id AS contact_id
FROM 
{field_data_endpoints} endp
LEFT OUTER JOIN {contact} c ON endp.endpoints_entity_id = c.contact_id AND (c.type = 'individual')
WHERE  (endp.entity_id IN  (SELECT relation.rid AS rid
FROM 
{relation} relation
INNER JOIN {field_data_relationship_status} b ON relation.rid = b.entity_id AND
        (b.entity_type = 'relation' AND b.deleted = '0')
LEFT OUTER JOIN {field_data_endpoints} n ON relation.rid = n.entity_id AND (n.entity_type = 'relation' AND n.deleted = '0')
WHERE  (n.endpoints_entity_id IN  ($ids_contact)) AND (relation.relation_type = 'employee') AND (relation.rid NOT IN  ( $rids_relation )) AND (b.relationship_status_value = '1') )) AND (endp.endpoints_entity_id NOT IN  ($rids_relation))";
*/