Unsupported Screen Size: The viewport size is too small for the theme to render properly.

Query Builder Table Order And Joins

forums forums SQLyog Using SQLyog Query Builder Table Order And Joins

This topic contains 1 reply, has 0 voices, and was last updated by  sudwebdesign 6 years, 7 months ago.

  • Author
    Posts
  • #12745

    sudwebdesign
    Member

    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

  • #33629

    ashwin
    Member
    Quote:

    Am I doing something wrong in the schema generator?

    Well.. It is not Schema generator. It is Query generator.

    From the help document(F1):

    Quote:
    Note that LEFT and RIGHT JOINs have a different meaning depending on the direction of the JOIN. LEFT JOINing table a on table b returns the same as RIGHT JOINing table b on table a (and vice versa). With INNER JOINs the result is the same. The JOIN direction is decided by the program from recording in which direction the line in the canvas was drawn. If you are not very familiar with JOINs it will probably be a good idea always to use a fixed procedure: for instance consider always having 'child' tables to the left of 'parent' tables and always draw in the canvas from left to right. Lines drawn by the program itself (due to the existence of Foreign Keys) will always be considered as being drawn from 'child' to 'parent'.

    The reason is simply this- Lines drawn by the program itself (due to the existence of Foreign Keys) will always be considered as being drawn from 'child' to 'parent'. Anyway with INNER JOIN the result is the same if you change the direction. BTW [background=rgb(255, 255, 255)]Foreign keys in SQL are used to check and enforce referential integrity, not to join tables. From MySQL docs: [/background]http://dev.mysql.com…reign-keys.html

  • #33630

    sudwebdesign
    Member

    Hi Ashwin, thanks for your reply.

    Quote:
    Foreign keys in SQL are used to check and enforce referential integrity, not to join tables. From MySQL docs: http://dev.mysql.com…reign-keys.html

    Yes, but also I'm using a framework which auto-generates models from the database. the existence of the foreign keys creates the model relationships thereby saving huge amounts of my time.

    Quote:
    The reason is simply this- Lines drawn by the program itself (due to the existence of Foreign Keys) will always be considered as being drawn from 'child' to 'parent'.

    On that note also, if everytime I want to use query builder, which is effectively building the realtionships the wrong way round, I have to delete the relationship and then drag parent->child. Not a time saving function.

    Seems like a bug to me – even if it's currently literally correct, it's not helping the user -> or maybe it should be an option in preferences….

You must be logged in to reply to this topic.