Many to many to many relationship, and other newbie question

[syscrusher]

Greetings, all

First, i want to say that I am tremendously impressed by Xataface. Although I am a long-time user and developer of Open Source apps (contributor to the Drupal project), I have only been using Xataface for one day. Yet I have a working application RIGHT NOW that does the essentials of what I needed. This tool is a godsend. Thanks!

Although my app is functional, there are things I want to refine, hence this post.

QUESTION 1:

The trickiest thing I need to do is to define a third-order domain table. To be specific, I have the following for a nonprofit organization's officer roster:

groups: {group_id, group_name, ...other_columns}
offices: {office_id, office_title, ....other_columns}
people: {person_id, person_name, .....other_columns}

I currently relate these with a domain table of this form:

group_officers: {group_id, office_id, officer_id, effective_date}

I see from the wiki that doing this sort of thing with a two-way domain table is fairly easy, but the semantics of the INI files don't seem to allow for my three-way domain table.

What I have done in the interim is to simply define the group_officers table as if it were a primary table, then made select vocabularies to populate each of its columns (except the date, which is a simple date field).

is there a way to do this using Xataface relationship semantics?

QUESTION 2:

When the group_officers table changes (appointment of a new officer), I need to copy the *old* contents into a history table. I see how to use trigger functionality to do this, and I'm familiar with PEAR's database functions in PHP. My question is, should I use PEAR invocations directly in my delegated class, or is it more "xataface-correct" to use a Xataface API call to execute my queries and let Xataface pass the query to PEAR?

Again, thanks to the developers for an amazingly useful piece of software. it is *exactly* what I needed for this application: I don't care about being able to highly-customize the look of the pages (although I know Xataface allows this), but I needed it to be functional quickly. I couldn't ask for more quickly than "one afternoon"!

Kind regards,

Scott

[shannah]

For question 1:

There are a few ways to skin this cat. Your step of adding drop-down lists for each of group_id, officer_id, and office_id is a good place to start. (though if the tables get too big you might want to switch to the lookup widget for each of these instead of the select widget as it doesn't require every record to be loaded as it would by defining vocabularies on these tables).

You could easily add relationships from the source tables to this domain/join table of yours in the normal way. E.g. in your groups table, you might have the "officers" relationship:

Code: Select all

[officers]
    group_officers.group_id="$group_id"


In the people table you might have the "groups" relationship:

Code: Select all

[groups]
    group_officers.officer_id="$person_id"


etc...

If you use the lookup widget for your id fields instead of the select widget, you get the benefit of being able to add new related records a little more easily also.

For Question 2:

It is up to you whether you use Xataface APIs or not. Quite often I'll jump straight into a mysql_query() call inside delegate classes for convenience. If you want to ensure that updates are made through Xataface (so that beforeSave/Insert/Update etc... triggers are properly called), then you might use the Dataface_Record save() method instead.

Also for this functionality, have you looked at the Xataface history feature?
http://xataface.com/documentation/how-to/history-howto

-Steve

[syscrusher]

For question 1:
There are a few ways to skin this cat. Your step of adding drop-down lists for each of group_id, officer_id, and office_id is a good place to start. (though if the tables get too big you might want to switch to the lookup widget for each of these instead of the select widget as it doesn't require every record to be loaded as it would by defining vocabularies on these tables).

Good point, and thanks for the refresh. I *knew* I remembered seeing a better widget than select somewhere in the docs, when I was first skimming them to see if Xataface was what I was looking for. Then when I was actually building the app, I couldn't remember where I had seen that in the docs, nor could I remember the name of the feature for a seacch. The group is feasible to list as a select, because there are only several dozen of those. But the people table contains over 500 rows, so lookup will be much better there.

You could easily add relationships from the source tables to this domain/join table of yours in the normal way. E.g. in your groups table, you might have the "officers" relationship:

Yes, I thought about that, but wasn't sure from the docs whether Xataface would gracefully handle the fact that any one of those defined relationships would define only two out of three of the key segments, rather than all of them. I'll give that a try and see how it behaves in use.

If you use the lookup widget for your id fields instead of the select widget, you get the benefit of being able to add new related records a little more easily also.

For Question 2:

It is up to you whether you use Xataface APIs or not. Quite often I'll jump straight into a mysql_query() call inside delegate classes for convenience. If you want to ensure that updates are made through Xataface (so that beforeSave/Insert/Update etc... triggers are properly called), then you might use the Dataface_Record save() method instead.

That sounds like a pretty good reason to stay within the API. Thanks.

Also for this functionality, have you looked at the Xataface history feature?
http://xataface.com/documentation/how-to/history-howto

No, I wasn't aware there was a built-in history feature. I'll check that out. I've been managing this database for almost a decade with SQL commands, which are very comfortable to me, but I'm now looking to delegate some of the data entry to someone less technically inclined. So the existing history mechanism has worked fine, but I'm not married to the current schema for that. The built-in one is worth a look, certainly.

Thanks for the great reply.

Scott

[syscrusher]

The history function was exactly what I needed, and it has allowed me to completely eliminate something that I was doing manually. This will save me a ton of work while providing better functionality.

The relationships settings you suggested also worked perfectly for me.

My one remaining question: I enabled a "lookup" field type, but it seems to offer only the first field other than the ID column as a lookup option. In my case, the table schema for people is something like this:

id int auto_increment
lname varchar(100)
fname varchar(100

et cetera. What I am seeing is that it pulls "lname" only as the lookup, which of course means you can't pick from the records of anyone whose last name is shared by more than one person.

Everything else works great, and that history feature is truly awesome.

I continue to be really impressed by Xataface and what I'm able to accomplish with very minimal effort at my end.

One final question: I looked on the web site for a donations link, but found none. Is there a foundation or other group to which I could donate financially to support this excellent Open Source project?

Kind regards,

Scott

[shannah]

I enabled a "lookup" field type, but it seems to offer only the first field other than the ID column as a lookup option.

The lookup field will show whatever the record title is. If you don't explicitly define the getTitle() method in the delegate class, Xataface will just make a best guess at the title (and the guess is basically the first varchar field that it finds). This page has an example of the getTitle() method:
http://www.xataface.com/documentation/t ... te_classes

One final question: I looked on the web site for a donations link, but found none. Is there a foundation or other group to which I could donate financially to support this excellent Open Source project?

We currently aren't set up to take monetary donations. Taking donations is a big responsibility as it would require setting up financial systems to be able to provide transparency and accountability. The best way to donate to the project is simply to spread the word, add to documentation where you can (you can log into the wiki with your forum username and password, and most pages have comments at the bottom so you can add your comments), blog about it, etc...

Glad to hear you're enjoying Xataface.

-Steve