MySQL Forums :: Workbench - Database Modeling :: Complex drupal query
Complex drupal query
Posted by: John Ophof ()
Date: January 06, 2013 11:49AM
I am looking for weeks to the following issue.
A friend of mine made a query in Access runs fine.
My DB is Mysql using Drupal 7.
I used copy and paste for the table create statements.
I have the following tables at the bottom I describe my view. The complex query runs on the view:
delimiter $$
A table with nodes (D& way):
delimiter $$
CREATE TABLE `node` (
`nid` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'The primary identifier for a node.',
`vid` int(10) unsigned 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.',
PRIMARY KEY (`nid`),
UNIQUE KEY `vid` (`vid`),
KEY `node_changed` (`changed`),
KEY `node_created` (`created`),
KEY `node_frontpage` (`promote`,`status`,`sticky`,`created`),
KEY `node_status_type` (`status`,`type`,`nid`),
KEY `node_title_type` (`title`,`type`(4)),
KEY `node_type` (`type`(4)),
KEY `uid` (`uid`),
KEY `tnid` (`tnid`),
KEY `translate` (`translate`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COMMENT='The base table for nodes.'$$
A table holding week prices:
delimiter $$
CREATE TABLE `field_data_field_price` (
`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',
`field_price_value` int(11) DEFAULT NULL,
PRIMARY KEY (`entity_type`,`entity_id`,`deleted`,`delta`,`language`),
KEY `entity_type` (`entity_type`),
KEY `bundle` (`bundle`),
KEY `deleted` (`deleted`),
KEY `entity_id` (`entity_id`),
KEY `revision_id` (`revision_id`),
KEY `language` (`language`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Data storage for field 5 (field_price)'$$
A table indicating a week is free:
delimiter $$
CREATE TABLE `field_data_field_free` (
`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',
`field_free_value` int(11) DEFAULT NULL,
PRIMARY KEY (`entity_type`,`entity_id`,`deleted`,`delta`,`language`),
KEY `entity_type` (`entity_type`),
KEY `bundle` (`bundle`),
KEY `deleted` (`deleted`),
KEY `entity_id` (`entity_id`),
KEY `revision_id` (`revision_id`),
KEY `language` (`language`),
KEY `field_free_value` (`field_free_value`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Data storage for field 6 (field_free)'$$
delimiter $$
CREATE TABLE `field_data_field_reserved` (
`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',
`field_reserved_value` int(11) DEFAULT NULL,
PRIMARY KEY (`entity_type`,`entity_id`,`deleted`,`delta`,`language`),
KEY `entity_type` (`entity_type`),
KEY `bundle` (`bundle`),
KEY `deleted` (`deleted`),
KEY `entity_id` (`entity_id`),
KEY `revision_id` (`revision_id`),
KEY `language` (`language`),
KEY `field_reserved_value` (`field_reserved_value`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Data storage for field 7 (field_reserved)'$$
A table with week numbers and dates:
CREATE TABLE `field_data_field_saturday` (
`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',
`field_saturday_value` datetime DEFAULT NULL,
PRIMARY KEY (`entity_type`,`entity_id`,`deleted`,`delta`,`language`),
KEY `entity_type` (`entity_type`),
KEY `bundle` (`bundle`),
KEY `deleted` (`deleted`),
KEY `entity_id` (`entity_id`),
KEY `revision_id` (`revision_id`),
KEY `language` (`language`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Data storage for field 111 (field_saturday)'$$
To make things simpeler I created a view like this:
delimiter $$
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER
VIEW `acquia_drupal4`.`vweek` AS select `acquia_drupal4`.`node`.`title`
AS `title`,`acquia_drupal4`.`field_data_field_saturday`.`field_saturday_value`
AS `saturday`,`acquia_drupal4`.`field_data_field_price`.`field_price_value`
AS `price`,`acquia_drupal4`.`field_data_field_free`.`field_free_value`
AS `free`,`acquia_drupal4`.`field_data_field_reserved`.`field_reserved_value`
AS `reserved` from ((((`acquia_drupal4`.`node` left join `acquia_drupal4`.`field_data_field_free`
on((`acquia_drupal4`.`field_data_field_free`.`entity_id` = `acquia_drupal4`.`node`.`nid`)))
left join `acquia_drupal4`.`field_data_field_price` on((`acquia_drupal4`.`field_data_field_price`.`entity_id` = `acquia_drupal4`.`node`.`nid`)))
left join `acquia_drupal4`.`field_data_field_saturday` on((`acquia_drupal4`.`field_data_field_saturday`.`entity_id` = `acquia_drupal4`.`node`.`nid`)))
left join `acquia_drupal4`.`field_data_field_reserved` on((`acquia_drupal4`.`field_data_field_reserved`.`entity_id` = `acquia_drupal4`.`node`.`nid`)))
where (`acquia_drupal4`.`node`.`type` = 'week')$$
The query on the view like this (the 2 is number of weeks which is a variable):
select alle.title,
alle.saturday as startdatum,
DATE_ADD(alle.saturday,INTERVAL (2 * 7) DAY),
MAX (res.reserved) as reserved,
sum (res.price) as price
from ((vweek as alle
left outer join vweek as occ
on (occ.saturday >= alle.saturday
and occ.saturday < DATE_ADD(alle.saturday,INTERVAL (2 * 7) DAY)
and occ.free = 0)
)
left outer join vweek as res
on (res.saturday >= alle.saturday
and res.saturday < DATE_ADD(alle.saturday,INTERVAL (2 * 7) DAY))
)
where occ.saturday is null
group by alle.title, alle.saturday
Error Code: 1630. FUNCTION acquia_drupal4.MAX does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual 0.037 sec
[5:49pm]
Also complains on sum function.
Thanks for patience and help!
John
Edited 1 time(s). Last edit at 01/07/2013 11:43AM by John Ophof.
A friend of mine made a query in Access runs fine.
My DB is Mysql using Drupal 7.
I used copy and paste for the table create statements.
I have the following tables at the bottom I describe my view. The complex query runs on the view:
delimiter $$
A table with nodes (D& way):
delimiter $$
CREATE TABLE `node` (
`nid` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'The primary identifier for a node.',
`vid` int(10) unsigned 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.',
PRIMARY KEY (`nid`),
UNIQUE KEY `vid` (`vid`),
KEY `node_changed` (`changed`),
KEY `node_created` (`created`),
KEY `node_frontpage` (`promote`,`status`,`sticky`,`created`),
KEY `node_status_type` (`status`,`type`,`nid`),
KEY `node_title_type` (`title`,`type`(4)),
KEY `node_type` (`type`(4)),
KEY `uid` (`uid`),
KEY `tnid` (`tnid`),
KEY `translate` (`translate`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COMMENT='The base table for nodes.'$$
A table holding week prices:
delimiter $$
CREATE TABLE `field_data_field_price` (
`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',
`field_price_value` int(11) DEFAULT NULL,
PRIMARY KEY (`entity_type`,`entity_id`,`deleted`,`delta`,`language`),
KEY `entity_type` (`entity_type`),
KEY `bundle` (`bundle`),
KEY `deleted` (`deleted`),
KEY `entity_id` (`entity_id`),
KEY `revision_id` (`revision_id`),
KEY `language` (`language`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Data storage for field 5 (field_price)'$$
A table indicating a week is free:
delimiter $$
CREATE TABLE `field_data_field_free` (
`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',
`field_free_value` int(11) DEFAULT NULL,
PRIMARY KEY (`entity_type`,`entity_id`,`deleted`,`delta`,`language`),
KEY `entity_type` (`entity_type`),
KEY `bundle` (`bundle`),
KEY `deleted` (`deleted`),
KEY `entity_id` (`entity_id`),
KEY `revision_id` (`revision_id`),
KEY `language` (`language`),
KEY `field_free_value` (`field_free_value`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Data storage for field 6 (field_free)'$$
delimiter $$
CREATE TABLE `field_data_field_reserved` (
`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',
`field_reserved_value` int(11) DEFAULT NULL,
PRIMARY KEY (`entity_type`,`entity_id`,`deleted`,`delta`,`language`),
KEY `entity_type` (`entity_type`),
KEY `bundle` (`bundle`),
KEY `deleted` (`deleted`),
KEY `entity_id` (`entity_id`),
KEY `revision_id` (`revision_id`),
KEY `language` (`language`),
KEY `field_reserved_value` (`field_reserved_value`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Data storage for field 7 (field_reserved)'$$
A table with week numbers and dates:
CREATE TABLE `field_data_field_saturday` (
`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',
`field_saturday_value` datetime DEFAULT NULL,
PRIMARY KEY (`entity_type`,`entity_id`,`deleted`,`delta`,`language`),
KEY `entity_type` (`entity_type`),
KEY `bundle` (`bundle`),
KEY `deleted` (`deleted`),
KEY `entity_id` (`entity_id`),
KEY `revision_id` (`revision_id`),
KEY `language` (`language`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Data storage for field 111 (field_saturday)'$$
To make things simpeler I created a view like this:
delimiter $$
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER
VIEW `acquia_drupal4`.`vweek` AS select `acquia_drupal4`.`node`.`title`
AS `title`,`acquia_drupal4`.`field_data_field_saturday`.`field_saturday_value`
AS `saturday`,`acquia_drupal4`.`field_data_field_price`.`field_price_value`
AS `price`,`acquia_drupal4`.`field_data_field_free`.`field_free_value`
AS `free`,`acquia_drupal4`.`field_data_field_reserved`.`field_reserved_value`
AS `reserved` from ((((`acquia_drupal4`.`node` left join `acquia_drupal4`.`field_data_field_free`
on((`acquia_drupal4`.`field_data_field_free`.`entity_id` = `acquia_drupal4`.`node`.`nid`)))
left join `acquia_drupal4`.`field_data_field_price` on((`acquia_drupal4`.`field_data_field_price`.`entity_id` = `acquia_drupal4`.`node`.`nid`)))
left join `acquia_drupal4`.`field_data_field_saturday` on((`acquia_drupal4`.`field_data_field_saturday`.`entity_id` = `acquia_drupal4`.`node`.`nid`)))
left join `acquia_drupal4`.`field_data_field_reserved` on((`acquia_drupal4`.`field_data_field_reserved`.`entity_id` = `acquia_drupal4`.`node`.`nid`)))
where (`acquia_drupal4`.`node`.`type` = 'week')$$
The query on the view like this (the 2 is number of weeks which is a variable):
select alle.title,
alle.saturday as startdatum,
DATE_ADD(alle.saturday,INTERVAL (2 * 7) DAY),
MAX (res.reserved) as reserved,
sum (res.price) as price
from ((vweek as alle
left outer join vweek as occ
on (occ.saturday >= alle.saturday
and occ.saturday < DATE_ADD(alle.saturday,INTERVAL (2 * 7) DAY)
and occ.free = 0)
)
left outer join vweek as res
on (res.saturday >= alle.saturday
and res.saturday < DATE_ADD(alle.saturday,INTERVAL (2 * 7) DAY))
)
where occ.saturday is null
group by alle.title, alle.saturday
Error Code: 1630. FUNCTION acquia_drupal4.MAX does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual 0.037 sec
[5:49pm]
Also complains on sum function.
Thanks for patience and help!
John
Edited 1 time(s). Last edit at 01/07/2013 11:43AM by John Ophof.
| Subject | Views | Written By | Posted |
|---|---|---|---|
| Complex drupal query | 453 | John Ophof | 01/06/2013 11:49AM |
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.
© 2013, Oracle Corporation and/or its affiliates