SELECT, JOIN and WHERE query questions

3 posts by 2 authors in: Forums > CMS Builder
Last Post: December 19, 2008   (RSS)

By msoda - December 16, 2008

I have just started using CMS Builder to try and speed up the development of CMS development for clients. On the whole it seems impressive and we have no problem in setting up the admin area in a way that our clients will easily be able to use. However, the generated code seems to be more limited (I'm new so I may be wrong). I have some general questions that I couldn't find any answers for on the thread...

- Is it possible to SELECT limited columns from the DB instead of all ( * )?

- Typically we would want to use foreign keys and JOIN tables when setting a schema. Is it possible to use foreign keys without relying on the "editors" to manage it as new content is added?

- Can you run WHERE clauses on multiple tables (in effect creating a JOIN). Specifically we don't want to pull all data from the cms_uploads table when we just want images with 'order = "1"'.

- Is there any reason why bespoke code shouldn't be written for client-facing pages if the generated code doesn't do the job?

I realize these are general questions but hopefully they should give us a steer.

Thanks in advance.
Joe

Re: [msoda] SELECT, JOIN and WHERE query questions

By Dave - December 17, 2008

Joe, Welcome to the CMS Builder forum! :)

As far as the generated code goes, you're right. We've made certain trade offs to make it accessible to non-programmers.

I myself will custom write MySQL queries when it makes sense to do so, such as when I need a lot of optimizations, or complex queries or joins. And lots of other times I'll just use the generated code because it's so fast and easy to do. :)

To answer your questions, there's no way to select limited columns, and very limited join capabilities with getRecords(). I'm adding some more advanced left join features for the next version, but if you're familiar with hand coding MySQL you may still find that easier.

The challenge for us is making a lot of the power of MySQL accessable to non-programmers without having to teach them MySQL. :)

One last note, you can see the queries CMS Builder generates by adding this option:
'debugSql' => true,

Also, there's a list of options for the getRecords() functions in the comments at the top of /lib/viewer_functions.php

And, you can hard code custom column types in the schema files with 'customColumnType'. See /data/schema/uploads.ini.php for an example.

Hope that helps, let me know if you have any other questions or feedback! :)
Dave Edis - Senior Developer
interactivetools.com