Join and Groups problem

[jflash]

I create two tabels with phpmyadmin (new0.sql): 'j17_tootetabel' and 'j17_kauplused'.
I create new list "Tooted" with "Link to table" whera table is 'j17_tootetabel'.
List creats group "Tooted" and elements for it f.rom table.
I create join (see attached new2.jpg)
Join creats group "Tooted- [j17_kauplused]" and elements for it.
List with join works correctly and i see all elements.
Now i go to Groups, open group "Tooted- [j17_kauplused]" and i dont change anything, just push button "Save and Close". Now list not working, just show me an error:
getData:Unknown column 'j17_tootetabel.Longitude' in 'field list' SQL=SELECT SQL_CALC_FOUND_ROWS DISTINCT `j17_tootetabel`.`id` AS `j17_tootetabel___id`, `j17_tootetabel`.`id` AS `j17_tootetabel___id_raw`, `j17_tootetabel`.`date_time` AS `j17_tootetabel___date_time`, `j17_tootetabel`.`date_time` AS `j17_tootetabel___date_time_raw`, `j17_tootetabel`.`Picture` AS `j17_tootetabel___Picture`, `j17_tootetabel`.`Picture` AS `j17_tootetabel___Picture_raw`, `j17_tootetabel`.`Availability` AS `j17_tootetabel___Availability`, `j17_tootetabel`.`Availability` AS `j17_tootetabel___Availability_raw`, `j17_tootetabel`.`Retailer` AS `j17_tootetabel___Retailer`, `j17_tootetabel`.`Retailer` AS `j17_tootetabel___Retailer_raw`, `j17_tootetabel`.`Importer` AS `j17_tootetabel___Importer`, `j17_tootetabel`.`Importer` AS `j17_tootetabel___Importer_raw`, `j17_tootetabel`.`Organic` AS `j17_tootetabel___Organic`, `j17_tootetabel`.`Organic` AS `j17_tootetabel___Organic_raw`, `j17_tootetabel`.`PackageSize` AS `j17_tootetabel___PackageSize`, `j17_tootetabel`.`PackageSize` AS `j17_tootetabel___PackageSize_raw`, `j17_tootetabel`.`Product_description` AS `j17_tootetabel___Product_description`, `j17_tootetabel`.`Product_description` AS `j17_tootetabel___Product_description_raw`, `j17_tootetabel`.`EAN` AS `j17_tootetabel___EAN`, `j17_tootetabel`.`EAN` AS `j17_tootetabel___EAN_raw`, `j17_tootetabel`.`Product_name` AS `j17_tootetabel___Product_name`, `j17_tootetabel`.`Product_name` AS `j17_tootetabel___Product_name_raw`, `j17_tootetabel`.`Brand` AS `j17_tootetabel___Brand`, `j17_tootetabel`.`Brand` AS `j17_tootetabel___Brand_raw`, `j17_tootetabel`.`Product_category` AS `j17_tootetabel___Product_category`, `j17_tootetabel`.`Product_category` AS `j17_tootetabel___Product_category_raw`, `j17_tootetabel`.`NR` AS `j17_tootetabel___NR`, `j17_tootetabel`.`NR` AS `j17_tootetabel___NR_raw`, `j17_tootetabel`.`pildike` AS `j17_tootetabel___pildike`, `j17_tootetabel`.`pildike` AS `j17_tootetabel___pildike_raw`, `j17_tootetabel`.`Longitude` AS `j17_tootetabel___Longitude`, `j17_tootetabel`.`Longitude` AS `j17_tootetabel___Longitude_raw`, `j17_tootetabel`.`Latitude` AS `j17_tootetabel___Latitude`, `j17_tootetabel`.`Latitude` AS `j17_tootetabel___Latitude_raw`, `j17_tootetabel`.`id` AS slug , `j17_tootetabel`.`id` AS `__pk_val` FROM `j17_tootetabel` LEFT JOIN `j17_kauplused` AS `j17_kauplused` ON `j17_kauplused`.`NR` = `j17_tootetabel`.`NR` WHERE `j17_tootetabel`.`id` IN (1,2,3,4,5,6,7,8,9,10)

I install Xampp with fresh Joomla and pkg_fabrik_3.0a2.zip, then i downloaded yesterdays source from Github and replace all files with it. Same issue after saving group...

 

[Jaanus]

My first impression is that some of the selected fields in this query are actually in the joined table. For example it looks for j17_tootetabel.Longitude that should actually be j17_kauplused.Longitude and the same thing with Latitude (just thinking logically - geo coordinates may belong to shops rather than to products )

So I think in this case saving a group with joined data makes some unwanted changes somewhere and after that Fabrik thinks the joined fields being a part of the main table, right?

 

[jflash]

Looks like that. But under "Elements" all right. If i remove join, save list and create join again - list works. But, again into grops, save - and same error...
You can test it, databases are attached

 

[Jaanus]

Played with it a little. Now look at this:
http://j17.nurmoja.net.ee/index.php/tooted-ja-kauplused

One part of your problems came from the "from" and "to" fields in the table join. Perhaps because the first was not primary key of main table. I created one additional table, "map table" j17_toode_pood with fk-s toode_id and pood_id, deleted "original" join and joined j17_toode_pood to j17_tootetabel FROM id to toode_id; additionally made it repeatable.
Then I changed the new joined groups label to normal (Mis poodides m??akse instead of j17_toode_pood), saved it - and everything was OK. You can see table and add new records, edit them...

BUT - something unexpected still happened. This action added 2 new elements in fields table (one with existing name id, another named parent_id, perhaps both fake - no parent_id in any db table!) and one "additional join" to a "fake table" (nonexisting) j17_tootetabel_13_repeat in table admin form! See pics:


Who knows - perhaps everything is normal, at least this form seems to work

 

[rob]

thanks for the thorough report - it made it very easy to see the issue and fix it
The fix is in the latest code at github. Basically when you saved the group if it wasn't repeating fabrik thought it wasn't a join and unset the group 'is_join' property hence the ensuing chaos!

-Rob

 

[Jaanus]

Thanks, I will try ASAP, in some 15 minutes! There were still issues I didn't notice and as I checked better I saw that the additional tables were still existing and so on... but let's see what will happen after update.

 

[Jaanus]

It still creates another group and another join! See the picture (upper 2 rows):

Or I downloaded the update too early?

 

[jflash]

For now Filters in Elements does not work, just show All option in dropdown.

 

[Jaanus]

Still no changes. Maybe I have to explain with more details.

Picture 1: I create a new repeating table join and insert successfully new joined record. Everything is OK but the joined group label that is currently the db table name.


Picture 2: I change the group label and save. Here is the beginning of problems. New empty db table 17_tootetabel_19_repeat is created with a new field parent_id and new join as well. Not shown in pictures but the parent_id and new table's id appeared also in elements table as they were in the same joined group.


Picture 3: We see the new id and parent_id in the table view. Now I try to add an additional joined record. After saving we see duplicated first record and the id and parent_id (the last two are inserted into new db table 17_tootetabel_19_repeat and both shop id-s as well). OK, I try to resave the entry. I change the lower "Mahemarket" with "Rohujuur" and save. Nothing changes. Well, I open the table admin where we see 2 joins. I remove the "new one" and save. Then I reopen - and what we see? Both joins are disappeared!


By describing the issue I got also one idea. As we can say already while creating a table join should it be repeated or not - perhaps it wouldn't be hard to add there also possiblity to give normal label to the new joined group there, in form admin?

 

[Jaanus]

Tested a little more. Only way to avoid such problems: first create nonrepeating join, then rename group label and only then go back to the list admin form and make the join group repeatable.

In case when you want to rename the group label again, you also have to set repeating to no, save changes and then set repeating back to yes (again from list admin form, NOT from group admin form!)

 

[rob]

I was referring to the original issue which has been resolved.

I'm not sure what you are seeing isn't just the way things are in fabrik3.0.
Repeating groups are always stored in their own database table. That is regardless of whether they were created via a database join or not.

Any chance you can make a video of all the steps you went through - its hard to follow from the screen shots?

thx
Rob

 

[Jaanus]

Any chance you can make a video of all the steps you went through - its hard to follow from the screen shots?

thx
Rob

As it's done last week - any ideas?

By describing the issue I got also one idea. As we can say already while creating a table join should it be repeated or not - perhaps it wouldn't be hard to add there also possiblity to give normal label to the new joined group there, in form admin?

And what about this?

 

[rob]

the double join issue should be fixed now.

By describing the issue I got also one idea. As we can say already while creating a table join should it be repeated or not - perhaps it wouldn't be hard to add there also possiblity to give normal label to the new joined group there, in form admin?

Ill add a low priority ticket on that one, I agree it would be nice, but I want to focus on fixing existing issue at the moment.

-Rob