Hi,
I am needing to write an enterprise level Joomla component and I am seriously considering a long term subscription with COOK for this.

I need to have an ability to write custom SQL for form field just like JFormFieldSQL in Joomla. I know there are foreign keys function in COOK, but I don't think this will be ideal for my application since I need to limit the choices based on a custom SQL query.

I think this ability belongs in the "TABLES -> Data Types" area.

Is something that will be available in the 2.0 version that has been muted?

Thanks,
Dan

Hi, and welcome to the community !

Cook V2.0 implements JForms, so you will be able to use that native type just by changing one word.

For the moment this not implemented in Cook, even in V2.0.
Fields collection will be considerably improved after V2.0.

Also because yet in V2.0, Cook override every jform field in order to reuse common functionalities (missing in native). So if you use a native SQL field type, you'll should to override it in the same way Cook does (JS validators, and ACLS accesses)

Once Joomla implements better the jform layer, I will be able to add much more fields. (I do not want to add too much overrides files, it is not clear and too much)

Well, it is a little bit of chinese, because you do not see the source of V2.0 for the moment...

In definitive, in 2.0 Cook will not do everything, but the coding way is much easier, and much native, much standard.

Have fun

Thanks for your reply admin.

So if I understand what you are saying, there is no method in v2.0 COOK for me to do custom SQL right?

If I make the field FK type first for testing and then change to SQL type manually after download right?

Dan

Every feature should be available in sandbox for testing

SQL on the fly is too dangerous in sandbox. (should require some additional securities and limitations for sandbox)
So, it is not a priority now.
But since the first V1.0, there is a beginning of a 'SQL' field type (not published) and never used but the idea was there.

TODO :

Well, can you please tell me how you want it working ?

- SQL strings stored in list ? or SQL research on the fly ?


Well, anyway...
Look at your model, it prepares the datas for the view.
All you have to do is :
- send (or read) the SQL custom to the model, instead of the classic query.
- get the columns names of the query result
- return both structure and datas

In your view.html.php
- Init the model
- Call that special function and get structure and datas
- send the datas the the template (already there)
- send the structure in the same way.

In your template :
- Enumerate the columns and construct the grid on the fly
- Enumerate your datas, and switch JDom calls depending of the field type (structure of the column)

Good luck to code your custom PhpMyAdmin

Can I ask you a copy of your future app ?
I am serious, it can be really usefull for debugging, and why not integrated in Cook (as an option) ?

Hi admin,
The SQL function I need is really basic...

SELECT 'field' FROM 'table1' WHERE 'table2.field' = 'table1.field' (or 'value') AND .... etc, etc

This will help to limit the choices in the Enumeration (Select) List in the item form to a limited set of values (depending on values in other table) that you only want in certain conditions.

I don't require complicated SQL for my application at ths point so I think a dynamic SQL Query Builder integrated in COOK would be a good solution for me and your other customer and if you limit what the SQL Query Builder can do, I am sure you can keep your sandbox safe and secure (Because you only want users to execute basic SQL queries and restrict custom SQL queries). What I mean is a little like MySQL Query Builder http://vimeo.com/27530599 but perhaps much more simple for first integration into COOK and then extend it like the powerful MySQL Query Builder.

I would like to email you a screenshot of how I think it should work in COOK but I do not know how to do that with these forums.

Please contact me by email and I will send my UI through to you.

Whereas I dont require complicated SQL in my dropdown fields, I would like the option to designate a field from the associated foreign table as a filter.

Example, I have a table called CONTACTS which I reuse several times. Some of the contacts are classed as SYSTEM TYPES, ie they are defined at installation and never change.

I would like to specify in my FOREIGN datatype in the form, WHERE id = whatever, or WHERE NAME LIKE etc.

I can obviously work this in the code, but being lazy is always a nice option XD