/* Examples of new SQL */

/* Join based related object count */
SELECT
    COUNT( outer_object.id ) AS count
FROM
    ezcontentobject outer_object, ezcontentobject inner_object, ezcontentobject_link outer_link
INNER JOIN
    ezcontentobject_link inner_link ON outer_link.id = inner_link.id
WHERE
    outer_object.id = outer_link.to_contentobject_id AND
    outer_object.status = 1 AND
    inner_object.id = inner_link.from_contentobject_id AND
    inner_object.status = 1 AND
    inner_link.op_code = 0 AND
    inner_link.from_contentobject_id = 1237 AND
    inner_link.from_contentobject_version = 3 AND
    ( inner_link.relation_type & 15 ) <> 0 AND
    ( SELECT MIN( ezct.is_invisible ) FROM ezcontentobject_tree ezct WHERE ezct.contentobject_id = inner_object.id ) = 0;

/* Join based reverse related object count */
SELECT
    COUNT( outer_object.id ) AS count
FROM
    ezcontentobject outer_object, ezcontentobject inner_object, ezcontentobject_link outer_link
INNER JOIN
    ezcontentobject_link inner_link ON outer_link.id = inner_link.id
WHERE
    outer_object.id = outer_link.from_contentobject_id AND
    outer_object.status = 1 AND
    inner_object.id = inner_link.from_contentobject_id AND
    inner_object.status = 1 AND
    inner_link.op_code = 0 AND
    inner_link.to_contentobject_id = 1237 AND
    inner_link.from_contentobject_version = inner_object.current_version AND
    ( inner_link.relation_type & 15 ) <> 0 AND
    ( SELECT MIN( ezct.is_invisible ) FROM ezcontentobject_tree ezct WHERE ezct.contentobject_id = inner_object.id ) = 0;



/* Join based related object id, status and name (for debugging) */
SELECT
    outer_object.id, outer_object.status, outer_object.name
FROM
    ezcontentobject outer_object, ezcontentobject inner_object, ezcontentobject_link outer_link
INNER JOIN
    ezcontentobject_link inner_link ON outer_link.id = inner_link.id
WHERE
    outer_object.id = outer_link.to_contentobject_id AND
    outer_object.status = 1 AND
    inner_object.id = inner_link.from_contentobject_id AND
    inner_object.status = 1 AND
    inner_link.op_code = 0 AND
    inner_link.from_contentobject_id = 1237 AND
    inner_link.from_contentobject_version = 3 AND
    ( inner_link.relation_type & 15 ) <> 0 AND
    ( SELECT MIN( ezct.is_invisible ) FROM ezcontentobject_tree ezct WHERE ezct.contentobject_id = inner_object.id ) = 0;

/* Join based reverse related object id, status and name (for debugging) */
SELECT
    outer_object.id, outer_object.status, outer_object.name
FROM
    ezcontentobject outer_object, ezcontentobject inner_object, ezcontentobject_link outer_link
INNER JOIN
    ezcontentobject_link inner_link ON outer_link.id = inner_link.id
WHERE
    outer_object.id = outer_link.from_contentobject_id AND
    outer_object.status = 1 AND
    inner_object.id = inner_link.from_contentobject_id AND
    inner_object.status = 1 AND
    inner_link.op_code = 0 AND
    inner_link.to_contentobject_id = 1237 AND
    inner_link.from_contentobject_version = inner_object.current_version AND
    ( inner_link.relation_type & 15 ) <> 0 AND
    ( SELECT MIN( ezct.is_invisible ) FROM ezcontentobject_tree ezct WHERE ezct.contentobject_id = inner_object.id ) = 0;
