Details
-
Type:
Bug
-
Status:
Open
-
Priority:
Major
-
Resolution: Unresolved
-
Affects Version/s: 1.2.1
-
Fix Version/s: None
-
Component/s: Query
-
Labels:None
Description
I use this schema:
---
Actuality:
connection: web
tableName: actuality
actAs:
softDelete: ~
Timestampable: ~
Sluggable:
fields: [name]
indexName: actuality_sluggable
canUpdate: true
columns:
id:
type: integer(4)
primary: true
unsigned: true
sequence: actuality
created_at:
type: timestamp
updated_at:
type: timestamp
published_at:
type: timestamp
notnull: true
unpublished_at:
type: timestamp
name:
type: string(255)
notnull: true
description_short:
type: string(1000)
notnull: true
description:
type: clob
notnull: true
is_professional:
type: boolean
default: true
relations:
Categories:
class: ActualityCategory
local: actuality_id
foreign: actuality_category_id
refClass: ActualityActualityCategory
foreignAlias: Actualities
ActualityActualityCategory:
connection: web
tableName: actuality_actuality_category
options:
symfony:
form: false
filter: false
columns:
actuality_id:
type: integer(4)
primary: true
unsigned: true
actuality_category_id:
type: integer(2)
primary: true
unsigned: true
relations:
Actuality:
onDelete: CASCADE
ActualityCategory:
connection: web
tableName: actuality_category
actAs:
softDelete: ~
Timestampable: ~
Sluggable:
fields: [name]
indexName: actualityc_sluggable
canUpdate: true
columns:
id:
type: integer(2)
primary: true
unsigned: true
sequence: actualitycategory
created_at:
type: timestamp
updated_at:
type: timestamp
name:
type: string(60)
notnull: true
Function in my model Actuality
public function retrieveActive($is_authenticated = false) { $q = $this->createQuery('a') ->leftJoin('a.Categories c') ->orderBy('a.published_at DESC'); if (!$is_authenticated) { $q->where('a.is_professional = ?', false); } return $q; }
with that, i receive the message "Doctrine_Connection_Oracle_Exception" because Oracle is strict and in the subselect, the field published_at is missing in select. This is the query generate by doctrine:
SELECT a.id AS a__id, a.published_at AS a__published_at, a.name AS a__name, a.description_short AS a__description_short, a.slug AS a__slug, a2.id AS a2__id, a2.name AS a2__name FROM actuality a LEFT JOIN actuality_actuality_category a3 ON (a.id = a3.actuality_id) LEFT JOIN actuality_category a2 ON a2.id = a3.actuality_category_id WHERE a.id IN (SELECT a4.id FROM ( SELECT DISTINCT a4.id FROM actuality a4 LEFT JOIN actuality_actuality_category a6 ON (a4.id = a6.actuality_id) LEFT JOIN actuality_category a5 ON a5.id = a6.actuality_category_id WHERE a4.is_professional = 0 ORDER BY a4.published_at DESC ) a4 WHERE ROWNUM <= 5) AND (a.is_professional = 0) ORDER BY a.published_at DESC
If i execute this query and add the field published_at in subquery, this is work.
SELECT a.id AS a__id, a.published_at AS a__published_at, a.name AS a__name, a.description_short AS a__description_short, a.slug AS a__slug, a2.id AS a2__id, a2.name AS a2__name FROM actuality a LEFT JOIN actuality_actuality_category a3 ON (a.id = a3.actuality_id) LEFT JOIN actuality_category a2 ON a2.id = a3.actuality_category_id WHERE a.id IN (SELECT a4.id FROM ( SELECT DISTINCT a4.id, a4.published_at FROM actuality a4 LEFT JOIN actuality_actuality_category a6 ON (a4.id = a6.actuality_id) LEFT JOIN actuality_category a5 ON a5.id = a6.actuality_category_id WHERE a4.is_professional = 0 ORDER BY a4.published_at DESC ) a4 WHERE ROWNUM <= 5) AND (a.is_professional = 0) ORDER BY a.published_at DESC
Thanks for your help.
I specify another thing. I use this with a pager.