Complex SELECT ?

WanacomEngine 14 Oct, 2014

Do you have any idea how I can integrate the request below in ChronoConnectivity5 ?
SELECT	 tasks.*
	FROM pabg9_chronoengine_jobs_tasks AS tasks
	LEFT JOIN 	pabg9_chronoengine_jobs_tasks_types_dependance AS dependance
	INNER JOIN pabg9_chronoengine_jobs_tasks AS tasksDep
	ON tasksDep.typeId = dependance.dependsOfTypeId
		tasks.typeId = dependance.typeId
		AND tasksDep.jobId = tasks.jobId
		AND tasksDep.statusId <> 5
		tasks.statusId = 1

Thank you in advance

++ Gilles
WanacomEngine 15 Oct, 2014

Is there a way to completely custom query, or are we limited to the field "conditions" with filters in the WHERE clause?

Thank you in advance.

++ Gilles
GreyHead 16 Oct, 2014
Hi Gilles,

I'm far from expert in CCV5 :-(

I don't know of any way to have a completely custom query.

I think that you can build the one you have here by adding two extra Model tabs and defining the Join conditions.

I have no idea if CC can handle the INNER JOIN back to the first table but I'd expect that to work OK.

I have a small hesitation about tasksDep.statusId <> 5 but the only way to find out is to build it and test.

Max_admin 16 Oct, 2014

The query has 2 consecutive "ON", is this correct ?

You can use the "Join conditions" field for the "ON" part, and the conditions box for the WHERE part, and as Bob said you will need 3 models for the 3 tables, with hasOne or belongsTo relationships.

You can try get the Joins working first then try the conditions, just enable the CC debug to be able to check the built query!

ChronoForms developer...
Did you try ChronoMyAdmin for managing your Joomla database tables ?
This topic is locked and no more replies can be posted.