Master/Detail with two level deep tables

[darren]

Hello all,

I wish to use dadabik for my mysql server front end, however, in the database I have setup I have the current database structure:

Mail list (which contains all people we mail to)

Adoptions (some people adopt cats)

Cats (cats those people have adopted)

The mail list table has a foreign key in adoptions to link the adoptions and the Mail list

The Cats table has a foreign key in the Adoptions Table to link cats to their adoption numbers (a unique number)

What I am trying to do is show the cats and their adoption numbers on the master/detail section of a person on the mail list. However, the master/detail only goes one level deep so I can only show the adoption number but I cannot display the cat associated as the link between the mail list and the cats table is through the adoption table.

I would also want to be able to add a field to the cats table and the adoption table in one create because as of now when I want to add data for an adoption and a cat I have to go to each table and enter in the data individually.

Suggestions Appreciated!

Enviroment:

PHP Version: 7.1.11-1+ubuntu16.04.1+deb.sury.org+1

mysql version: 5.7.20-0ubuntu0.16.04.1

Web server: Apache/2.4.18 (Ubuntu)

 

[eugenio]

Hello,
you didn't mention your DaDaBIK version, I assume you are using 8.1 Enterprise.

Question: I guess an adoption can have several cats linked?

What you want to see in the edit form of a person (mail list) is:
- the various adoptions he/she is involved in (first table)
- all the cats he/she adopted (second table)
am I right?

If you want to show both adoptions and cats you must have a reference to id_mail also in the table cats.

I can see two solutions:

1) you create a VIEW in SQL that combines adoption + cats involved in each adoption and you use only that view in the master/details form

2) you add redundant field id_person in the cats table; this field could also be filled automatically by an after update HOOK when you update a cat record, linking the cat to an adoption.

Best,

 

[darren]

Thank you for the reply,

My mistake for not including the dadabik version, it is 8.1 pro with a chrome browser

To answer your question about the adoption having multiple cats, it is actually the reverse, one cat can have multiple adoptions, a one (cats) to many (adoptions)

As far a views go, this would world the only issue I foresee is that inserts create problems for MySQL with views containing joins as the statement needed to make a proper insert would have to be segmented as in

[pre]
INSERT INTO cats
cats.foo value 1
cats.bar value 2
INSERT INTO adoptions
adoptions.biz value 1
adoptions.baz value 2
[/pre]


Which I do not think dadabik dynamically supports nor any other database front end for that matter. Is there a way to manually edit the SQL statements for inserts?

As far as the redundancy idea goes, it is not possible because the table cats is connected to the mail list in another way as there is also a table for relinquishing cats to us that is connected in the same way as the adoptions. basically when we receive cats, people get a release number and when we "sell" cats they get an adoption number.

 

[eugenio]

I am lost So each person can have multiple adoptions and each adoption has a single cat?
If this is the case, you can at least use a VIEW to show, for each person, all the related info (adoptions and cats). You cannot use a VIEW to insert at the same times records in two tables, as you correctly said.

Regarding the second solution, hooks are available only in DaDaBIK Enterprise/Platinum; it's not 100% clear to me how your tables are connected but using a hook you can add all the PHP/SQL logic you want and you can execute all the queries you want after an update or an insert operation so probably you could solve your issue.

Best,

 

[darren]

a picture is worth a thousand words as they say...

I may have found a way around this as the reader friendly autocomplete may solve my issue. As i can start at the adoption and find the associated cat and person from there.

 

[darren]

as the solution i found would work, the problem I am facing now is rather stressing. When I try to display a master/detail by a field that is not the primary key the form will only go by the primary key. for example:

cats table has:
ID = 1
unique number = 223
adoption table:
ID = 2
foreign key to CATS table unique number = 223

when I try to have dadabik display master/detail based on whether the cats has a matching foreign key in adoption it will not display.

To test whether or not its inserting the primary key from the table cats, I would try to insert a new row from cats into the adoption table and I would get an error saying that there is no unique number '1' (the ID field number from the cats table) in the cats table and that the insert violates foreign key integrity rules.

the insert statement should be like:

[pre]
INSERT INTO TABLE adoption_num
'adoption_num.unique_number-from-cats-table' value '223'
[/pre]

however the actual statement is:

[pre]
INSERT INTO TABLE adoption_num
'adoption_num.unique_number-from-cats-table' value '1'
[/pre]

its getting the 1 value from the primary key of the cats table when it should be using the unique number

 

[eugenio]

I am sorry but the problem is not clear to me (and also the picture is not very clear, I mean the quality): in the first post we were talking about a master/details view between people and adoption + people and cats; now I think you are discussing another master/details view, between adoption and cats?

If you have a public accessible version of your application and can provide the exact steps to reproduce the issue it would be useful.

Best,

 

[darren]

I found a workaround to make this work

Thank you for the help though