01
Mar

Using HQL In Coldfusion Orm

When using Coldfusion ORM you will often need to write more complex query statements. In cases such as these, ormExecuteQuery and new Query() come into play.

In this example, you will see that we can create a hql statement that gets only published art from a particular artist. You will notice that there isnt any direct call to the artist table. This is because this hql query calls the persistent entity and not the database. The persistent entity already knows about the relationship of art to artists so its able to perform this task easily.

art = ormExecuteQuery('FROM art
                                    WHERE isPublished = :isPublished
                                    AND artist.artistID = :artistID',
                                    {isPublished=1, artistID=123}
                                    );
 

At times you may need to do things like cast a timestamp to a date for comparion queries. For example, if you wanted to see how many times a particular ip address has posted a comment on your site you may cast the property from a timestamp to a date datatype:

prevComment = ormExecuteQuery("FROM comment
                                                   WHERE commentIP = :ipaddress
                                                   AND date(date_created) = :date",
                                                   {ipaddress=variables.ipaddress
                                                   date=DATEFORMAT(now(), 'short')
                                                  });

One thing to remember is that you are executing a query on the entity not the database per say. If you wanted to look up how many comments per post you would pass in the post id and reference the post entity. Remember the comment entity already understands its relationship to the post entity so you can reference it in your hql query. If you try to reference the database foriegn key you will produce an error.

postComment = ormExecuteQuery("FROM comment
                                                   WHERE post.post_id = :postID",
                                                   {postID=variables.postID});

 

You may also write hql using cfquery. In this example we want to select all the posts from year 2015. To do this we must set the db type to "hql".

q = new Query();
q.setDBType( "hql" );
q.setSQL( "FROM post
           WHERE year(created) = :passedYear");
q.addParam(name="passedYear", value='2015');
posts = q.execute().getResult();

There are also times when you will need to pass conditional logic into your query.

if(isDefined('variables.filterYear')){
    sqlQueryString = 'year(created) = :filterYear';
}
else {
    sqlQueryString = '0 = 0';
};
q = new Query();
q.setDBType( "hql" );
q.setSQL( "FROM post
           WHERE #sqlQueryString#");
if(isDefined('variables.filterYear')){
    q.addParam(name="filterYear", value='#variables.filterYear#', );
}
posts = q.execute().getResult();

Tags: ORM; Coldfusion