Bug - Table join from other than id is not working

[koolaid2]

I have testing on more than one site now.

If I make a test_table whit userid and title.

Then make an test_repeat_table whit userid and title.

Then trying to join that test_repeat_table from the test_table list-join from userid to userid. I always end up getting the test_table id as the value in my test_repeat_table userid.


It's basicly not posible to join using the userid at this time.
The only way working is join on the table id.

 

[rob]

so you are trying to use the userid as the primary key on both lists, and join with it? IF so no, that won't work.
We expect the joined table to have a primary key and a foreign key (in this case your user id field) as two separate elements.

I suggest trying to add an id element to test_repeat_table.

-Rob

 

[koolaid2]

Both my test_table and test_table_repeat has an id as Primary Key from the start and I do not change that.


The only thing I do in this test(trying to make it simple) is to make to tabels.

Add the userid element, (and a title element) to each table.

Then joining in the test_table (list) from userid to userid.


Thats all I do.


I culd set the Primary Key on test_table to be userid, then it will work.
But having userid as an Primary Key(autoincrement) not working when making new users obviously.


This my join:

"left JOIN lagre_test_repeat ON lagre_test.userid = lagre_test_repeat.userid"

But it end up whit the lagre_test.id as value in the lagre_test_repeat.userid.


This was working before....

Or are you saying that it's intentionaly that it's only posible to join from Primary Key like this:
"left JOIN lagre_test_repeat ON lagre_test.id = lagre_test_repeat.userid"

That is what it's doing now anyway.

 

[felixkat]

I'm a little confused what you want the end result to be, maybe it's because I'm not sure how you are setting this up. I don't believe you have to create any joins yourself if you did something like.

1) Create test table, create userid and title elements and assign them to test_table.

2) Create new group, assign 'form' to 'test table', click Save once then set the parameter to make the group repeatable.

3) Add new userid and title elements and add them to the newly created group.


That should do all the joins for you assuming that's what you are trying to achieve. Sorry if I have got this totally wrong..... If I have then maybe you could write or draw up the end result you are hoping to achieve.

 

[cheesegrits]

@koolaid - at the moment, we only support joining from an FK to a PK. And the PK has to be an auto-inc.

So no, at the moment you can't join from a userid to a userid (unless you are actually joining to the main J! user table).

This is something we have on our long term road map, to sort out being able to join without involving the PK. But right now, there's some code in the guts of the list and form models that make the assumption that you are joining to a PK, and that code is very, very complex, and not something we can easily change.

-- hugh

 

[koolaid2]

Hmm, so what you really are saying is that the dropdown there when you can select any on tabels elements as join from id is just for fun and has no longer any real function.

No matter way you are selecting it will joins on the primary id when saving.

I have used this this setup on many fabrik-project and it has always used to work.

It is it still working in most ways like in the table, form view or form-edit.
The only times it fails is when saving, eidt or new.



I think it is one of two: the selecting of the "Join from" should be removed which we do not want. Or the saving function regarding this should be fixed.

Joining on the userid like this is an pretty obvious thing to do.

Aren't anybody else using Fabrik to build on top of other components??

I often useing Fabrik as either the frontend or the backend of other components.

Fabrik blend so nicely into so many different components.
flexibility to join whatever to whatever is crucial to still be able to this.

 

[felixkat]

The 'from' doesn't have to be an 'ID' as the 'ID' maybe the 'to'....

So.... 'From' Foo to 'id', opposed to 'from' 'id' 'to' Foo. What Hugh is saying is that at least one end of the join is expected to be a PK or FK but it doesn't matter which way around it is. Hence the from being selectable.

Clearly from my previous post I am a little unsure what you are trying to do so I can't really comment further.

Maybe you need a link table, (as in a many to many), to be able to achieve your goal.

 

[cheesegrits]

Maybe I'm misunderstanding also. As Felix says, you should still be able to select from the 'from' menus, it's just that we expect one "end" of the join to be a PK.

If you join from the PK of the main table to an FK on another table, it can either be a one-to-one or one-to-many join (by setting 'repeat').

If you join from an FK on the main table to a PK on another tbale, it can only be a one-to-one.

-- hugh

 

[koolaid2]

In this case now I'm have a table whit data structured like this that I have collected.

I can do as you say Chees and use the Joomla #__users table as a base to get userid as PK. I have done this in projects lately case of this issue.

Join on userid like this is a thing I often run into.
example culd be to show all of a users sometihing on a userprofile.
not having the possibility to just change the userid to become a key or use the #__users table as a base for that profile. It shuld god to just make sure the userid in the profile table are unique.

And is not reasonable to save the profiles id PK into let say some job-applications.

But ok I see, use the #__users table as a base table I must do.


It's almost always other ways to get around this but it cost extra work for us. Like now, I have remake that application and refit lots of element not like I want them now in the Orginal table.

 

[cheesegrits]

Being able to have a join which doesn't involve a PK on either end of it is something we know we need to fix, so you can use a common key like 'userid' that isn't a PK on either table. BUT ... our code that builds and processes joins is very, very complex, and we would need to add a new option to the join specification to make this work.

The problem is, our existing setup for joins doesn't allow you to specify which is the FK, if neither 'end' of the join is a PK. So our code currently currently examines the table schema for each end, and works out which one has a PK element specified in the join, so we know which table to write what data to (i.e. which one to update the FK on).

This is a "legacy" issue due to the way our join options evolved, coupled with a blind spot we had in our thinking when we built that code. We were focuses on traditional FK -> PK joins, not "shared FK" joins.

Adding the actual option isn't a problem, but modifying our join handling code to use it is another matter. It's something we will do at some point, but probably not in the time frame you need it.

-- hugh

 

[koolaid2]

Sounds god, take this as litle push on the issue.