Dynamic Gets with WHERE statement on join table doesn't work

dformdotdk commented

2019-12-13 23:05:42 +00:00

(Migrated from github.com)

Hi

When I use a dynamic get and build a query with joins, and use a join table in my where statement, then the compiler doesn't add it to the model. It simply skips.

If I target the primary table, e.g. a.first_name = \'Super\' then the where statement is added to the model. But I can't target a join table in my where statement. Is this a bug perhaps?

Steps to reproduce the issue

Build a query with joins and add a where statement on the join table

Expected result

Where statement added to the model query

Actual result

Where statement not added to the model query

System information (as much as possible)

OS Name & Version: Ubuntu 16.04
MySql Version: 5.7
Apache Version: 2.4
PHP Version: 7.2
Joomla Version: 3.9.13
JCB Version: 2.10.9
Browser:

Additional comments

Hi When I use a dynamic get and build a query with joins, and use a join table in my where statement, then the compiler doesn't add it to the model. It simply skips. If I target the primary table, e.g. `a.first_name = \'Super\'` then the where statement is added to the model. But I can't target a join table in my where statement. Is this a bug perhaps? ### Steps to reproduce the issue Build a query with joins and add a where statement on the join table ### Expected result Where statement added to the model query ### Actual result Where statement not added to the model query ### System information (as much as possible) - OS Name & Version: Ubuntu 16.04 - MySql Version: 5.7 - Apache Version: 2.4 - PHP Version: 7.2 - Joomla Version: 3.9.13 - JCB Version: 2.10.9 - Browser: ### Additional comments

Llewellyn commented

2019-12-15 17:18:22 +00:00

Well currently the JOINT options only allow for field to field relationship, not field to string.

So if you look at the hint you see that the On Field and Join Field are both having table field notation.

If you would like to filter the relationship, you must use the Tweak tab Filter subform as Filter Type > FunctionVar and State Key > $supper and Table Key > a.first_name

To set the $supper var, use the Custom Script tab, testing which PHP area gets inserted before the query is made.

Well currently the JOINT options only allow for field to field relationship, not field to string. ![image](https://user-images.githubusercontent.com/5607939/70866139-a26ae700-1f6e-11ea-9b20-2e5bd8c015f1.png) So if you look at the hint you see that the **On Field** and **Join Field** are both having table field notation. If you would like to filter the relationship, you must use the Tweak tab **Filter** subform as **Filter Type** > `FunctionVar` and **State Key** > `$supper` and **Table Key** > `a.first_name` To set the `$supper` var, use the Custom Script tab, testing which PHP area gets inserted before the query is made.

dformdotdk commented

2019-12-15 17:32:18 +00:00

(Migrated from github.com)

Maybe I didn't explain it well enough.
It's not in the join, but in the WHERE statement in the tweak tab I'm having an issue. And using a string here works fine, if I do it on the main table (a.columnname) - just not for any field coming from a join table (e.g. b.columnname). And that's the issue...
Skærmbillede 2019-12-15 kl 18 28 51

Maybe I didn't explain it well enough. It's not in the join, but in the WHERE statement in the tweak tab I'm having an issue. And using a string here works fine, if I do it on the main table (a.columnname) - just not for any field coming from a join table (e.g. b.columnname). And that's the issue... [](url) <img width="1889" alt="Skærmbillede 2019-12-15 kl 18 28 51" src="https://user-images.githubusercontent.com/6020984/70866356-e3f89380-1f68-11ea-8bf8-f028e12f3d0d.png">

Llewellyn commented

2019-12-15 17:37:27 +00:00

Okay let me try on my side and see if I get the same issue. One moment!

Llewellyn commented

2019-12-15 17:57:28 +00:00

Okay so have a look at this quick trace... let me know https://youtu.be/a4j8yn9SCt4 (give google a few minutes to process the video)

dformdotdk commented

2019-12-15 21:28:31 +00:00

(Migrated from github.com)

Thank you for your video. I also have a video for you :-)
Please take a look here: https://www.youtube.com/watch?v=fL-HUUC6kpg

For some reason, unknown to me, the WHERE statement works with my main table, and first two join tables, but not on the third table. The query is valid and is returning data - also for the third table. So the only thing I can think of, is an issue using multiple joins beyond 2 join tables???

Thank you for your video. I also have a video for you :-) Please take a look here: https://www.youtube.com/watch?v=fL-HUUC6kpg For some reason, unknown to me, the WHERE statement works with my main table, and first two join tables, but not on the third table. The query is valid and is returning data - also for the third table. So the only thing I can think of, is an issue using multiple joins beyond 2 join tables???

Llewellyn commented

2019-12-15 22:16:10 +00:00

I think I know what the issue is, currently JCB is not able to like the d table with the a and so the filter is lost, it is not due to the limit of the number of joints, I have many joins in many projects and they work in filtering just fine. But they all hold direct relationship with the a table, yet you have it related to the c table. This seems to be the cause of the limitation. Now we can try and fix this, but the way I build the DynamicGet query builder has made it very fast, but also crazy complex.

So before I jump in and try to improve this, can you just check the above, try linking your third joint to a and see if the filter for table d gets added.

Since if we can indeed establish that it is due to its relationship to c instead of a that the filter is dropped, I can at least know where to start.

I think I know what the issue is, currently JCB is not able to like the **d** table with the **a** and so the filter is lost, it is not due to the limit of the number of joints, I have many joins in many projects and they work in filtering just fine. But they all hold direct relationship with the **a** table, yet you have it related to the **c** table. This seems to be the cause of the limitation. Now we can try and fix this, but the way I build the DynamicGet query builder has made it very fast, but also crazy complex. So before I jump in and try to improve this, can you just check the above, try linking your third joint to **a** and see if the filter for table **d** gets added. Since if we can indeed establish that it is due to its relationship to **c** instead of **a** that the filter is dropped, I can at least know where to start.

dformdotdk commented

2019-12-15 23:20:21 +00:00

(Migrated from github.com)

So before I jump in and try to improve this, can you just check the above, try linking your third joint to a and see if the filter for table d gets added.

It did. Funny though. If I changed it so that my where statement was: if c.field_id = a.id it didn't work. But if a.id = c.field_id it worked. So the order also seem to matter.

> So before I jump in and try to improve this, can you just check the above, try linking your third joint to **a** and see if the filter for table **d** gets added. It did. Funny though. If I changed it so that my where statement was: if c.field_id = a.id it didn't work. But if a.id = c.field_id it worked. So the order also seem to matter.

Llewellyn commented

2019-12-16 01:50:03 +00:00

So this is not a bug, it is just a limitation in JCB. Maybe not the expected behavior, but looking at the code it is doing what it was written to do. I can try and improve this, give me a few days.

Then if you turn the order around the relation is lost, the fields are named On Field and Join Field and this is important to keep in mind. Since ON targets the main field, and JOIN the join tables target field, you can't turn them around.

Now we know that the query gets added correctly so it is just the WHERE filter that gets missed. So I will just make sure the filters get correctly channeled.

So this is not a bug, it is just a limitation in JCB. Maybe not the expected behavior, but looking at the code it is doing what it was written to do. I can try and improve this, give me a few days. Then if you turn the order around the relation is lost, the fields are named **On Field** and **Join Field** and this is important to keep in mind. Since _ON_ targets the main field, and _JOIN_ the join tables target field, you can't turn them around. Now we know that the query gets added correctly so it is just the WHERE filter that gets missed. So I will just make sure the filters get correctly channeled.

Llewellyn commented

2019-12-16 02:11:17 +00:00

Then if you turn the order around the relation is lost, the fields are named On Field and Join Field and this is important to keep in mind. Since ON targets the main field, and JOIN the join tables target field, you can't turn them around.So that means

So this means as On Field->c.field_id and Join Field->a.id can never exist since the a table is never a Joint table.

> Then if you turn the order around the relation is lost, the fields are named On Field and Join Field and this is important to keep in mind. Since ON targets the main field, and JOIN the join tables target field, you can't turn them around.So that means So this means as _On Field_->`c.field_id` and _Join Field_->`a.id` can never exist since the **a** table is never a _Joint_ table.

Llewellyn commented

2019-12-16 03:03:22 +00:00

Okay this was bugging me so I added the correct behavior, please test this improvement in latest push to staging branch.

Okay this was bugging me so I added the correct behavior, please test this improvement in [latest push](https://github.com/vdm-io/Joomla-Component-Builder/commit/2ed53b2a3b050d151975259c9d116eec976cd665) to staging branch.

👍 1

dformdotdk commented

2019-12-16 11:05:15 +00:00

(Migrated from github.com)

It it working for me now :-) Awesome.
Thank you!

It it working for me now :-) Awesome. Thank you!

👍 1

Dynamic Gets with WHERE statement on join table doesn't work #500

Steps to reproduce the issue

Expected result

Actual result

System information (as much as possible)

Additional comments