Query Builder Table Order And Joins

[sudwebdesign]

Hello

I have a simple two table relationship customer->actions.

I have define a foreign key relationship using the schema designer and the resulting SQL is as follows:

DROP TABLE IF EXISTS `action`;

CREATE TABLE `action` (

`id` int(3) NOT NULL AUTO_INCREMENT,

`customer_id` int(3) DEFAULT NULL,

`action` varchar(49) DEFAULT NULL,

`enquiry_outcome` varchar(70) DEFAULT NULL,

`signup` varchar(39) DEFAULT NULL,

`action_date` varchar(43) DEFAULT NULL,

`product_name` varchar(40) DEFAULT NULL,

`product_SKU` varchar(33) DEFAULT NULL,

`item_value` varchar(72) DEFAULT NULL,

`product_type` varchar(66) DEFAULT NULL,

`custom_made` varchar(71) DEFAULT NULL,

`gender` varchar(66) DEFAULT NULL,

`reason_for_purchase` varchar(229) DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `customer_idx` (`customer_id`),

CONSTRAINT `action_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=288 DEFAULT CHARSET=utf8;

/*Table structure for table `customer` */

DROP TABLE IF EXISTS `customer`;

CREATE TABLE `customer` (

`id` int(3) NOT NULL,

`surname` varchar(55) DEFAULT NULL,

`forename` varchar(55) DEFAULT NULL,

`email` varchar(255) DEFAULT NULL,

`comment` varchar(255) DEFAULT NULL,

`phone` varchar(25) DEFAULT NULL,

`skype` varchar(55) DEFAULT NULL,

`address` varchar(255) DEFAULT NULL,

`city` varchar(25) DEFAULT NULL,

`postcode` varchar(25) DEFAULT NULL,

`country` varchar(25) DEFAULT NULL,

`gender` enum('F','M','') DEFAULT NULL,

`repeat_purchaser` tinyint(1) DEFAULT NULL,

`date_created` date DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

When I go in to use the query builder against these two tables it always places the `actions` table as the primary table in the query (see action-join.jpg).

[attachment=1771:action-join.jpg]

The foreign key constraint is defined on the `actions` table, so surely this should be the child table.

To get it to work, I have to delete the relationship and re-create it in the query builder (see customer-join.jpg)

[attachment=1772:customer-join.jpg]

Am I doing something wrong in the schema generator?

Thanks

Chris

[Author]

Posts