Hi, is there a way ho to select from subquery?

I really need something like this:

select *
from (
  select v.*, r.id rid
  from result_version v
  join result r on r.id = v.result_id
  where
    v.status != "concept"
  order by v.datetime DESC
) t
group by rid

I try to

$subQ = $this -> DAO -> createQueryBuilder();
        $subQ
            -> select ( "version, result.id rid" )
            -> from ( $this -> getEntityClassName(), "version" )
            -> join ( "version.result", "result" )
            -> andWhere ( "result.form = :form" )
            -> andWhere( "version.status != :concept" )
            -> addOrderBy( "version.datetime", "DESC" )

            -> setParameter( 'form', $form )
            -> setParameter( 'concept', ResultVersionStatus::CONCEPT )
        ;

        $qb = $this -> DAO -> createQueryBuilder();
        $qb
            -> select ( "t" )
            -> from ( $subQ -> getDQL(), "t" )
            -> groupBy ( "t.rid" )
        ;

but i seems not to working. Thanks for help

No, I believe you can use subqueries only in WHERE, you can study the syntax here

The query can be rewritten to this:

SELECT v.*
FROM   result_version v
LEFT   JOIN result_version v2
ON     v.result_id = v2.result_id
       AND v.status = v2.status
       AND v.datetime < v2.datetime
WHERE  v.status != "concept"
       AND v2.result_id IS NULL

Thanks to Kajman http://diskuse.jakpsatweb.cz/?…


You must first log in to participate in this discussion