[Phpmyadmin-devel] RFE #1491

[Marc Delisle]

Ashutosh Dhundhara a écrit :
> On Sat, Aug 2, 2014 at 2:45 AM, Marc Delisle <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
>>
>>
> Hi Marc,
> 
> In case of the complex example given in [0], if we select `product`.`id`,
> `product`.`price` and `product_order`.`product_id` columns then should the
> QBE interface generate the following SQL:
> 
> SELECT `product`.`id`, `product`.`price`, `product_order`.`product_id`
> FROM `product`
>  LEFT JOIN `GSoC14`.`product_order` ON `product`.`category` =
> `product_order`.`product_category` AND `product`.`id` =
> `product_order`.`product_id`

Yes, this looks fine; to prove it, enter some data in these tables and 
try it.

> 
> Also, if we select `product`.`id`, `customer`.`id` and
> `product_order`.`product_id` then what should be the output in case of 3
> tables?

I am not sure how the UI can help the user indicate his/her intentions 
in the case of three tables. Have a look at [1] for an example of double 
LEFT JOIN.

[1] http://stackoverflow.com/questions/8416967/double-left-join

> 
> [0] http://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html


-- 
Marc Delisle (phpMyAdmin)