Foreign key with more than one field as lable

Foreign key with more than one field as lable

3 years 9 months ago - 3 years 9 months ago #1215 by Nicolas Christener

I have two entities in my database:
* wine
* wine rating

In "wine" I have fields like:
* name (name of the wine)
* country
* year
* etc.

In "wine rating" I want to reference a wine and then specify how good this wine is. To do so, I tried to used the "foreign key" type of Component Creator. However in such a case, the select-box is limited to only *one* element of the table "wine" - e.g. "name". In this case I end up with a list that looks like this:
* Chateau Mouton Rothschild
* Chateau Mouton Rothschild
* Chateau Mouton Rothschild
* Gazin
* Petrus
* etc.

The first three element point to different bottles of the same wine but with e.g. different years. The user is unable to chose the right element in this case.

I therefore create a "SQL query" field having the following query:
SELECT `id` AS 'id', CONCAT_WS(' ', '', `name`, `year`) AS 'value' FROM `#__xxx_wine`

I then used "id" as "Key field" and "value" as "Value field". Make sure to name (alias) the "Key field" like the field you reference because otherwise you'll run in an error like this when opening the list in the admin area.

1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'key LIKE '10'' at line 1 SQL=SELECT `id` AS 'key', CONCAT_WS(' ', '', `name`, `year`) AS 'value' FROM `foo_xxx_wine` WHERE 1 HAVING key LIKE '10'

Now I have a list which looks like this:
* [ 1 ] Chateau Mouton Rothschild, 1984
* [ 2 ] Chateau Mouton Rothschild, 1988
* [ 3 ] Chateau Mouton Rothschild, 2001
* [ 4 ] Gazin, 1989
* [ 5 ] Petrus, 1999
* etc.

I hope this is helpful for some one :)

The following user(s) said Thank You: Andres Maeso

Please Log in or Create an account to join the conversation.

Time to create page: 0.185 seconds
Powered by Kunena Forum

We use cookies so that you can place orders and we can provide a better service. You can control the use of cookies at the individual browser level. If you reject cookies, you may still use our website, but your ability to use some features or areas of our website may be limited.