Independent Developer and Web Site Builders Network
Tutorial - JQuery Class
I will try explain the new JQuery Class from 1.6 ;)
The idea of JQuery Class is create dynamic SQL on execution time. Very simple to use.
// Create a new query object.
$query = new JQuery();
$query->select("id, title, alias");
$query->from('#__content');
echo $query; //return something like SELECT id, title, alias FROM #__content
more complex querys:
$query = new JQuery();
$query->select("c.id, c.title, c.alias");
$query->from('#__content as c');
$query->where('c.access = 1');
$query->order('c.created DESC');
echo $query; //return SELECT c.id, c.title, c.alias FOM #__content as c WHERE c.access = 1 ORDER c.created DESC
Now I will show a little secret. How tu use in DSL(Domain Specific Language).
If you think in future versions all users/developers use the same language to comunicate.
We can construct a Catalogs for construct Dyamic Querys.. I will show some examples that use Fluent Interface + DSLs + JQuery Class;
I will introduce another idea called DSLAPI(Domain Specific Language API) you can have DSLs from applications like Joomla!, Wordpress, Phpbb, Drupal, etc..
First we focus on Joomla! let go to contruct the first DSL class.
/**
* Catalog Content class
*
* @package DSL
* @since 1.0
*/
class DSL_Content
{
/**
* create a instance of JQuery
*
* @return Object JQuery
*/
protected function select() {
if( !$this->_query ){
$this->_query = new JQuery();
}
return $this->_query;
}
/**
* Initialize select with a list
*/
public function lists() {
$this->select()->select("
#__content.id,
#__content.title,
#__content.alias
");
$this->select()->from('#__content');
return $this;
}
/**
* Initialize select with count
*/
public function count() {
$this->select()->select(" COUNT(#__content.id) ");
$this->select()->from('#__content');
return $this;
}
/**
* Filter content from specific state
*
* @param $stateCondition Integer Sate of Content
*/
public function withState($stateCondition) {
$this->select()->where("#__content.state = {$stateCondition}");
return $this;
}
/**
* Filter content from specific parent id
*
* @param Integer Sate of Content
*/
public function withParendId($parendId = 0) {
$this->select()->where("#__content.parentid = {$parendId}");
return $this;
}
/**
* Filter content with specific title
*
* @param String title of content
*/
public function withTitle($title) {
$this->select()->where("#__content.title = '{$title}'");
return $this;
}
/**
* Filter content with specific title like
*
* @param String title of content
*/
public function withTitleLike($title) {
$this->select()->where("#__content.title LIKE '%{$title}%'");
return $this;
}
/**
* Order content by creation date
*
*/
public function latest() {
$this->select()->order("#__content.created DESC");
return $this;
}
/**
* Filter content by hits
*
* @param Integer Number of hits
*/
public function withHitsEqualThan($number) {
$this->select()->where("#__content.hits = {$number}");
return $this;
}
/**
* Filter content with hits more than number
*
* @param Integer Number of hits
*/
public function withHitsMoreThan($number) {
$this->select()->where("#__content.hits > {$number}");
return $this;
}
/**
* Filter content with hits less than number
*
* @param Integer Number of hits
*/
public function withHitsLessThan($number) {
$this->select()->where("#__content.hits < {$number}");
return $this;
}
/**
* Filter content in frontpage
*
* @param Integer Sate of Content
*/
public function inFrontpage() {
$this->select()->JOIN('JOIN',"#__content_frontpage ON (#__content.id = #__content_frontpage.content_id)");
return $this;
}
public function getRows(){
$db = JFactory::getDBO();
$db->setQuery( $this->select()->__toString() );
return $db->loadObjectList()
}
public function getResult(){
$db = JFactory::getDBO();
$db->setQuery( $this->select()->__toString() );
return $db->loadResult()
}
}
Now you can use something like:
$content = new DSL_Content();
$rowsContentFrontpage = $content->lists()->latest()->inFrontpage()->getRows();
//if you need count is just
$total = $content->count()->getResult();
Why I not re-config with same setings taht use on select? Because we just set before.
first code you get lists latest content in frontpage
secont get total.
Soon I will write more and more..
The idea of JQuery Class is create dynamic SQL on execution time. Very simple to use.
// Create a new query object.
$query = new JQuery();
$query->select("id, title, alias");
$query->from('#__content');
echo $query; //return something like SELECT id, title, alias FROM #__content
more complex querys:
$query = new JQuery();
$query->select("c.id, c.title, c.alias");
$query->from('#__content as c');
$query->where('c.access = 1');
$query->order('c.created DESC');
echo $query; //return SELECT c.id, c.title, c.alias FOM #__content as c WHERE c.access = 1 ORDER c.created DESC
Now I will show a little secret. How tu use in DSL(Domain Specific Language).
If you think in future versions all users/developers use the same language to comunicate.
We can construct a Catalogs for construct Dyamic Querys.. I will show some examples that use Fluent Interface + DSLs + JQuery Class;
I will introduce another idea called DSLAPI(Domain Specific Language API) you can have DSLs from applications like Joomla!, Wordpress, Phpbb, Drupal, etc..
First we focus on Joomla! let go to contruct the first DSL class.
/**
* Catalog Content class
*
* @package DSL
* @since 1.0
*/
class DSL_Content
{
/**
* create a instance of JQuery
*
* @return Object JQuery
*/
protected function select() {
if( !$this->_query ){
$this->_query = new JQuery();
}
return $this->_query;
}
/**
* Initialize select with a list
*/
public function lists() {
$this->select()->select("
#__content.id,
#__content.title,
#__content.alias
");
$this->select()->from('#__content');
return $this;
}
/**
* Initialize select with count
*/
public function count() {
$this->select()->select(" COUNT(#__content.id) ");
$this->select()->from('#__content');
return $this;
}
/**
* Filter content from specific state
*
* @param $stateCondition Integer Sate of Content
*/
public function withState($stateCondition) {
$this->select()->where("#__content.state = {$stateCondition}");
return $this;
}
/**
* Filter content from specific parent id
*
* @param Integer Sate of Content
*/
public function withParendId($parendId = 0) {
$this->select()->where("#__content.parentid = {$parendId}");
return $this;
}
/**
* Filter content with specific title
*
* @param String title of content
*/
public function withTitle($title) {
$this->select()->where("#__content.title = '{$title}'");
return $this;
}
/**
* Filter content with specific title like
*
* @param String title of content
*/
public function withTitleLike($title) {
$this->select()->where("#__content.title LIKE '%{$title}%'");
return $this;
}
/**
* Order content by creation date
*
*/
public function latest() {
$this->select()->order("#__content.created DESC");
return $this;
}
/**
* Filter content by hits
*
* @param Integer Number of hits
*/
public function withHitsEqualThan($number) {
$this->select()->where("#__content.hits = {$number}");
return $this;
}
/**
* Filter content with hits more than number
*
* @param Integer Number of hits
*/
public function withHitsMoreThan($number) {
$this->select()->where("#__content.hits > {$number}");
return $this;
}
/**
* Filter content with hits less than number
*
* @param Integer Number of hits
*/
public function withHitsLessThan($number) {
$this->select()->where("#__content.hits < {$number}");
return $this;
}
/**
* Filter content in frontpage
*
* @param Integer Sate of Content
*/
public function inFrontpage() {
$this->select()->JOIN('JOIN',"#__content_frontpage ON (#__content.id = #__content_frontpage.content_id)");
return $this;
}
public function getRows(){
$db = JFactory::getDBO();
$db->setQuery( $this->select()->__toString() );
return $db->loadObjectList()
}
public function getResult(){
$db = JFactory::getDBO();
$db->setQuery( $this->select()->__toString() );
return $db->loadResult()
}
}
Now you can use something like:
$content = new DSL_Content();
$rowsContentFrontpage = $content->lists()->latest()->inFrontpage()->getRows();
//if you need count is just
$total = $content->count()->getResult();
Why I not re-config with same setings taht use on select? Because we just set before.
first code you get lists latest content in frontpage
secont get total.
Soon I will write more and more..
-
Comment by Amy Stephen on September 28, 2009 at 6:34pm
- Rockstar! :) You're the best, dude. Keep doing what you do!
-
Comment by pollen8 on September 29, 2009 at 6:37am - excellent thanks! - I wish Joomla had chosen another name for the JQuery class - I keep thinking its the Javascrpt library! :o
-
Comment by Stian Didriksen on September 29, 2009 at 7:49am -
JDatabaseQuery would make much more sense ;)
Although this is an great improvement over writing raw queries, you can already write queries like this on Joomla! 1.5, using the Nooku Framework :)
$query = KFactory::tmp('lib.koowa.database.query')
->select(array('id, 'title', 'alias'))
->from('content');
Results in something like ''SELECT id, title, alias FROM #__content"
I'm just writing your query in koowa similar code. The query above is better done with KFactory::get('admin::com.content.model.content')->getList();
"Soon I will write more and more.." - With Koowa you write less and less... http://smashingdesign.net/koowa/
Great insight on the upcoming 1.6 apis, personally, I still think it's too much code that needs to be written :)
More code = more bugs.
Cheers mate! :)
-
Comment by Wilco Jansen on September 29, 2009 at 9:21am -
@Stian: although the Nooku code-base is widely available, it is not on public release and for that it has not much use to show it here as an example for writing less code. Agree though that less code is better, and I also agree that the name of the class it is not the most logical choice.
@Julio: thanks for sharing this. Question that I have is what this approach brings for benefits beside writing raw queries? As far as I understand you do not get database independency...that would be really interesting to use classes like these.
-
Comment by Júlio Pontes on September 29, 2009 at 9:37am - @Wilco, we can use Fluent Interface not just for building querys(I will show more idea based on this on next posts), but we can construct for example a Email DSL like: $email->to('wilco@joomla.org')->from('juliopfneto@gmail.com')->subject('What you think on apply Fluent Interface on Joomla?')->body('I waiting your aswer')->send(); I just make a language to send e-mail, I dont need know if we use phpmailer, mail(php) or anything else. I can write more examples if you need.
-
Comment by Wilco Jansen on September 29, 2009 at 9:41am
- Well I got that part, my specific question was related to writing database queries in relation to database independence. Currently such a class has no specific gain over the static queries
-
Comment by Júlio Pontes on September 29, 2009 at 9:46am
- @wilco, I have implemented here all fluent interface with Menu, Users, Banners, Extensions, Content, Categories from Joomla! 1.6 I will make post about explaining my Idea.
-
Comment by Ercan Özkaya on September 29, 2009 at 11:40am -
Note that you can chain method calls in JQuery too.
so this is possible:
$query = new JQuery;
echo $query->select('*')->from('#__content')->order('created');
Unfortunately you can't do stuff like echo (new JQuery)->select('*'); in PHP. So a helper method to return a new JQuery object is required for one line stuff.
@Wilco
Assume that you extend a model and just want to add additional filters or joins to the query. It needs either rewriting whole query or some weird search/replace action in the overridden method with static queries. But with JQuery, you just need to get the original query with a call to the parent and then you can customize it however you want.
-
Comment by Stian Didriksen on September 29, 2009 at 2:41pm
-
@chaining, that's one of the things you can do in koowa, using the factory class.
In koowa queries are built like this to allow you to change just the parts you need in a model.
By default, the select, from and ordering etc is predefined and overridable.
That way, if you for some reason need an extra WHERE clause, you only write that clause, nothing else.
@wilco there's no stable 1.0 release yet, but everyone can access their svn. The rest, like the mailing list and wiki you'll need to request code access first. If your argument for not showing how koowa does the same thing as the post is valid, how is J(Database)Query any different? Did I miss the Joomla! 1.6 release?
-
Comment by Wilco Jansen on September 29, 2009 at 3:06pm
-
lol @Stian, touche ;-) No, you did not miss 1.6, as far as I know it still is in pre-alpha stage, so enough time to implement it the way it should be done.
@Ercan: thanks for clearing that one up (knew that, but I mis-interpreted the post), would be interesting to see if it also would be possible to generate database independent code, but as far as I have seen there is no solution out there that does this job decently.
About
© 2010 Created by Robert Vining on Ning. Create a Ning Network!
You need to be a member of All Together, As A Whole to add comments!
Join All Together, As A Whole