Complex SELECT ?

Hello

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
	ON
		tasks.typeId = dependance.typeId
		AND tasksDep.jobId = tasks.jobId
		AND tasksDep.statusId <> 5
WHERE
		tasks.statusId = 1
	AND tasksDep.id IS NULL

Thank you in advance

++ Gilles

hello

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

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.

Bob

Hi,

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!

Regards,
Max