1

Topic: Whether probably to make sampling for one request?

Prompt please, whether there is a possibility to make sampling for one request or somehow to optimize:
There are two bound tables: relation and field_data_endpoints

--
-- Table structure ` 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. ';
--
--  the table data ` 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);
......
--
-- Table structure ` 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) ';
--
--  the table data ` 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);
....

Communication: relation.rid => field_data_endpoints.entity_id
Tables describe communication the person-organization, the organization-organization, the person-person
For one record in the table relation always 2 positions in the table field_data_endpoints with different not unique endpoints_entity_id.
To select all people (endpoints_entity_id), connected to the organizations (endpoints_entity_id) which are connected in turn to the person interesting us (endpoints_entity_id).
Now I do so:
1) all Is sampled connected through identical relation.rid endpoints_entity_id, entering into pair c $user_contact_id=21.
Request with a subquery:

$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;
}
}

2) On the basis of sampling again similar request with a subquery which yields result:

/*
$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)) ";
*/

Very much confuses still that fact that arrays $rids_relation, $ids_contact in IN (NOT IN) can be very big.

2

Re: Whether probably to make sampling for one request?

bmikl wrote:

to Select all people (endpoints_entity_id), connected to the organizations (endpoints_entity_id) which are connected in turn to the person interesting us (endpoints_entity_id).

Well also what here such difficult?

SELECT t1.*
FROM field_data_endpoints t1, field_data_endpoints t2, field_data_endpoints t3, relation t12, relation t23
WHERE t1.entity_id = t12.rid AND t12.rid = t2.entity_id AND t1.delta <> t2.delta
AND t2.entity_id = t23.rid AND t23.rid = t3.entity_id AND t2.delta <> t3.delta
AND t3.endpoints_entity_id = $user_contact_id

3

Re: Whether probably to make sampling for one request?

Akina, thanks!
Here I blunted.... Why that did not think at all in this side