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

Le 2014-08-02 06:42, Ashutosh Dhundhara a écrit :

On Sat, Aug 2, 2014 at 2:45 AM, Marc Delisle <marc@infomarc.info mailto:marc@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.

Le 2014-08-03 13:37, Ashutosh Dhundhara a écrit :

On Sat, Aug 2, 2014 at 5:28 PM, Marc Delisle <marc@infomarc.info mailto:marc@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@infomarc.info
<mailto:marc@infomarc.info>
> <mailto:marc@infomarc.info <mailto:marc@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.

Ashutosh Dhundhara a écrit :

On Sat, Aug 2, 2014 at 2:45 AM, Marc Delisle marc@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