asked on

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?

Pawan Kumar

can you pls provide few input rows and expected output.

Steve Bink

ASKER

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 Kumar

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

Steve Bink

ASKER

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 Kumar

What is the input. List of documents- meaning comma separated entity_types ??

Steve Bink

ASKER

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.

ASKER CERTIFIED SOLUTION

Steve Bink

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Steve Bink

ASKER

My code works