I have this query to collect information about one order and it becomes quite complicated.
I don’t have data to test, so I ask if anyone has experience with this in small and large datasets, is there a limit on the number of joins you can or should do in one query? Would it make sense to split large queries into smaller parts or is this not significant?
Also, is it legal to have an offer WHEREafter each INNER JOIN?
Thanks for your advice.
Here is the request:
function getOrder($order_id) {
$sql = "SELECT (order.id, order.created, o_status.status,
/* payment info */
order.total, p_status.status,
/* ordered by */
cust_title.title, cust.forename, cust.surname,
customer.phone, customer.email,
cust.door_name, cust.street1,
cust.street2, cust.town,
cust.city, cust.postcode,
/* deliver to */
recip_title.title, recipient.forename, recipient.surname,
recipient.door_name, recipient.street1,
recipient.street2, recipient.town,
recipient.city, recipient.postcode,
/* deliver info */
shipping.name, order.memo,
/* meta data */
order.last_update)
FROM tbl_order AS order
INNER JOIN tbl_order_st AS o_status
ON order.order_status_id = o_status.id
INNER JOIN tbl_payment_st AS p_status
ON order.payment_status_id = p_status.id
INNER JOIN (SELECT (cust_title.title, cust.forename, cust.surname,
customer.phone, customer.email,
/* ordered by */ cust.door_name, cust.street1,
cust.street2, cust.town,
cust.city, cust.postcode)
FROM tbl_customer AS customer
INNER JOIN tbl_contact AS cust
ON customer.contact_id = cust.id
INNER JOIN tbl_contact_title AS cust_title
ON cust.contact_title_id = cust_title.id
WHERE order.customer_id = customer.id)
ON order.customer_id = customer.id
INNER JOIN (SELECT (recip_title.title, recipient.forename, recipient.surname,
/* deliver to */ recipient.door_name, recipient.street1,
recipient.street2, recipient.town,
recipient.city, recipient.postcode)
FROM tbl_contact AS recipient
INNER JOIN tbl_contact_title AS recip_title
ON recipient.contact_title_id = recip_title.id
WHERE order.contact_id = recipient.id)
ON order.contact_id = recipient.id
INNER JOIN tbl_shipping_opt AS shipping
ON order.shipping_option_id = shipping.id
WHERE order.id = '?';";
dbQuery($sql, array((int)$order_id));
$rows = dbRowsAffected();
if ($rows == 1)
return dbFetchAll();
else
return null;
}
Since someone requested a schema for this request, here it is:
DROP TABLE IF EXISTS tbl_contact_title;
CREATE TABLE tbl_contact_title(
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(id),
title CHAR(3)
) ENGINE = InnoDB;
INSERT INTO tbl_contact_title
(title)
VALUES ('MR'),
('MRS'),
('MS');
DROP TABLE IF EXISTS tbl_contact;
CREATE TABLE tbl_contact(
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(id),
contact_title_id INT,
FOREIGN KEY(contact_title_id) REFERENCES tbl_contact_title(id) ON DELETE SET NULL,
forename VARCHAR(50),
surname VARCHAR(50),
door_name VARCHAR(25),
street1 VARCHAR(40),
street2 VARCHAR(40),
town VARCHAR(40),
city VARCHAR(40),
postcode VARCHAR(10),
currency_id INT,
FOREIGN KEY(currency_id) REFERENCES tbl_currency(id) ON DELETE SET NULL
) ENGINE = InnoDB;
DROP TABLE IF EXISTS tbl_customer;
CREATE TABLE tbl_customer(
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(id),
contact_id INT,
FOREIGN KEY(contact_id) REFERENCES tbl_contact(id) ON DELETE SET NULL,
birthday DATE,
is_male TINYINT,
phone VARCHAR(20),
email VARCHAR(50) NOT NULL
) ENGINE = InnoDB, AUTO_INCREMENT = 1000;
DROP TABLE IF EXISTS tbl_order_st;
CREATE TABLE tbl_order_st(
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(id),
status VARCHAR(25)
) ENGINE = InnoDB;
INSERT INTO tbl_order_st
(status)
VALUES
('NEW'),
('PROCESSING'),
('SHIPPED'),
('COMPLETED'),
('CANCELLED');
DROP TABLE IF EXISTS tbl_shipping_opt;
CREATE TABLE tbl_shipping_opt(
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(id),
name VARCHAR(50),
description VARCHAR(255),
cost DECIMAL(6,3)
) ENGINE = InnoDB;
INSERT INTO tbl_shipping_opt
(name, description, cost)
VALUES
('UK Premier', 'U.K. Mainland upto 30KG, Next Working Day', 8.00),
('Europe Standard', 'Most European Destinations* upto 30KG, 2 to 5 Working Days *please check before purchase', 15.00);
DROP TABLE IF EXISTS tbl_payment_st;
CREATE TABLE tbl_payment_st(
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(id),
status VARCHAR(25)
) ENGINE = InnoDB;
INSERT INTO tbl_payment_st
(status)
VALUES
('UNPAID'),
('PAID');
DROP TABLE IF EXISTS tbl_order;
CREATE TABLE tbl_order(
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(id),
customer_id INT,
FOREIGN KEY(customer_id) REFERENCES tbl_customer(id) ON DELETE SET NULL,
contact_id INT,
FOREIGN KEY(contact_id) REFERENCES tbl_contact(id) ON DELETE SET NULL,
created DATETIME,
last_update TIMESTAMP,
memo VARCHAR(255),
order_status_id INT,
FOREIGN KEY(order_status_id) REFERENCES tbl_order_st(id),
shipping_option_id INT,
FOREIGN KEY(shipping_option_id) REFERENCES tbl_shipping_opt(id),
coupon_id INT,
FOREIGN KEY(coupon_id) REFERENCES tbl_coupon(id) ON DELETE SET NULL,
total DECIMAL(9,3),
payment_status_id INT,
FOREIGN KEY(payment_status_id) REFERENCES tbl_payment_st(id)
) ENGINE = InnoDB, AUTO_INCREMENT = 1000;