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
- 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! :)
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
interactivetools.com
Re: [Dave] SELECT, JOIN and WHERE query questions
By msoda - December 19, 2008
Thanks for your comments.
I think that, for us, we will use CMS Builder for rapid client interface development and use some of the manual methods you suggest for displaying the public pages. We have nearly finished our first site so we have several query models to work with already.
We'll definitely be using CMS Builder again.
I think that, for us, we will use CMS Builder for rapid client interface development and use some of the manual methods you suggest for displaying the public pages. We have nearly finished our first site so we have several query models to work with already.
We'll definitely be using CMS Builder again.