Finding Drupal nodes by combinations of field values

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?
LVL 51
Steve BinkAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pawan KumarDatabase ExpertCommented:
can you pls provide few input rows and expected output.
0
Steve BinkAuthor 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.
0
Pawan KumarDatabase ExpertCommented:
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
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Steve BinkAuthor 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

0
Pawan KumarDatabase ExpertCommented:
What is the input. List of documents- meaning comma separated entity_types ??
0
Steve BinkAuthor 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.
0
Steve BinkAuthor 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

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Steve BinkAuthor Commented:
My code works
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.