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