Doctrine 2: how do you use a subquery column (in the SELECT clause)

SELECT temp.* FROM (SELECT
     page_instance.name AS 'page name',
    (
       SELECT COUNT(*) 
       FROM visitor_event 
       WHERE page_instance.id = visitor_event.item_id
     ) AS 'visit_count'

    FROM item_event

    LEFT JOIN visitor_event_type ON visitor_event_type.id =  visitor_event_type_id
    LEFT JOIN page_instance ON page_instance.id = visitor_event.item_id
    LEFT JOIN page ON page.id = page_instance.page_id

    WHERE visitor_event_type.handle = 'viewed'
    AND page_instance.name != 'NULL'
    GROUP BY page.id
    LIMIT 5 ) as temp ORDER BY temp.visit_count DESC

select c.name, j.james_count  
from company c cross apply
     (select count(*) as james_count
      from [user] u
      where first_name = 'James' and u.parent_company_id = company_id
     ) j
where james_count > 0;

select c.name, count(*) as james_count
from company c join
     [user] u
     on u.parent_company_id = c.company_id and u.first_name = 'James'
group by c.name;

select t1.S_ID
from table1 t1
where t1.S_Type = 'TYPE' and
      t1.item in (select t2.item from table2 t2)
group by S_ID
having count(distinct t1.item) = (select count(distinct t2.item) from table2 t2);

SELECT

  t1.column1,
  t1.column2,
  t2.column1,
  t3.column2

FROM

  table1 t1

JOIN table2 t2 ON t2.id = 
(
    SELECT id 
    FROM table2 
    WHERE t1.column2 > table2.column1
    ORDER BY table2.date DESC
    LIMIT 1
) 

JOIN table3 t3 ON t2.column1=t3.column2