Using Quick ORM return result as a cf query

Communities Quick ORM
1

Here’s the code:

How do you make:

var query = db.where(‘cancelled’, ‘0’).orderByAsc(‘companyName’).get();

return the result as cf query (like queryExecute)?

The first method in the code returns the desired query format, how do you do the same when using the property “db” as in the code below???

component accessors=“true” singleton {

// Dependency Injection
property name="db" inject="quickService:clientsModel";
property name="wirebox" inject="wirebox";

function getClients(){
    try{

        var qbRaw = wirebox.getInstance("QueryBuilder@qb");

        var query = qbRaw.setReturnFormat('query').select( [ "id", "companyname" ] ).from( "clients" ).where( "cancelled", 0 ).orderByAsc('companyName').get(  options = { datasource: "x" } );

        var query = db.where('cancelled', '0').orderByAsc('companyName').get();
        
        return query;

    }catch (any e) {
        return e;
    }
}

}

2

You can use .asQuery() to return an array of structs

I have not used it, however since Quick uses QB under the hood you can most likely use

.setReturnFormat( 'query' )

3

@MikeR

I was having some difficulty with the .asQuery(), but it won’t return the cfquery result that I’m looking for. The other, .setReturnFormat(‘query’) works as I mentioned, but only with a qb instance and not with a Quick entity. That’s the question: is there a way within Quick to do this or is the method of using a qb instance with .setReturnFormat(‘query’) the only available option?

This way works:

var qbRaw = wirebox.getInstance("QueryBuilder@qb");
 var query = qbRaw.setReturnFormat('query').select( [ "id", "companyname" ] ).from( "clients" ).where( "cancelled", 0 ).orderByAsc('companyName').get(  options = { datasource: "x" } );

Can you do it with Quick (code below) (I’ve tried the .setReturnFormat() with the code below and it errors out.
var query = db.where('cancelled', '0').orderByAsc('companyName').get();

4

If you look at the Quick API docs (link at bottom) the quick.models.BaseEntity has a getQuickBuilder() function. This returns the QuickBuilder@quick which in turn has the getQb() function. The getQb() function returns the Quick entities internal QueryBuilder@qb with any already defined constraints applied to the Quick entity. From there you can use .setReturnFormat( 'query').

Good Timing… I was already working on a ColdBox site so I was able to test some examples :+1:. The examples below assume your Quick entity is named “client”. Also note the examples are passing the list of columns to return directly to the .get() function as well as the queryExecute() options struct with the datasource definition. Both examples will return the same results in the ColdFusion query format.

Example 1: Adding constraints to the Quick Entity

var qryClients = getInstance( "client" ).where( "cancelled", 0 ).orderByAsc('companyName').getQuickBuilder().getQb().setReturnFormat( 'query').get( "id, name", { datasource = "X" } );

Example 2: Adding constraints to the QB querybuilder

var qryClients = getInstance( "client" ).getQuickBuilder().getQb().where( "cancelled", 0 ).orderByAsc('companyName').setReturnFormat( 'query').get( "id, name", { datasource = "X" } );

https://s3.amazonaws.com/apidocs.ortussolutions.com/coldbox-modules/quick/7.1.0/index.html

5

@MikeR Thank you! This does what I wanted it to do. I was looking in the docs (https://quick.ortusbooks.com/ and https://qb.ortusbooks.com/) and did not see what I was looking for.

I don’t know I as would have known to add the .getQb() however just by looking at the API. It would be nice to see some examples of this functionality in the other websites I mention above.

Any advantage of this over just keeping it simple and using queryExecute?

Robert

6

Known working examples in the documentation would be a big deal for everyone. I have a very hard time implementing features in my code w/o this.

7

I agree, it would be great to see more in the cookbook sections of the docs, however in my opinion the lack of examples is mostly on the CF Community rather than Ortus (I put myself on that list too, I should be contributing!). All their docs are generated from GitHub repositories, and they invite anyone to make pull requests. When you look at everything they provide as open source and to the CF community in general it’s very impressive. All their repositories for OrtusBooks.com documentation can be found at https://github.com/ortus-docs.

There are many resources that I use on a regular basis when it comes to Ortus products beyond the docs at OrtusBooks.com and the corresponding API Docs.

  • CFCasts. They have a great deal of free material available, and I have found the subscription to be more than worth the cost. Highly Recommend.

  • GitHub, by that I mean that since most Ortus products are open source you can browse through the repositories to see examples of thier code and functionality you may not have realized was there.

  • ForgeBox.io. Similar to my item above I have found other people’s ForgeBox packages and browsed to their git pages to browse through code to see how something was implemented.

  • Ortus Community of course this forum right here. Asking for help. I know first-hand all the Ortus team are always ready and willing to be helpful. I have met many of them in person and they are all very friendly, helpful and an excellent group of developers.

There are many resources available out there. I am sure others can contribute more suggestions as well. These are just a few I use quite often.

I use both, it really depends on the situation. If I need to run a simple query to retrieve a limited result set (i.e. one or two columns from one record) I will use queryExecute(). If I need to build a more complex query based on various conditions and variables and then execute, I find Quick or QB to be MUCH more adapt at that. For instance, look at QB’s When / Conditionals Page.

Instead of having to build the arguments and SQL string then execute, you can add .when() to your query builder chain.

query.from( "posts" )
    .when( someFlag, function( q ) {
        q.orderBy( "published_date", "desc" );
    } )
    .get();

Hopefully, some of this has been helpful. I know I for one should be looking at what examples I can contribute to a few of the OrtusBooks.com docs.

8

I would like to help address this valid comment: “the lack of examples is mostly on the CF Community rather than Ortus”. I’ve never contributed in this way. Are there resources out there to get me started down this road? I can ask Ortus directly if this is an off topic newbie kind of question.

9

The process of a pull request is straight forward. Not knowing how familiar you are with git, here is a link to a short git tutorial if needed. I know personally early on when working on projects solo, I avoided using git. Now I feel nervous if I am editing anything outside of a git repository :man_shrugging: :rofl:

and here is a great quick overview of the pull request process.

My $0.02 would be to take a few minutes and look at how Ortus formats and presents the examples they already provide and follow their formatting and standards as much as possible.

Side Note: I did find this a while ago and find it well organized and helpful in helping me write better code and maintain some semblance of standards. :rofl:

I hope this helps and hopefully we can get some more examples submitted soon. :+1: