Defining a custom find form with fields from more than one t

[Markus]

Hi Steve

it's me again

I have another problem to solve with my application. My app now has 11 tables and is pretty well normalised. Now I would like to build a custom find form (and its action) to do the following:

The form should have 9 fields out of 7 tables. There should be dynamic valuelists behind each form field (with the values which are in my database) and the possibility to combine these values for a search.

When leaving one or more fields blank the search should only combine the filled in fields (values) and ignore the not filled.

As a result page I want to have all the combined records displayed (in a table maybe) with once again fields from more than one table. Kind of a view I guess?

I think I could define a custom search form for one single table or just change the standard find form. However it puzzles me a bit that most of the .ini files are related to one special table.

How can I achieve to have such a combined search over more than one (which means 7) tables.

I have set the fulltext-search to hidden in my apps conf.ini because it would not get the matches I need exactly enough. Maybe I could modify this one?

Any idea if this is possible and how to start in the right way?

Kind regards

Markus

[shannah]

Hi Markus,

Will you be searching a union of these 7 tables or a join?

In a union, the rows from the respective tables constitute distinct rows in the result set.
In a join, each row will consist of fields from the 7 tables.

e.g. Union

Row 1: table1.Name table1.Phone table1.Address
Row 2: table2.Title table2.Author table2.PublishedDate
...
etc..

e.g. Join

Row 1: table1.Name table1.Phone table1.Address table2.Title table2.Author table2.PublishedDate ... etc..
Row 2: table1.Name table1.Phone table1.Address table2.Title .... etc..
etc ..
...

In order for a join to work the tables would need to have a column in common on which to perform the join (i.e. the records from the tables would have to be related somehow).

The answer to the above question makes a big difference when it comes to deciding how to implement it.

If you can provide me with some information about the tables and your reason for needing to search them together, I can give some better advice.

-Steve

[Markus]

Hi Markus,
Will you be searching a union of these 7 tables or a join?
In a union, the rows from the respective tables constitute distinct rows in the result set.
In a join, each row will consist of fields from the 7 tables.
e.g. Union
Row 1: table1.Name table1.Phone table1.Address
Row 2: table2.Title table2.Author table2.PublishedDate
...
etc..
e.g. Join
Row 1: table1.Name table1.Phone table1.Address table2.Title table2.Author table2.PublishedDate ... etc..
Row 2: table1.Name table1.Phone table1.Address table2.Title .... etc..
etc ..
...
In order for a join to work the tables would need to have a column in common on which to perform the join (i.e. the records from the tables would have to be related somehow).
The answer to the above question makes a big difference when it comes to deciding how to implement it.
If you can provide me with some information about the tables and your reason for needing to search them together, I can give some better advice.
-Steve

Hi Steve,

it should be a Join-Query I suppose. Looks like I want to have a result like you write for Join

The tables are like this:

Ê

betriebe
Feld	Typ	Null	Standard	Kommentare
bid	int(8)	Nein
owner	varchar(20)	Ja	NULL
USER_ID	int(11)	Ja	NULL
gruppe	varchar(10)	Ja	NULL
betrieb	varchar(60)	Ja	NULL
bundesland	varchar(40)	Ja	NULL
MORE FIELDS
event
Feld	Typ	Null	Standard	Kommentare
eid	int(8)	Nein
owner	varchar(20)	Ja	NULL
USER_ID	int(8)	Ja	NULL
gruppe	varchar(10)	Ja	NULL
L_GID	int(8)	Nein
L_TID	int(8)	Nein
U_ID	int(8)	Nein
MORE FIELDS
list_event_group
Feld	Typ	Null	Standard	Kommentare
l_gid	int(8)	Nein
type_group	varchar(30)	Ja	NULL
list_event_type
Feld	Typ	Null	Standard	Kommentare
l_tid	int(8)	Nein
L_GID	int(8)	Ja	NULL
type	varchar(256)	Ja	NULL
list_hersteller
Feld	Typ	Null	Standard	Kommentare
l_hid	int(8)	Nein
hersteller	varchar(30)	Nein
MORE FIELDS
list_system
Feld	Typ	Null	Standard	Kommentare
l_sid	int(8)	Nein
sys_name	varchar(30)	Ja	NULL
typ	enum('1-Tank', '2-Tank', 'Biodiesel')	Ja	NULL
list_vehicles
Feld	Typ	Null	Standard	Kommentare
l_vid	int(8)	Nein
L_HID	int(8)	Ja	NULL
serie	varchar(30)	Ja	NULL
fahrzeug_Typ	varchar(30)	Ja	NULL
MORE FIELDS
umruest
Feld	Typ	Null	Standard	Kommentare
uid	int(8)	Nein
owner	varchar(20)	Ja	NULL
USER_ID	int(8)	Ja	NULL
gruppe	varchar(10)	Ja	NULL
V_ID	int(8)	Nein
L_SID	int(8)	Nein
B_ID	int(8)	Nein
MORE FIELDS
umruestdetails
Feld	Typ	Null	Standard	Kommentare
did	int(8)	Nein
owner	varchar(30)	Ja	NULL
USER_ID	int(8)	Ja	NULL
gruppe	varchar(10)	Ja	NULL
U_ID	int(8)	Ja	NULL
MORE FIELDS
users
Feld	Typ	Null	Standard	Kommentare
UserID	int(8)	Nein
UserName	varchar(32)	Ja	NULL
Password	varchar(32)	Ja	NULL
owner	varchar(30)	Ja	NULL
gruppe	varchar(10)	Ja	NULL
Role	enum('NO ACCESS', 'READ ONLY', 'EDIT', 'DELETE', 'OWNER', 'USER', 'ADMIN')	Ja	READ ONLY
MORE FIELDS
vehicles
Feld	Typ	Null	Standard	Kommentare
vid	int(8)	Nein
owner	varchar(20)	Ja	NULL
USER_ID	int(8)	Ja	NULL
gruppe	varchar(10)	Ja	NULL
L_VID	int(8)	Ja	NULL
MORE FIELDS

The IDs in small letters are the auto increment primary keysÊof each table. The IDs in CAPITAL LETTERS are the foreign_keys which are connected to the primary keys of the other tables in my relationships.ini files, like betriebe.bit=umruest.B_ID and so on..

I need the fields

betrieb, bundesland from table betriebe,

type_group from table list_event_groups,

type from list_event_type,

hersteller from list_hersteller,

sys_name, typ from list_system

and serie, fahrzeug_Typ from list_vehicles

as select fields in my custom search form.

To me it looks like a pretty complicate action to do this. I am not very much into joins but I have done some.

To me it isÊalso veryÊinteresting to get all the needed files in DF configured in the right way to do so.

Thank you for your comments

Markus

Ê

Ê

[shannah]

Hi markus,

Sorry for the delay on this... this possible but after looking at your tables, I would need to know a little bit more in order to give some good advice. Are the table relationships 1 to 1, one to many, or many to many? If they are all 1 to 1, then the join will be a simple matter. If they are 1 to many or many to many it will be a little more complicated.

=Steve

[Markus]

Hi Steve,
as far as I know, I have only one to many relationships in my database but I'm not really sure how I would create one to one and many to many relationships. Could you maybe explain that first?
I forgot to tell you why I want to have such a custom Search Form. It's just because me and the guy I work for on this database think that it could be more user friendly than the standard search/find forms.
Here are all my current relations I have defined until now:
relationships.ini of table betriebe
[umruest]
__sql__="SELECT*FROM umruest WHERE B_ID='$bid'"

relationships.ini of table event
[umruest]
umruest.uid = "$U_ID"
[list_event_group]
list_event_group.l_gid = "$L_GID"
[list_event_type]
list_event_type.l_tid = "$L_TID"

relationships.ini of table list_event_type
[list_event_group]
list_event_group.l_gid = "$L_GID"

relationships.ini of table list_system
[umruest]
__sql__="SELECT*FROM umruest WHERE L_SID='$l_sid'"

relationships.ini of table list_vehicles
[list_hersteller]
__sql__="SELECT l_hid, hersteller FROM list_hersteller WHERE l_hid='$L_HID'"

[list_system]
__sql__="SELECT*FROM list_system WHERE l_sid='$L_SID'"

relationships.ini of table umruest
[vehicles]
__sql__="SELECT*FROM vehicles WHERE vid='$V_ID'"
[betriebe]
__sql__="SELECT*FROM betriebe WHERE bid='$B_ID'"

relationships.ini of table umruestdetails
[umruest]
__sql__="SELECT*FROM umruest WHERE uid='$U_ID'"

relationships.ini of table vehicles
[list_hersteller]
list_hersteller.l_hid = "$L_HID"
[list_vehicles]
list_vehicles.l_vid = "$L_VID"
Sorry for the different syntax I have to make it all the same I think but I wanted to try it out before so I used both methods.
Thank you for having a look at this desaster
Markus

[shannah]

OK.. i'm still having a little trouble wrapping my head around exactly how you need the results.

In general, anything is possible but I don't want to suggest a way unless I understand the output.

Perhaps if you can describe the result set that you are hoping to obtain in more detail.

e.g.

What columns need to be in the result set?

From your previous post it looks like:
betriebe.betrieb,
betriebe.bundesland
list_event_groups.type_group
list_event_type.type
list_hersteller.hersteller
list_system.sys_name
list_system.typ
list_vehicles.serie
list_vehicles.fahrzeug_Typ

So each row will have these columns. What exactly does a row in this result set represent?
As my German is non-existent, I had to resort to google translate to figure out what some of these columns meant. From what I can gather:
betrieb is the company name,
bundesland is the country or province,
type_group and type are categories relating to the company
hersteller is the manufacturer of a product
sys_name and typ ??? Not sure what these are
serie and fahrzeug_Typ are likely the make and model of vehicle.

So I'm not altogether sure how these all fit together. I.e. What is the primary entity described by this result set?
By 'primary entity' I mean, which of these things is the row actually pertaining to.

Any result set should have a primary entity.

For example, if we were doing a search for students, we might want to know which school the students attend, or where the student lives, what type of car the student drives, etc. All of these would be pulled from different tables, but the 'students' are the primary entities. Our result set should only contain one row for each student.

In your example, is the primary entity the company, the automobile, the manufacturer, or... something else.

One way to identify which is the primary entity is to answer the following question:
If the user clicks on one of the rows of this result set, where should the user be directed?

Once you have identified a primary entity, I would suggest we either:
a. Create a view in MySQL (if you're using MySQL 5) that encapsulates the information you need.
or
b. Create some calculated fields (using the __sql__ attribute in the fields.ini file) that fill out the primary entity's results to include these other useful columns.
or
c. Figure out the SQL query that is required to build the result set, and create a custom action centered around this query.

-Steve

[Markus]

Hi Steve,
sounds great. I am really sorry about the messy state of my app right now. It is because I started to have table and columnnames in english and later on add some in german. All in all your right with your guesses and translations on table and columnnames.
The sys_name and typ column in list_systemÊ belong to different machineparts of a certain vehicle. The database as a whole deals with agricultural machines like (now I have to make use of google translate ecause of my poor english) tractors, combines(???) which are re-equipped with certain parts to use bio-fuel instead of conventional fuel.
These machines/vehicles and their equipment together with parameters like the quantity of consumption, etc. should be entered into a database to have the possibility to compare them to each other and find out which re-equipments combined with which vehicles are the best in practical use. Hope you can understand my strange english.
The thing with the custom search form is that we want to get records out of the table umruest which are related to the searchable columns you mentioned above i.e.
betriebe.betrieb,

betriebe.bundesland

list_event_groups.type_group

list_event_type.type

list_hersteller.hersteller

list_system.sys_name

list_system.typ

list_vehicles.serie

list_vehicles.fahrzeug_Typ
That means, we don't need all of these columns in our result set (maybe some) and the records from table umruest which is our main table.
I think that is what you would call primary entity.
This table umruest has the following structure:
Ê `uid` int(8) NOT NULL auto_increment,
Ê `owner` varchar(20) collate latin1_german1_ci default NULL,
Ê `USER_ID` int(8) default NULL,
Ê `gruppe` varchar(10) collate latin1_german1_ci default NULL,
Ê `V_ID` int(8) NOT NULL,
Ê `L_SID` int(8) NOT NULL,
Ê `B_ID` int(8) NOT NULL,
Ê `D_ID` int(8) NOT NULL,
Ê `u_event_nr` int(3) default NULL,
Ê `u_datum` date default NULL,
Ê `bemerkung_u` text collate latin1_german1_ci,
Ê PRIMARY KEYÊ (`uid`)
As you can see it is related to at least the Tables vehicles (V_ID), list_system (L_SID), betriebe (B_ID), umruestdetails (D_ID) and these tables themselves are again related to other tables.
If it is possible maybe, to have all of the fields of table umruest plus list_hersteller.hersteller, betriebe.bundesland and list_vehicles.fahrzeug_Typ in the result set it will be perfect, I guess.
Thanks a lot
Markus

[shannah]

Ok. Your next step is to devise the SQL query that you would use to form this compound result set.

If will probably look something like:

select u.*, lh.hersteller, b.bundesland, lv.fahrzeug from umruest u left join list_hersteller lh on u.??? = lh.??? left join .... etc...

Once you have this query you can:
a) Create a custom action to display the results you want based on the parameters that you give in the URL.
or
b) Use the __sql__ parameter in the fields.ini file to customize the query that is used to gather records for the umruest table.

Then you can look at creating a search form to feed your custom action or your result set.

Best regards

Steve

[Markus]

Hi Steve,

To get the proper JOIN query will take me a while I think but I will try my best. When I got it I will ask you again how to proceed. Good to know, that there are different strategies here too. DF is really nice in its modular structure. More and more I have the feeling that I understand the most of what is necessary to meet my needs.

And it is fun to get the solutions step by step. A real learning effect to me.

Thank you again

Markus

[Markus]

Hi Steve,

I think, I have the query. It is:

"SELECT u.*, lh.hersteller, v.b_land, lv.fahrzeug_Typ FROM umruest u
LEFT JOIN vehicles v ON u.V_ID=v.vid
LEFT JOIN list_vehicles lv ON v.L_VID=lv.l_vid
LEFT JOIN list_hersteller lh ON lv.L_HID=lh.l_hid"

I had to change the betriebe.bundesland to vehicles.b_land because this is the field we need in our result.

How to proceed now? What is the difference between the both strategies you suggest? I think the first one sounds more like what I need. But not sure.

Thank you
Markus

[shannah]

You can now use this query as a custom query for the umruest table under 1 condition: There is a one-to-one and onto relationship between the rows of your query result and the original. That is every row of the umruest table should appear in the results of your query exactly once.

By eyeballing this query it looks like this is probably the case.

So you can add to the beginning of your tables/umruest/fields.ini file:
__sql__ = "SELECT u.*, lh.hersteller, v.b_land, lv.fahrzeug_Typ FROM umruest u
LEFT JOIN vehicles v ON u.V_ID=v.vid
LEFT JOIN list_vehicles lv ON v.L_VID=lv.l_vid
LEFT JOIN list_hersteller lh ON lv.L_HID=lh.l_hid"

(Note if you are using PHP 4, you will have to put this all on one line).

This will have the effect of appending the extra columns onto the umruest table. The values of these columns will now show up in list and details view. The current release of Dataface won't search these fields using the top-right search. Nor will they show up on the find form (The next release includes these features). However you can search them manually using the Dataface URL conventions.

e.g.

index.php?-table=umruest&hersteller=BMW
Will return a list of all records in the umruest table where the calculated hersteller column (from your custom query) is BMW.

You can use this to construct a custom FIND form to return the records that you want.
e.g.

Hersteller
...

Hope this helps a little.

-Steve

[Markus]

Hi Steve,
concerning the query on top of umruest/fields.ini. Works fantastic
Now it is so that the fields of the related tables, i.e. lh.hersteller, v.b_land, lv.fahrzeug_Typ got displayed at the end of my results table in details and list view.
I know, that I can assign the order=1, order=2 to my fields in fields ini. But this will not have effect on the fields of the query coming from related tables, right.
Do I have a chance to give these fields another order in the details and list view?
And is there a possibility to haveÊ a field lh.hersteller displayed in the new record view also?
Concerning the custom find form I had no chance to try it out til now but it looks pretty clear. If I have tried it, I'll tell you about my results.
Best regards
Markus

[shannah]

Try setting the order in the umruest/fields.ini file for the related fields.
e.g.

[hersteller]
order=2

[fahrzeug_Typ]
order=3

etc..

I can't remember if this works or not... if it doesn't let me know and I'll add it to the todo list.

Currently there is no option to add these fields to the new record form. You may be able to obtain a form with the fields you want by creating a dummy relationship and then using the new related records form for that relationship.. but that would be a bit of a hack.

-Steve

[Markus]

Hi Steve,

i tried it but the order= does not have any influence on the fields from the other tables. Can I change that myself somewhere in the source code of DF?

How would I create a dummy relationship? I would appreciate very much to have this functionality.

Kind regards

Markus

[shannah]

Hi Markus,

If the order= isn't working, then I'll have to look into this..

As far as the dummy relationship goes, just try making a relationship (on any table) such that the __sql__ attribute of the relationship is your SQL query. Then you can try adding new records to this relationship. This won't really help for editing though.

The next version of dataface has quite a few new features for being able to edit fields from multiple tables on a single form. It will be available in the next couple of months.

_Steve