Table Joins

Is it possible to do the equivalent of a SQL left join? I have a profile page where a user's 'state' is listed as an ID for another table and I need to show the actual value of the 'state' instead of the index reference.

Thanks.

Hi tabriszero,

Unfortunately this feature is not available yet, I'm improving the component currently and will look into this, the problem is that column names may conflict with each other when JOINING so this needs some care.

Regards

Max

If I understand this correctly, then this would allow more of a relational DB structure, which is what we need as well. It seems that with ChronoForms you can pull in fields into one form from multiple tables through some PHP coding and then save into a table. But when you want to display that data from the table that references other tables (i.e. Joins), that cannot be done through ChronoConnectivity, right? We are exploring these products to use on some projects, and this is one of my main issues. Do you know of any other Joomla components that might allow you to do this?
Thanks,
Daniel

Hi Daniel,

yes, Connectivity can not connect to more than 1 table, I have no idea about other extensions may does this, but may be try to check http://extensions.joomla.org/component/option,com_mtree/task,viewlink/link_id,326/Itemid,35/

I'm not sure, never tried it!

Regards
Max

There's Fabrik - but it seems to be exceedingly resource intensive because it is a full-blown application builder.

Unfortunately, joins are pretty important so the database can be close to third normal form and data doesn't have to be duplicated across multiple tables.

I'm assuming that there's still no word on when joins might be available in ChronoForms and ChronoEngine.

ChronoForms already has a huge advantage over some of the other form builders. Facile / Breezing forms doesn't create individual tables like ChronoForms. It stores the data with meta-data as rows in a table. So if you have a form with 5 fields in it, each form record will occupy 5 rows in the data table and will be mixed in with the data from the other forms. This makes it impossible to do much with the data unless you want to do a lot of coding.

Hi hostricity,

I'm just wondering if this could be done with a temporary table. If there was a box to set one up then ChronoConenctivity worked on that? I don't know enough about MySQL to know if this is a good idea or not :-(

Bob

I ran into this problem a couple of weeks ago, and had the idea of creating a VIEW in PHPAdmin. So far so good, as it had all the fields I wanted to display in my table and the VIEW shows up as a selectable entity. However, when I went to save it I got a warning from ChronoConnectivity that there was no Primary Key defined. MySQL VIEWS do not have primary keys, only Base Tables do. Is there a way to get around the Primary Key issue (so that it doesn't look for one)? If so, I'm home free (or at least I think I will be).

Thanks.

Hi hostricity,

I'm just wondering if this could be done with a temporary table. If there was a box to set one up then ChronoConenctivity worked on that? I don't know enough about MySQL to know if this is a good idea or not :-(

Bob

Yes and no. If all you want to do is view the table, that would work fine, as would a view.

The real issue is this: If you use things like foreign-key and cascading updates/deletes, to drive the CRUD generation, you are able to generate CRUD that has a lot less code in it. You are taking advantage of the database definition to enforce update rules and that eliminates a lot of logic and looping in the PHP code.

Of course, to be fair, ChronoForms is a rather nice form generator and is great for lots of stuff. ChronoForms fills an important need. In fact, for most people, ChronoForms fits the bill perfectly.

But there are those who need a full-blown application generator. ChronoForms isn't really an application generator, which is something Joomla desperately needs. As it is now, the registration system, contacts, and most extensions, duplicate profile information (name, address, email address, etc.) in each of the different extensions which creates a maintenance nightmare if you want to use Joomla as a publishing system for business applications.

Geoff

Hi hostricity,

I'm just wondering if this could be done with a temporary table. If there was a box to set one up then ChronoConenctivity worked on that? I don't know enough about MySQL to know if this is a good idea or not :-(

Bob

A Different answer from my previous response:

I don't want to see ChronoForms / ChronoConnectivity become a full-blown application / code generator. ChronoForms serves an important function and does it very well. I tried BreezingForms, which is a nice tool except for one thing: It stores the form data in its own tables in a way that you must use BreezingForms to display the data, or write a whole lot of code to access it.

With ChronoForms, you can write to a db table that is accessible in normal fashion by ChronoConnectivity or any other database tools - within or outside of Joomla.

A very simple join feature that would allow storage of visitor profile info in one table and transaction specific information in joined tables would be nice. In fact, I wouldn't be opposed to the idea of "hard-coding" a contact info table that could be joined to any other table created through ChronoForms. (The contact info table would be used for single instance data and it could be joined to any table which could contain multiple rows for the contact, and the contact info table could still be customized by adding columns to it. The hard-coded part would be to allow CRUD on it joined to other tables.) You could also allow joins on read-only reference tables such as state, province, country, currency, telephone country code, etc.

The point of this is that: ChronoForms is excellent for at least 90% of what people want to do. Turning it into a full-blown code-generator or application-generator can't be done without increasing the complexity to the point that it would no longer be the perfect tool for more than 90% of the typical Joomla user's needs.

I'm a former college professor in computer science and will tell you that a well-defined, task-specific application which is really easy to use - even if it doesn't adhere strictly to database design rules - can be more useful then an application that adheres religiously to proper design rules. The problem is that applications done this way are limited in the ability to expand them and add new functionality.

I claim that ChronoForms / ChronoConnectivity is a niche application that fits most Joomla user's needs and that by storing the data in native mySQL tables, it can already be expanded by using other tools. But, ChronoForms / ChronoConnectivity should do exactly what you are doing with it: Strive to be the best tool within a specific, well-defined, range of function. Don't ruin it by trying to turn it into a full-blown code / application generator. A limited table join and CRUD on joined tables would be great -- But, NOT a generalized CRUD on joined tables. To do that, I think the user design process would have to be reversed: Design and implement the database with foreign-key, cascade, and other constraints and then generate the forms, CRUD, and data views from there. THAT requires a level of database design knowledge that would destroy the usability of ChronoForms / ChronoConnect.

I was able to figure out how to use ChronoForms and ChronoConnect simply by looking for features that your website said were available. I didn't need any tutorials, or documentation (although, that is always important to have).

So, my message to you is this: Expand the functionality ONLY to the extent that it makes it more useful to most Joomla users and without making it more complicated. Limited join functionality along the lines of what I suggested would be useful -- but only if it can be done while maintaining the simplicity and ease of use ChronoForms and ChronoConnectivity now have.

Or, put another way: You have a truly great tool. Don't screw it up by extending it to meet every possible use. Stick to what would be useful to 90% of your users in a cool form builder with sophisticated data view and update capabilities.

Geoff

I ran into this problem a couple of weeks ago, and had the idea of creating a VIEW in PHPAdmin. So far so good, as it had all the fields I wanted to display in my table and the VIEW shows up as a selectable entity. However, when I went to save it I got a warning from ChronoConnectivity that there was no Primary Key defined. MySQL VIEWS do not have primary keys, only Base Tables do. Is there a way to get around the Primary Key issue (so that it doesn't look for one)? If so, I'm home free (or at least I think I will be).

Thanks.

A primary key shouldn't be needed if all you want to do is display data, search your joined view, etc. The requirements to do Create, Update, and Delete on a view are much more stringent. That means ChronoConnectivity shouldn't require a primary key for everything it does. However, I have no idea how deeply that requirement is embedded in the ChronoConnectivity code or how difficult it would be to change that requirement.

Geoff

Hi both,

I think that the ChronoConnectivity error is only triggered on Save (as Mark says). So you could use either approach for viewing a table but need something else to edit. You can make Edit links that go back to a ChronoForms form if you wish and that could link directly to one underlying table. Doesn't resolve the full CRUD question but allows for the display of more complex data than you can edit.

Bob

I have successfully created and used a VIEW to access multiple tables in Chronoconnectivity. Instructions are here: http://chronoengine.com/forums/index.php?option=com_chronoforums&cont=posts&f=12&t=13169&p=61465#p61465

Neil