[Phpmyadmin-devel] RFE #1491

[Ashutosh Dhundhara]

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`

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

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


-- 
Ashutosh Dhundhara
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.phpmyadmin.net/pipermail/developers/attachments/20140804/3a68165c/attachment.html>