Multiple Foreign Keys / Select Single

[Ueli Preisig]

I've a problem and couldn't find a solution for it, maybe you can help:

Table 1 'rezept'
with primary_key 'id'
and fields 'flav1', 'flav2', 'flav3', ... 'flav7'
and several other fields.

Table 2 'sortiment'
with primary_key 'id'
and several other fields (sort_name_ge, sort_name_fr).

Fields 'flav1', 'flav2', ... 'flav7' are foreign keys to table 'sortiment', so I can choose up to 7 flavours ('flav') that are contained in a recipie ('rezept').

No problem to insert new records, this works perfect, but displaying the list and submitting a search doesn't work, because it seems not possible to get all the results in one single SQL-query.

Here is the SQL-Statement that get's executed:

SELECT
`rezept`.`id`,
`rezept`.`rez_name`,
`rezept`.`flav1`,
`rezept`.`flav2`,
`rezept`.`flav3`,
`rezept`.`flav4`,
`rezept`.`flav5`,
`rezept`.`flav6`,
`rezept`.`flav7`,
`rezept`.`ingredients`, `rezept`.`preparation`,
`rezept`.`category`,
`rezept`.`saison`,
`rezept`.`month`,
`rezept`.`language`,
`sortiment`.`sort_name_ge`,
`sortiment`.`sort_name_fr`,
`sortiment`.`sort_name_ge`,
`sortiment`.`sort_name_fr`,
`sortiment`.`sort_name_ge`,
`sortiment`.`sort_name_fr`,
`sortiment`.`sort_name_ge`,
`sortiment`.`sort_name_fr`,
`sortiment`.`sort_name_ge`,
`sortiment`.`sort_name_fr`,
`sortiment`.`sort_name_ge`,
`sortiment`.`sort_name_fr`,
`sortiment`.`sort_name_ge`,
`sortiment`.`sort_name_fr`,
`drink_kategorie`.`drink_name_ge`,
`drink_kategorie`.`drink_name_fr`,
`saison`.`saison_name_ge`,
`saison`.`saison_name_fr`
FROM rezept
LEFT JOIN `sortiment` ON `rezept`.`flav1` = `sortiment`.`id`
LEFT JOIN `sortiment` ON `rezept`.`flav2` = `sortiment`.`id`
LEFT JOIN `sortiment` ON `rezept`.`flav3` = `sortiment`.`id`
LEFT JOIN `sortiment` ON `rezept`.`flav4` = `sortiment`.`id`
LEFT JOIN `sortiment` ON `rezept`.`flav5` = `sortiment`.`id`
LEFT JOIN `sortiment` ON `rezept`.`flav6` = `sortiment`.`id`
LEFT JOIN `sortiment` ON `rezept`.`flav7` = `sortiment`.`id`
LEFT JOIN `drink_kategorie` ON `rezept`.`category` = `drink_kategorie`.`id`
LEFT JOIN `saison` ON `rezept`.`saison` = `saison`.`id`

Producing the following Error:

MySQL server said: Not unique table/alias: 'sortiment'

Is there a workaround to fix this problem?

Of course, I could not use 'Select Single' to choose the flavours ('flav') and just enter the corresponding flavour id manually, but this isn't very user-friendly.

Any help would be great. I'm sure I'm not the only one with this problem.

Thx in advance!

 

[Eugenio]

Ueli Preisig wrote:

> I've a problem and couldn't find a solution for it, maybe you
> can help:
>
> Table 1 'rezept'
> with primary_key 'id'
> and fields 'flav1', 'flav2', 'flav3', ... 'flav7'
> and several other fields.
>
> Table 2 'sortiment'
> with primary_key 'id'
> and several other fields (sort_name_ge, sort_name_fr).
>
> Fields 'flav1', 'flav2', ... 'flav7' are foreign keys to
> table 'sortiment', so I can choose up to 7 flavours ('flav')
> that are contained in a recipie ('rezept').

[....]

Please report how you have set primary/foreign key fields/table in the admin interface.



(Latest version of DaDaBIK when this message was posted: 3.0 beta)

 

[Ueli Preisig]

I've uploaded the schema of the DB where you see how the tables are linked together:
http://www.implikat.com/db_schema.gif

In Table rezept, flav1.... flav7 get their values from Table sortiment.

As long as I only set one foreign key, everything is fine. but not with multiple keys.

Example (equals attributes flav1 .. flav7:

Primary key field: id
Primary key table: sortiment
Linked fields: sort_name_ge~sort_name_fr

Thx for your help!

PS: E-mail replies to threads in this forum don't seem to work properly.

 

[Eugenio]

Ueli Preisig wrote:

> I've uploaded the schema of the DB where you see how the
> tables are linked together:
> http://www.implikat.com/db_schema.gif

[....]

Unfortunately this is a bug of v3.0 beta, see here:
http://www.dadabik.org/forum/read.php?f=1&i=2374&t=2374

> PS: E-mail replies to threads in this forum don't seem to
> work properly.

Probably it was a temporary problem, does it work correctly now?



(Latest version of DaDaBIK when this message was posted: 3.0 beta)