[Phpmyadmin-devel] RFE #1491

[Marc Delisle]

Le 2014-08-03 13:37, Ashutosh Dhundhara a écrit :
> On Sat, Aug 2, 2014 at 5:28 PM, Marc Delisle <marc at infomarc.info
> <mailto:marc at infomarc.info>> wrote:
> 
>     Le 2014-08-02 06:42, Ashutosh Dhundhara a écrit :
>     > On Sat, Aug 2, 2014 at 2:45 AM, Marc Delisle <marc at infomarc.info
>     <mailto:marc at infomarc.info>
>     > <mailto:marc at infomarc.info <mailto:marc at infomarc.info>>> wrote:
>     >
>     >     Le 2014-08-01 16:32, Ashutosh Dhundhara a écrit :
>     >     > Hi all,
>     >     >
>     >     > I was looking into RFE #1491
>     >     > <http://sourceforge.net/p/phpmyadmin/feature-requests/1491/>
>     (Support
>     >     > InnoDB for database Query by example). If I am not wrong, is the
>     >     concept
>     >     > of displaying the 'LEFT JOIN' is something like as discussed in
>     >     the case
>     >     > below:
>     >     >
>     >     > Lets say I have two tables:
>     >     >
>     >     > CREATE TABLE `product` (
>     >     >  `id` int(11) NOT NULL,
>     >     >  `price` decimal(10,0) DEFAULT NULL,
>     >     >  PRIMARY KEY (`id`)
>     >     > )
>     >     >
>     >     > CREATE TABLE `product_order` (
>     >     >  `product_id` int(11) NOT NULL,
>     >     >  `customer_id` int(11) NOT NULL,
>     >     >  KEY `product_category` (`product_id`),
>     >     >  KEY `customer_id` (`customer_id`),
>     >     >  CONSTRAINT `product_order_ibfk_4` FOREIGN KEY (`product_id`)
>     >     REFERENCES
>     >     > `product` (`id`)
>     >     > )
>     >     >
>     >     > Now in QBE interface, when I will select
>     >     `product_order`.`product_id` in
>     >     > first column drop-down and `product`.`id` in second column
>     >     drop-down, I
>     >     > should get the following result:
>     >     >
>     >     > FROM `db_name`.`product_order` LEFT JOIN `db_name`.`product`
>     >     > ON `product_order`.`product_id` = `product`.`id`
>     >     >
>     >     > In general, 'LEFT JOIN' with foreign table and ON condition
>     between
>     >     > master column and foreign column. Am I getting it right?
>     >
>     >     Hi Ashutosh,
>     >
>     >     For the record, you are using the example given in [0].
>     >
>     >     Note that to make a more complete example, you have to click
>     >     the Show checkboxes for each selected column. Also, you would
>     have to
>     >     choose another column, for example the price. When the feature is
>     >     implemented, as per your suggestion this would generate:
>     >
>     >     select `product`.`price`, `product_order`.`product_id`,
>     `product`.`id`
>     >     FROM `product_order` LEFT JOIN `product` ON
>     `product_order`.`product_id`
>     >     = `product`.`id`
>     >
>     >     Now, choosing which table is on which side of the LEFT JOIN is
>     open to
>     >     interpretation.
>     >
>     >     The current QBE interface does not permit to specify which table
>     >     you want on the left side and it's a shortcoming. It's not
>     clear if
>     >     the user wants to show all of the products, even those for which
>     >     there are no order. If this is the case, it should become
>     >
>     >     FROM `product` LEFT JOIN `product_order` ...
>     >
>     >     I tend to think that this is what the user wants. So, this is the
>     >     statement we should aim for:
>     >
>     >     select `product`.`price`, `product_order`.`product_id`,
>     `product`.`id`
>     >     FROM `product` LEFT JOIN `product_order` ON
>     `product_order`.`product_id`
>     >     = `product`.`id`
>     >
>     >     producing results such as:
>     >
>     >     price   product_id      id
>     >     100     10              10
>     >     2000    20              20
>     >     3000    NULL            30
>     >
>     >     where there is no order for product id 30.
>     >
>     >     I'm not sure whether we should let the user decide which table
>     should be
>     >     on the left side. Things can become tricky in the UI when you
>     add a
>     >     third table like customer.
>     >
>     >     [0]
>     >    
>     http://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html
>     >
>     >
>     > Thanks Marc,
>     >
>     > That was quite comprehensive description of the problem. I also think
>     > that user generally want to display (in context with above
>     example) all
>     > the products, even those for which there are no order. So in that case
>     > following statement becomes our objective:
>     >
>     > SELECT `product`.`price`, `product_order`.`product_id`, `product`.`id`
>     > FROM `product` LEFT JOIN `product_order` ON
>     `product_order`.`product_id`
>     > = `product`.`id`
>     >
>     > But if we talk about the current scenario, in case of 'Internal
>     > relations', the QBE generates the following statement:
>     >
>     > SELECT `product`.`price`, `product_order`.`product_id`, `product`.`id`
>     > FROM `product_order` LEFT JOIN `product` ON
>     `product_order`.`product_id`
>     > = `product`.`id`
>     >
>     > I selected `product`.`price`, `product_order`.`product_id` and
>     > `product`.`id` in columns 1, 2 and 3 respectively with 'Show'
>     checkboxes
>     > checked.
>     > And I have an internal relation defined from
>     > `product_order`.`product_id` to `product`.`id`.
>     > I am a little confused here. Am I doing something wrong?
> 
>     This is the current behavior of QBE for internal relations. In
>     libraries/DBQbe.class.php, if you look for "LEFT JOIN", you'll find what
>     is the logic used.
> 
>     So much the better if this logic can be improved when doing your task
>     with InnoDB. If not, we can leave the logic for internal relations as it
>     is currently.
> 
> 
> Thanks Marc.
> 
> I tried to improve the current logic for internal relations as suggested
> by you. It works fine for my test cases.
> Can you point me to some more complex test cases for this or should I
> create a pull request?

Please create a pull request.

-- 
Marc Delisle | phpMyAdmin