Join on other field than id

[greif]

Hi, I have tables customer and addresses, and would like to join them on userid, but it somehow doesn't work in my setup.

For testing I did
first
id | name | user (plugin user)

second
id | surname | user (plugin user)

I joined them on first.user=second.user . When I fill the tables with data first, the join works as expected. But, when I submit the form on joined list, the table1 id is saved to user field on table2 instead of userid.

Am I missing something or is this a bug?
Thanks
Vasik

 

[hubby]

May be one the user in table 1 or table 2 has to be defined as a primary key, to work...

 

[vasikgreif]

Tried this now, it doesn't work - if I understand correctly, the primary key has to be unique. The error I get is:
Store row failed:
Duplicate entry '42' for key 1 SQL=INSERT INTO `first` ( `name`,`user`,`date_time` ) VALUES ( 'dfds','42','2012-02-03 22:03:00' )

 

[hubby]

yes what i mean is that you have to choose to definite the user in the first table for example as the primary key ( so you don't need the id field anymore) and this primary key (the user) will match withe the user field in the second. I'had the same problem with two table and it worked after doing like that. (may be it'sn't the good or best way but it worked.
Pay attention to know if your user records are autoincrement records or not in order to not erased your records (make a copy and work on it).

 

[vasikgreif]

Even after setting the user field as primary key in the first table, I still get the id saved in the second table after submit the joined form...

 

[hubby]

Here is my exemple : i have two table "pharmacies" and "gardes" the gardes table was made with the data of the pharmacies tables and the data of an another table that called "dates" (for day-off). I used the autofill pluging to fill the form with the choosen pharmacy (adress,name,...) and the choosen pharmacy is recorded in the gardes table + the databasejoin pluging to choose a date that will be recorded in the gardes tables too. I didn't used the join option. the join were made automatically. And my table is filling very well. see over here for some screenshot http://fabrikar.com/forums/showthread.php?t=24612 (at the end of the thread)

 

[vasikgreif]

Thanks for help, I see how this works for you, but the autofill option isn't the best one for me. I really would expect to get the value I choose in the join properties to get written into the joined table...

 

[hubby]

i've read again you post, and may be the mode join you've chosen is not for what you expect. What about the "inner join" (you have chosen the "left join")...

 

[vasikgreif]

I tried that, with no luck. Let me reformulate the question:

Consider two tables:
first
id | name | user (plugin user)

second
id | surname | user (plugin user)

I want to join them together on the user field.

left join second on first.user=second.user

This works for displaying the list, if it is filled with data already. But, it doesn't work when submiting the joined form, as the id instead of the userid is written to the second table.

Any ideas?
Thanks
Vasik

 

[troester]

the id instead of the userid is written to the second table.

I can confirm this
Edit: join on field elements, too

 

[greif]

Bumpity bump this one, as this is showstopper bug for me...

 

[troester]

This work around seems to do
edit the joined element user (in group first-[second]), unlink and set it to database join: table first, value and label = user

Edit:
but this is only for viewing the first-[second] elements, you can't modify those elements. Because id in first-[second] is missing any save will create a new record in second table

Edit again: missing id and date_time element may come from testing (deleting +adding new joins, getting error message "element name already in use), maybe it will do on a "clean" list.

 

[greif]

Thanks for help, I tried this now, doesn't seem to work for me, I still get the id saved into db after the submission of joined form.:-( Even if this workaround worked, this won't be an ideal solution for me - I'm going to build quite complex app with many joins, and these glitches can make the building really complex and difficult to maintain...

 

[vasikgreif]

Gentle bump... :'(

 

[vasikgreif]

Bumpity bump.. Rob, cheesgrits, anyone? :'(

 

[rob]

this isn't really going to work

why not do this:


customers
id | name | user (plugin user)

addresses
id | surname | customer_id

left join addresses on customers.id = addresses.customer_id

 

[troester]

Do you say you only can join from table1.id to anything else?

 

[greif]

The problem is I won't always submit the forms at the same time, so I won't always have customer_id. The ideal key to join is userid in this case...

 

[madpad]

Grief, did you get this problem resolved?
If you can post the solution, would benefit us. Thanks.

 

[cheesegrits]

@grief - is this a 1-to-1 join, so "repeat" is No?

I seem to recall there's some really hairy code in out form submission stuff to do with 1-to-1, because when it's a non-repeating join, it isn't obvious which way the join should be, eg. which of the selected fields is the FK.

In other words, if you set Repeat, we know that the field you select on the "other" table is the FK.

But in a 1-to-1, it's could be either one. So we then look to see which of the selected fields is the PK for it's table, on the assumption that one of them will be, and by a process of elimination that means the other one must be the FK. And that assumption about one of them being the PK means that's what we write into the FK ... i.e. we assume the FK will take the PK's value. Which is where a lot of the complexity of our join handling comes in, in that we need to write out the row with the PK on it before we can then write out / update the joined row, with the PK value from the PK's row (because we won't know that value till after writing it out).

But of course, if you are trying to do what you are trying to do, which is link between two tables where neither of the fields involved is the PK of the table ... then our code breaks, and does what you observed, which is write the PK value into whichever one we decided was the FK. Which we shouldn't be doing.

Sorry it's taken me a while, I had to dredge this stuff up from memory of the last time i worked on this code. And I now remember realizing that we need to add a setting on the join options, which tells us which (if either) is the FK in the join, or whether they are just a mutual key, rather than a FK / PK arrangement.

Bottom line, this isn't trivial, and isn't going to be easy to fix.

Ah hah, I just checked the code, and if you look at the tortuous logic in ./components/com_fabrik/models/form.php, in the processToDb() method, starting around line 1364 ("trying to get one-to-one joins working"), around line 1400 is this comment:

							// $$$ hugh - at this point we are assuming that we have a situation where the FK is on the joined table,
							// pointing to PK on main table.  BUT ... we may have a situation where neither of the selected keys are
							// a PK, i.e. two records are joined by some other field.  In which case we do not want to set the FK val!
							// So, we need some logic here to handle that!

So ... I'm going to have to try and work out what we need to do if neither of the selected elements is a PK element.

-- hugh