Finding Drupal nodes by combinations of field values

Steve Bink
Steve Bink used Ask the Experts™
on
In a Drupal 7 site, we have a node entity type for stored documents.  This type has two custom fields - year and reference - which uniquely identify the node.  These values look something like shown below, with the format being "<reference><hyphen><year>".
S123-2015
S456-2013
S1145-2015

Open in new window

I'm receiving a list of these document references (perhaps hundreds at a time) and need an efficient way to find the corresponding node IDs.  What's the best way to do this?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
can you pls provide few input rows and expected output.
Top Expert 2004

Author

Commented:
The input "rows" are exactly what is displayed in the original question.  I receive an array of string values in that format.  My expected output is the node IDs corresponding to those unique combinations of reference number and year.
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
I do not have your DB. Can you give me rows from the tables associated like node table I can help you with the query you need
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Top Expert 2004

Author

Commented:
It is a standard Drupal 7 database.

The node table:
CREATE TABLE `node` (
	`nid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'The primary identifier for a node.',
	`vid` INT(10) UNSIGNED NULL DEFAULT NULL COMMENT 'The current node_revision.vid version identifier.',
	`type` VARCHAR(32) NOT NULL DEFAULT '' COMMENT 'The node_type.type of this node.',
	`language` VARCHAR(12) NOT NULL DEFAULT '' COMMENT 'The languages.language of this node.',
	`title` VARCHAR(255) NOT NULL DEFAULT '' COMMENT 'The title of this node, always treated as non-markup plain text.',
	`uid` INT(11) NOT NULL DEFAULT '0' COMMENT 'The users.uid that owns this node; initially, this is the user that created it.',
	`status` INT(11) NOT NULL DEFAULT '1' COMMENT 'Boolean indicating whether the node is published (visible to non-administrators).',
	`created` INT(11) NOT NULL DEFAULT '0' COMMENT 'The Unix timestamp when the node was created.',
	`changed` INT(11) NOT NULL DEFAULT '0' COMMENT 'The Unix timestamp when the node was most recently saved.',
	`comment` INT(11) NOT NULL DEFAULT '0' COMMENT 'Whether comments are allowed on this node: 0 = no, 1 = closed (read only), 2 = open (read/write).',
	`promote` INT(11) NOT NULL DEFAULT '0' COMMENT 'Boolean indicating whether the node should be displayed on the front page.',
	`sticky` INT(11) NOT NULL DEFAULT '0' COMMENT 'Boolean indicating whether the node should be displayed at the top of lists in which it appears.',
	`tnid` INT(10) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'The translation set id for this node, which equals the node id of the source post in each set.',
	`translate` INT(11) NOT NULL DEFAULT '0' COMMENT 'A boolean indicating whether this translation page needs to be updated.',
	`uuid` CHAR(36) NOT NULL DEFAULT '' COMMENT 'The Universally Unique Identifier.',
	PRIMARY KEY (`nid`),
	UNIQUE INDEX `vid` (`vid`),
	INDEX `node_changed` (`changed`),
	INDEX `node_created` (`created`),
	INDEX `node_frontpage` (`promote`, `status`, `sticky`, `created`),
	INDEX `node_status_type` (`status`, `type`, `nid`),
	INDEX `node_title_type` (`title`, `type`(4)),
	INDEX `node_type` (`type`(4)),
	INDEX `uid` (`uid`),
	INDEX `tnid` (`tnid`),
	INDEX `translate` (`translate`),
	INDEX `language` (`language`),
	INDEX `uuid` (`uuid`)
)
COMMENT='The base table for nodes.'
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

Open in new window



The session (year) table:
CREATE TABLE `field_data_field_ol_session` (
	`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 NULL 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',
	`field_ol_session_value` INT(11) NULL DEFAULT NULL,
	PRIMARY KEY (`entity_type`, `entity_id`, `deleted`, `delta`, `language`),
	INDEX `entity_type` (`entity_type`),
	INDEX `bundle` (`bundle`),
	INDEX `deleted` (`deleted`),
	INDEX `entity_id` (`entity_id`),
	INDEX `revision_id` (`revision_id`),
	INDEX `language` (`language`),
	INDEX `field_ol_session_value` (`field_ol_session_value`)
)
COMMENT='Data storage for field 474 (field_ol_session)'
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

Open in new window


The table for the document reference number (print number):
CREATE TABLE `field_data_field_ol_base_print_no` (
	`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 NULL 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',
	`field_ol_base_print_no_value` VARCHAR(255) NULL DEFAULT NULL,
	`field_ol_base_print_no_format` VARCHAR(255) NULL DEFAULT NULL,
	PRIMARY KEY (`entity_type`, `entity_id`, `deleted`, `delta`, `language`),
	INDEX `entity_type` (`entity_type`),
	INDEX `bundle` (`bundle`),
	INDEX `deleted` (`deleted`),
	INDEX `entity_id` (`entity_id`),
	INDEX `revision_id` (`revision_id`),
	INDEX `language` (`language`),
	INDEX `field_ol_base_print_no_format` (`field_ol_base_print_no_format`),
	INDEX `field_ol_base_print_no_value` (`field_ol_base_print_no_value`)
)
COMMENT='Data storage for field 444 (field_ol_base_print_no)'
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

Open in new window

Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
What is the input. List of documents- meaning comma separated entity_types ??
Top Expert 2004

Author

Commented:
As I said, I receive an array of references.  Given the example input of "S1145-2015", then
`field_data_field_ol_base_print_no`.`field_ol_base_print_no_value`='S1145'
`field_data_field_ol_session`.`field_ol_session_value`='2015'
`node`.`nid` is the target I need

Open in new window

I'm asking for a query to resolve hundreds of these inputs at a time...  I already have a method to do them one by one.
Top Expert 2004
Commented:
Ended up with this:
function nys_bill_notifications_find_taxonomy_roots() {
  // Get the compiled changes.
  $changes = &_get_openleg_session_changes();

  if (count($changes)) {
    // Limit the search by session year and print number generally, with a hard
    // compare on concatenation of the two fields.
    // Compile the arrays for the query.
    $print_nums = $session_years = [];
    foreach (array_keys($changes) as $val) {
      list($t_print, $t_year) = explode('-', $val);
      $print_nums[$t_print] = 1;
      $session_years[$t_year] = 1;
    }

    // Generate the query used to look up the taxonomy ID.
    $query = "SELECT " .
      "CONCAT(bpn.field_ol_base_print_no_value, '-', sess.field_ol_session_value) AS `key`, " .
      "root.field_bill_multi_session_root_tid AS `tid` " .
      "FROM {field_data_field_ol_session} sess " .
      "INNER JOIN {field_data_field_ol_base_print_no} bpn " .
      "ON sess.entity_id = bpn.entity_id " .
      "INNER JOIN {field_data_field_bill_multi_session_root} root " .
      "ON bpn.entity_id=root.entity_id " .
      "WHERE sess.entity_type='node' AND sess.bundle='bill' " .
      "AND bpn.entity_type='node' AND bpn.bundle='bill' " .
      "AND root.entity_type='node' AND root.bundle='bill' " .
      // These three lines have the parameters we'll need.
      "AND sess.field_ol_session_value IN (:session_year) " .
      "AND bpn.field_ol_base_print_no_value IN (:print_no) " .
      "HAVING `key` in (:keys)";
    $result = db_query($query, [
      ':session_year' => array_keys($session_years),
      ':print_no' => array_keys($print_nums),
      ':keys' => array_keys($changes),
    ]);

    // Correlate the query results with the changes being processed.
    while ($one_pn = $result->fetchAssoc()) {
      if (array_key_exists($one_pn['key'], $changes)) {
        $changes[$one_pn['key']]['#root'] = $one_pn['tid'];
      }
    }
  }
}

Open in new window

Top Expert 2004

Author

Commented:
My code works

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial