Query Builder


#1

We are happy to announce that lucee can handle Queries in different way. This is simplest & easiest way to handle the Queries by lucee server.

QueryBuilder

Create object to Query Builder Component which is used to build the query in different way.

qb = new QueryBuilder();

DataSource Initialize

We need a datasource to execute the query. Here we can initialize the datasource by two methods

Method:1 datasource Initialize with constructor

qb = new QueryBuilder(“dsnName”);

Method:2 datasource Initialize with setDataSource()

qb.setDataSource (“dnsName”);

Get DataSource Info

You can get the datasource info from the getDataSourceInfo().

qb.getDatasourceInfo(“dataSourceName”);

It return’s details of datasource in structure

Select

Select the column from the table.

Method 1:

passing column name as string

qb.select(“column”)

Method 2:

passing column names as array

qb.select([“column1”, “column2”]);

If you want to overwrite the selected column need to use like below

qb.select([“column1”, “column2”], true);

Examples

Example

//1st method select the single column passing value as string
		qb = new QueryBuilder();
		qb.setDatasource("TestDSN");
		qb.select("cat_Name");
		qb.from("categories");
		writeDump(var = qb.execute(), label="passing Column name as string");

		qb = new QueryBuilder();
		qb.setDatasource("TestDSN");
		qb.select("cat_Name, cat_ID");
		qb.from("categories");
		writeDump(var = qb.execute(), label="passing Column name as list");

	//2nd method select the multiple column with array of data

		qb = new QueryBuilder("TestDSN");
		qb.select(["cat_Name", "Cat_id"]);
		qb.from("categories");
		writeDump(var = qb.execute(), label="passing Column name as array");

	//overwrites selected data

		qb = new QueryBuilder("TestDSN");
		qb.select(["cat_Name", "Cat_id"]);
		qb.select("cat_Name", true);
		qb.from("categories");
		writeDump(var = qb.execute(), label="Select statement with overwrite eq true");

	// To reterive all data

		without select statement it calls all the data in table

		qb = new QueryBuilder("TestDSN");
		qb.from("categories");
		writeDump(var = qb.execute(), label="Select statement with overwrite eq true");


		qb = new QueryBuilder("TestDSN");
		qb.select("*");
		qb.from("categories");
		writeDump(var = qb.execute(), label="Select statement with overwrite eq true");

From

Used to choose the table which you want to perform the operation. Without from statement it throw’s error.

Method 1:

passing table name as string

qb.from(“table”);

Method 2:

Passing table name as array

qb.from([“table1”,“table2”, “table3”]);

IF you want to overwrite the table name it replace already defined table name

qb.from(“tableName”, true);

Examples

// passing table name as string

  qb = new QueryBuilder("TestDSN");
  qb.select("cat_Name");
  qb.from("categories,product");
  writeDump(var = qb.execute(), label="using from");

// passing table name as array format

  qb = new QueryBuilder("TestDSN");
  qb.from(["categories","product", "item"]);
  writeDump(var = qb.execute(), label="passing table name as array");

// setting overwrite true in from statement

qb = new QueryBuilder(“TestDSN”);
qb.from([“categories”,“product”, “item”]);
qb.from(“categories”, true)
writeDump(var = qb.execute(), label=“overwrite true in from statement”);

Where Clause

Here we can use where condition opertions

qb.where(QB::eq(“columnName”, “value”));

QB is component contains static function which is used to perform operations like eq,neq,gt,lt,in,notin,and,or,like,between

Use like below

Syntax

qb.where(QB::eq(“columnName”, “value”));
qb.where(QB::neq(“columnName”, “value”));
qb.where(QB::gt(“columnName”, “value”));
qb.where(QB::lt(“columnName”, “value”));
qb.where(QB::in(“columnName”, “listValue”));
qb.where(QB::notin(“columnName”, “listValue”));
qb.where(QB:: like(“columnName”, “SearchingText”));
qb.where(QB:: between(“columnName”, “values as list”));
qb.where(QB::and(QB::neq(“columnName”, “value”), QB::gt(“columnName”, “value”), QB::lt(“columnName”, “value”)));
qb.where(QB::or(QB::eq(“columnName”, “value”), QB::eq(“columnName”, “value”)));

Examples

 Eq opertaion
qb = new QueryBuilder();
qb.setDatasource("TestDSN");
qb.select("cat_Name");
qb.from("categories");
qb.where(QB::eq("Cat_id", "1"));
writeDump(var = qb.execute(), label="Eqaual operation in QueryBuilder like QB::eq()");



// not equal

qb = new QueryBuilder();
qb.setDatasource("TestDSN");
qb.select("cat_Name");
qb.from("categories");
qb.where(QB::neq("Cat_id", "1"));
writeDump(var = qb.execute(), label="Not Eqaual operation in QueryBuilder like QB::neq()");



//greater than
qb = new QueryBuilder();
qb.setDatasource("TestDSN");
qb.select("cat_Name");
qb.from("categories");
qb.where(QB::gt("Cat_id", "1"));
writeDump(var = qb.execute(), label="Greater than operation in QueryBuilder like QB::gt()");



// less than
qb = new QueryBuilder();
qb.setDatasource("TestDSN");
qb.select("cat_Name");
qb.from("categories");
qb.where(QB::lt("Cat_id", "4"));
writeDump(var = qb.execute(), label="Less than operation in QueryBuilder like QB::lt()");


// IN
qb = new QueryBuilder("TestDSN");
qb.select(["cat_Name", "Cat_id"]);
qb.from("categories");
qb.where(QB::in("Cat_id", "1,2,3"));
writeDump(var = qb.execute(), label="Less than operation in QueryBuilder like QB::in()");

//not IN
qb = new QueryBuilder("TestDSN");
qb.select(["cat_Name", "Cat_id"]);
qb.from("categories");
qb.where(QB:: notin("Cat_id", "2,4"));
writeDump(var = qb.execute(), label="Less than operation in QueryBuilder like QB::notin()");

//not
qb = new QueryBuilder("TestDSN");
qb.select(["cat_Name", "Cat_id"]);
qb.from("categories");
qb.where(QB:: not("Cat_id", "1"));
writeDump(var = qb.execute(), label="Less than operation in QueryBuilder like QB::not()");

//Like
qb = new QueryBuilder("TestDSN");
qb.select(["cat_Name", "Cat_id"]);
qb.from("categories");
qb.where(QB:: Like("cat_Name", "%ver"));
writeDump(var = qb.execute(), label="Like operation in QueryBuilder like QB:: Like ()");

//Between
qb = new QueryBuilder("TestDSN");
qb.select(["cat_Name", "Cat_id"]);
qb.from("categories");
qb.where(QB:: Between("Cat_id", "1,3"));
writeDump(var = qb.execute(), label="Between operation in QueryBuilder like QB:: Between ()");


//And Opertion
qb = new QueryBuilder("TestDSN");
qb.select(["cat_Name", "Cat_id"]);
qb.from("categories");
qb.where(QB::and(QB::neq("cat_Name", "freeware"), QB::gt("Cat_id", "2"), QB::lt("Cat_id", "4")));
writeDump(var = qb.execute(), label="And operation in QueryBuilder like QB::and(QB::neq(), QB::gt(),..)");

// OR Operation
qb = new QueryBuilder("TestDSN");
qb.select(["cat_Name", "Cat_id"]);
qb.from("categories");
qb.where(QB::or(QB::eq("cat_Name", "freeware"), QB::eq("Cat_id", "3")));
writeDump(var = qb.execute(), label="OR operation in QueryBuilder like QB::or(QB::neq(), QB::gt(),..)");

Joins

We can joins the table

Syntax

//inner join
From("table1")
qb.join("table2", QB:: on("table1.columnName", "table2.columnName"));

From("table1")
qb.leftJoin("table2", QB:: on("table1.columnName", "table2.columnName"));

From("table1")
qb.rightJoin("table2", QB:: on("table1.columnName", "table2.columnName"));

on method is called from QB component used to join the tables based on their column values

QB:: on(“table1.columnName”, “table2.columnName”);

Examples

// inner join
	qb = new QueryBuilder("TestDSN");
	qb.from("categories");
	qb.join("product", QB:: on("categories.Cat_id", "product.pro_id"));
	qb.join("item", QB:: on("product.pro_id", "item.itm_id"));
	writeDump(var = qb.execute(), label="join operation in QueryBuilder");

	//Left join

	qb = new QueryBuilder("TestDSN");
	qb.from("product");
	qb.Leftjoin("categories", QB:: on("categories.Cat_id", "product.pro_id"));
	writeDump(var = qb.execute(), label="Leftjoin operation in QueryBuilder");

	//Right join
	qb = new QueryBuilder("TestDSN");
	qb.from("categories");
	qb.rightJoin("product", QB:: on("categories.Cat_id", "product.pro_id"));
	writeDump(var = qb.execute(), label="Rightjoin operation in QueryBuilder");

Is Just beginning later will support for create, insert, delete, drop operations.

Any suggestion should be greatly appreciated.

Thanks
cfmitrah


#2

Interesting! Can you also set LIMIT, ORDER BY, GROUP BY ?


#3

This looks very similar to:
https://www.forgebox.io/view/qb


#4

Hi @Ivan,

Yes In future we are going to implement all those operation done by cfquery.

Use Query Builder as Extension . You can installed it in both server & web.

Available in extension Repo


#5

Hi @markdrew,
@tonyjunkes has also developed a similar solution for FW/1 http://tonyjunkes.com/blog/working-with-fw1-and-qb/


#6

That was actually developed by Eric Peterson. The project is called QB. I just wrote some articles about integrating it with FW/1.


#7

Interesting, i was not aware of the QB project from Eric, we need to look into this and decide how we go on with this.
I’m still thinking that is something Lucee needs independent of Forgebox or FW/1.
Any inputs welcome


#8

It’s a shame that Lucee doesn’t work MORE with forgebox to be honest. Having it’s own repository/extension store mechanism that has no front end (aside from the lucee admin) seems silly, especially with all the features that forgebox.io already has.

But sure, I guess great minds do think alike and we have two similar projects. I just want whichever project to have good documentation, rather than just blog posts or missing links in the extension admin (not that there are, but a lucee admin is not the most google-able place)


#9

I discussed this a bit internally with Micha, but my thought is that this project is a duplication of efforts given the maturity and configurability of Eric Peterson’s project which already has extensible grammar support, shema builders, and several community contributors. QB is also part of larger projects like the CLI database migrations commands here: https://www.forgebox.io/view/commandbox-migrations
QB will also likely be at the core of a concept project called Quick, which is to be a simpler ORM implementation in CFML that doesn’t use Hibernate. Eric’s QB already seems to have quite a bit of traction in the CFML space IMO.

I do realize QB itself is a little Box-centric (as is the migrations project, but that’s not a huge deal since the CLI is a turn key util), though it is simple enough to be repurposed for FW/1 and probably standalone if you wanted. (I’m personally tired of the lowest common denominator always being the “no-framework” crowd which hamstrings any library wanting wide adoption but also wants to use 3rd party dependencies, package management, DI, or any moden framework features)

Minimally, if Lucee continues their own query builder project, my recommendation is to change the name to avoid confusion.


#10

Query Builder
Agree ALL modules in forgebox/lucee or any repo should be prefixed unless agnostic (aka coldbox-x or cb- for coldbox modules), not that i think any framework-centric extensions should be in the lucee repo. Personally I’d call it ‘CFMitrah Coldbox Query Builder’ (cfmitrah-cb-querybuilder)? (assuming it’s a coldbox-centric module") otherwise drop coldbox, and go with brand)

On Lucee Extension Providers
Lucee should have it’s own repos IMHO. It allows lucee to dictate what it wants, however it would be good for forgebox (being coldbox centric (in reality) to have coldbox extension provider for Lucee separate to the lucee provider) Each framework could then have their own provider and contribs could be filtered through framework people. Otherwise we’ll end up with a lot of ‘noise’ in the exensions library unless we introduce categories or highlight fw dependancies). Personally I don’t want coldbox, fusebox or any other framework extensions in my results. That’s why I suggest there are framework-based providers.

As for repos (forgebox,riaforge,github,bitbucket,self-hosted), we have our own also (with over 100 components) as forgebox has several limitations (the biggest one is having only one type, then again it is a coldbox/preside repo really, not that agnostic, let’s be honest). Forgebox has their own focus, so rather than arguing, we create our own.

Component Makeup the CB mafia
As for the ‘lowerest common denominator’ and hamstring comment, I couldn’t disagree more, and find this comment self-serving and a conflict of interest (aka *box serving), most people DONT use ColdBox (there it’s been said). There are so many components on forgebox that 90% of devs can’t use out of the box as they have hard-wired internal references (DI, package mangement, variables) for generic functionality for no performance or implmentation gain. That’s an epic fail for a generic component in my book. What happens in 5 years when coldbox is not the fad anymore (like fusebox, mach ii and others). coldbox will be in that boat soon enough and then what? we rewite everything for the next framework?

Cross-Framework Dependency Self-Documentation Standards
If a component needs external resources it should document them and allow the framework running it to decide HOW to implement them, not the component itself. Break out your functionality into functional areas and make it reusable, then let the application decide how to implement it.

In the application I have a heavy framework is unnecessary.A framework should be able to consume block efficiently, or it needs to be rebuilt in a world of microservices (aka 2016 onward).

It wouldn’t be that hard to create community standards (and by that I don’t mean ‘cool framework of the day’ standards) for module dependencies, package management). None of this should be baked into a functionality or generic view (eg: facebook like button renderer) component. It really wouldn’t be that hard to create. We need a standard or this failed mess will continue. If we at least had some basic guidelines for coding ‘community extensions’ then we would have more contributions IMHO.

If your framework wants to document further to the standard, woo hoo, do it in a proprietary way so other frameworks don’ throw and error, or write a tool to convert the standard to your framework standard.

Conclusion Rant
Right now it’s *box or bugger off, why not just rename lucee to luccebox? Have we learnt nothing in 20 years?

Do we bake coldbox functionality into the server administrator? so why would logging into oauth, or talking to xero or any other 3rd party service need framework-specific code? It NEVER DOES. Having suffered through all frameworks (mine included) I can safely say wiring a component directly to a framework will bite you in the bum eventually when you move frameworks (as we do each 5 years, and will happen again).

I know I’m raving on about ColdBox, but it’s “insert posterboy framwork of the year” into any ‘coldbox’ statement really. Mach II mafia used to be as bad, and before that the fusebox mafia…


#11

I just want whichever project to have good documentation.

Amen.


#12

I don’t think it is coldbox centric, or at least it doesn’t have to be. Commandbox, Forgebox and CFConfig are the most amazing useful things to have happened since Lucee was invented and they have nothing to do with the ColdBox (MVC)

Forgebox.io could become literally the npm of CF if treated right. The problem with the extension provider for Lucee is that we are back to something that “Lucee” controls (or rather the LAS, of which Ortus is part of) that the community cannot contribute to that easily without having to pay off someone to add our own extension.

So, if I make an extension, how do I add it to the lucee admin (or more importantly to all the update mechanisms)? I have to go ask Micha, cos I know him.

Forgebox, although fairly new, has a lot of the same features that are required, even having nice semver versioning and what not. You can have private repos (I think?) as well as voting and commenting and starring. Can you do that for the extension store at the moment? How do I know the state of an extension? How do I know WHO to contact to get more info, without literally having to spin up a server myself and then go to the admin?


#13

It appears that this would be vulnerable to SQL Injection right? I don’t see any parameterization going on. It appears to just pass the values right into a SQL String… https://github.com/lucee/extension-querybuilder/blob/master/source/components/org/lucee/cfml/QB.cfc#L9

I would think that you should make it clear that the end user needs to take on the full burden of SQL Injection concerns when using this component.

While I haven’t fully looked into the coldbox-modules/qb that one does appear to be concerned about SQL Injection - it will parameterize values the where clause for example. I’m just wondering why that was not considered when building this one?


#14

Would someone please explain why anyone would want this when you can just use cfquery with less lines of code? I do want to learn more. This is in no way a criticism.


#15

For simple queries it probably won’t buy you anything. I wrote qb (mentioned above) and I use it because 1) I like the syntax better (personal preference) and 2) because I can easily compose SQL statements without worrying about order (I can specify “ORDER BY” before “WHERE” and “LIMIT” before “SELECT”). A 3rd (for qb, not necessarily this project) is database grammar agnostic (You can write query.limit( 5 ) in qb and it will work in all supported grammars even though they don’t all support the LIMIT keyword.)

Hope that makes sense. I wrote more about it in the qb documentation: https://qb.ortusbooks.com


#16

Thought I’d link this little video that @micstriit recently put together;

It talks about the myriad of ways you can implement queries… including a brief discussion about query builder concepts.