Fellow Chef's,

I know that there is a great appetite for this, as much from our head chef (@admin) as much as the rest of us pot-washers, junior chefs and alike however, this is as much as anything, a suggestion of how this might/could be implemented. (Forgive the cook puns btw - I'm no comedian/writer)

OK, so like many, I have the need to relate a single item in on model to another item elsewhere - a user to a project for instance. To clarify, lets say this is just a 3 table setup:

1. Users (users) - let's assume that ultimately this will be the core joomla user table
2. Projects (projects)
3. Project Users (project_users) - contains 2 fields, project_id & user_id

Simply put, project_users is a join table whereby, it is not necessary to store an id for each record, hence there only being 2 fields... Each project (record in projects) can have multiple associated users and each user (record in users) can have multiple project associated with it. However, each user/project should only be associated with one another ONCE, i.e. project_users table should contain distinct user_id/project_id combinations. Example;POSSIBLE

user_id	project_id
62	1
62	2
62	3
63	1
63	2
63	3

NOT POSSIBLE - invalid highlighted red

user_id	project_id
62	1
62	1
63	2
63	2
64	3
64	3

Given that there's no need for an explicit id field in such a join table (although there could be & for the moment, COOK will auto generate one anyway), we would need to establish the combination of the 2 candidate keys (user_id & project_id) as the primary/unique key.

HOW???
It seems to me that there would be 2 ways of going about this;

1. A new table type parameter join_table in the table itself that when checked would NOT auto-generate an id field. Instead it would request at least 2 fields (maybe more) - the combination of these would be enforced as unique in the table, model & controller. A new class for each field that extends their parent tables maybe???
2. An added Unique Combination parameter in the FK field type. Naturally, it would be super-awesome here as it already has options for FK integrity that could be called whenever a project and or user was deleted.

Simple version of this in SQL to alter table after the fact -
Anyway, what are your thoughts on this COOKS - would love to hear them?!!!!!!!

Gez

3. A third field that saves uid62pid1 (user_id = 62 and project_id = 1) when you save your new user/project link, that third field with the UNIQUE parameter applied...
But where should we save this... In the model or table ? When it prepares the data to store them ? I must say, with the new version of cook, i'm a bit lost... i could do it in cook v1.5, but now i can't find the right place anymore...

Hi @VeCrea,
I don't quite follow...? What's the advantage of this?

i was just thinking that there'd be an 'add user to project view where you select the project from combo then the user from combo to save them to the join table.

RE:Would that depend on where your DB automations are handled (Builder->Config->DB automatisms)?

G

audibleid wrote: Because it creates an unique value that contains both the user id and project id. And if the unique parameter is set, then you can't create the same association twice.

Yeah, found out... in my case, it's in Table, in the "public function store($updateNulls = false)"

Cool, glad you found it!

There must be a way to check that 62:3 (or any other) combination doesntt exist in the model, I just can't quite get my head around it at the moment...

I know it's hardly any additional data to store the data as you suggest - I suppose in sql a concat("usr",u.id,"prj",1) would do it - but, I just don't think the extra data needs to be stored. I always try to be as anal as possible where data integrity's concerned.

That said, I suppose concatenating the vals in JS/JQuery would be best, as it could do it pre-php validation passing the concatenated ref to php to look up, right? Similar to the way the 'alias' field works, taking whatever you put in the title if alias is empty, right?

Gez

I am a simple guy with simple solutions

I know the functionality exists but as I said I just can't get my head around it at the minute!

Did you see the sql to do it:
I just need to work out how to get the model to obey that construct, then it will just be a case of the save, updated and delete routines to follow through too...

I wish my brain wouldn't complicate things!!! (all at the same time too!)

Ok i will let you in on a secret about using unique within Cook at present before you get too carried away
As i have discovered - at present within Cook the unique property is controlled at the database level

just in case someone gets a bright idea to add two strings...
-There is maximum of 1000 bytes that can be stored in a unique field at the database level
- the build will fail if you have defined something like this where both fields are defined as unique
`username` VARCHAR(150),
`email` VARCHAR(255)

you need to be aware of the limits of unique at the database level
The formula below will keep you out of trouble
(uniqueStringLength1 x 3) + (uniqueString2Length2 x 3) +( uniqueString3Length3 x 3) + .... >= 1000

For more information see this post
www.j-cook.pro/forum/32-trouble-shooting...le-fails-at-sql#3873

Hi BTB300!

Thanks for the tip!

Not that I was specifically aware of the 1000 bytes restriction per se, but 'economy' was why I was looking to enforce the combination candidate keys (pairs of integer vals like 62:1). as opposed to concatenating a string out of them as a ref...

Something to the effect of:
Any suggestions on how to achieve this?

BTW, if this is at table level, the above SQL would invalidate combinations that I was trying to store that exist already, but where/how can I leverage this so once a user is picked in one field, only projects that are possible are loaded in the second select and vice versa?

Naturally, these 2 selects would be ajax combos...

A custom query??? But where???

Many thanks for your help!!!

Gez

Simple

Concat the two fields and set the field to be unique at the data base level

ALTER TABLE project_users ADD UNIQUE (user_id,project_id) is a MySql command run it against the DB on the field you want to be unique

or use something like this a few modifications and you will be on your way

HOPE IT HELPS

Hi @BTB300,

Thanks for your post, I definitely want to try to do it using a similar method to the one we both posted earlier in the topic:as I'm going to have lots of join tables in my application that only require 2 foreign keys and it would almost seem a waste to bother to concatenate the values.

Do you have any idea how this might be achieved?audibleid (me) wrote:
many thanks,

Gez

I think that your requirements mean that the table should be normalized into 2 tables:

Voucher
where both Abbreviation and (voucher_type, voucher_sub_type) are UNIQUE:

voucher_type voucher_sub_type Abbreviation
INV DOM ID allowed
INV INT ID not allowed (if the above row exists)

Voucher_Dates
where Abbreviation is a FOREIGN KEY to Voucher(Abbreviation) and
(date, Abbreviation) is either PRIMARY or UNIQUE:

date Abbreviation
1/1/2000 ID
15/1/2000 ID

mark d wrote: Once again, please read the entire thread before posting as you would have realised that my question pertained to joining 2 existing tables in a 'join' table. This is because my data has already been noramalised into 2 tables.

This join table is actually the 3rd table.

Gez

mark d wrote:
This is totally irrelevant, as almost of all post you are submitting.
The point is if you are not more attentioned for posting, I may will take a decision to solve this problem.
If you are responding to a post, you must reuse the example and DO DOT MAKE REFERENCE TO SOMETHING ELSE.
I ask you for the moment also to remove your signature please. No link for you until you are not more involved and continue to spam with inapropriate contents.

OK, just did a bit of google surfing and I must say, this looks promising...

Someone has updated JTable class to be able to account for passing in an array of composite primary keys (pks) which will suit my requirements perfectly for this. Essentially, it allows passing in an array of fields to be used as PK or a single field (typically just 'id').

Anyway, here's the link to the github code: github.com/joomla/joomla-platform/pull/1606/files

Therefore, I'm going to look into this and see whether it is in fact something that should just be updated in the JTable Class in COOK or at the core (libraries/joomla/table/table.php).

Any suggestions as to whether this should be a core 'hack' or just an update of cooks child class of jtable? To my mind, it seems that doing this in COOK's class would be safest due to it's portability and automatic inclusion with one's components.

Thanks,

Gez
//************EDIT******************//
This may work for some but on further reflection, I believe it would be best for me to add a UNIQU to the 2 FK fields. DO NOTE, this is actually for the platform, not the CMS.

OK, has anyone any experience of customising the AJAX combo to return only items not existing in the table?Many thanks,

Gez