Help with a query

Communities Quick ORM
1

I am needing help with a query.

array function getAll(){

return query.select("staff_details.*", "user.id", "user.firstName", "user.lastName", "concern_status.statusName")
	.setReturnFormat('array')
	.from("staff_details")
	.leftJoin("user", "user.id", "staff_details.userIDfk")
	.leftJoin("concern_status", "concern_status.statusID", "staff_details.statusIDfk")
	.union( function ( q ){
		q.from("staff_notes")
			.select("count( * ) AS NoteCount")
			.where("userIDfk", "user.id");
	}
	
	)
	.get();

}

If I take the union out the left join works just fine. In the index as I loop through the query I need to show how many notes a user has, which is in the table concern_notes. I tried using a union and I get this error:

Unknown column ‘count( * )’ in ‘field list’

Stack Frame(s): 13

  • 13

modules\qb\models\Grammars\BaseGrammar.cfc:106

  • 12

modules\qb\models\Grammars\AutoDiscover.cfc:29

  • 11

modules\qb\models\Query\QueryBuilder.cfc:3539

  • 10

modules\qb\models\Query\QueryBuilder.cfc:3495

  • 9

modules\qb\models\Query\QueryBuilder.cfc:3247

  • 8

models\StaffServices.cfc:19

  • 7

handlers\staff.cfc:11

  • 6

coldbox\system\web\Controller.cfc:1257

  • 5

coldbox\system\web\Controller.cfc:1006

  • 4

coldbox\system\web\Controller.cfc:713

  • 3

coldbox\system\Bootstrap.cfc:290

  • 2

coldbox\system\Bootstrap.cfc:506

  • 1

Application.cfc:120

I’m not sure how to get the information from the user and staff_notes (count) and staff details in one query. In the old days I would include a count query that would use the userid within the loop but I can’t figure out how to do this in coldbox.

Thank you for your help,

Rick

2

Use selectRaw for your count.

3

Thank you elpete. I read about that but didn’t understand it. I appreciate your help so much!!!

Rick

4

I corrected that and now I get this error:
The used SELECT statements have a different number of columns

Stack Frame(s): 13

  • 13

modules\qb\models\Grammars\BaseGrammar.cfc:106

  • 12

modules\qb\models\Grammars\AutoDiscover.cfc:29

  • 11

modules\qb\models\Query\QueryBuilder.cfc:3539

  • 10

modules\qb\models\Query\QueryBuilder.cfc:3495

  • 9

modules\qb\models\Query\QueryBuilder.cfc:3247

  • 8

models\StaffServices.cfc:19

I put a comment on line 19 of the object in the model:

array function getAll(){

return query.select("staff_details.*", "user.id", "user.firstName", "user.lastName", "concern_status.statusName")
	.setReturnFormat('array')
	.from("staff_details")
	.leftJoin("user", "user.id", "staff_details.userIDfk")
	.leftJoin("concern_status", "concern_status.statusID", "staff_details.statusIDfk")
	.union( function ( q ){
		q.from("staff_notes")
			.selectraw("count( * ) AS NoteCount")
			.where("staff_notes.userIDfk", "id");
	} // this is line 19
	
	)
	.get();

}

Thank you again!

Rick

5

Unioned queries must have identical columns. I think you want a subselect instead maybe?