Missing quotation for Postgresql when using relation in active records (Yii 1.13) #1918

Closed
unibas-franckborel opened this Issue Jan 3, 2013 · 9 comments

Comments

Projects
None yet
6 participants
@unibas-franckborel

When using relation in active records the quotation will not be set for alias. e.g.:

Relationname in Model (here Name.php):

'PrimaryLocation' => array(self::BELONGS_TO, 
'Location', 
'location_id')

Used in:

$names = Name::model()->with('PrimaryLocation')->findAll($criteria);

Will create following SQL-Statement:

SELECT
"t"."id" AS "t0_c0",
"t"."location_id" AS "t0_c1",
 "t"."name" AS "t0_c2", 
"t"."comment" AS "t0_c3", 
"t"."created_by" AS "t0_c4", 
"t"."modified_by" AS "t0_c5", 
"t"."reviewed_by" AS "t0_c6", 
"t"."project_id" AS "t0_c7", 
"t"."timestamp" AS "t0_c8", 
"t"."location_certain" AS "t0_c9", 
"PrimaryLocation"."id" AS "t1_c0", 
"PrimaryLocation"."primary_name_id" AS "t1_c1", 
"PrimaryLocation"."location_type_id" AS "t1_c2", 
"PrimaryLocation"."comment" AS "t1_c3", 
"PrimaryLocation"."created_by" AS "t1_c4", 
"PrimaryLocation"."modified_by" AS "t1_c5", 
"PrimaryLocation"."reviewed_by" AS "t1_c6", 
"PrimaryLocation"."project_id" AS "t1_c7", 
"PrimaryLocation"."geometry" AS "t1_c8", 
"PrimaryLocation"."timestamp" AS "t1_c9",
st_astext(PrimaryLocation.geometry) AS geometry_wkt
                 ------------Error, this should be written as 
"PrimaryLocation".geometry 
FROM "name" "t"  LEFT OUTER JOIN "location" "PrimaryLocation" ON 
("t"."location_id"="PrimaryLocation"."id")  
WHERE ((name LIKE '%fre%' OR name LIKE '%FRE%' OR 
name LIKE '%fre%' OR name LIKE '%Fre%')) 
ORDER BY name 
LIMIT 10 

This was not a problem with Yii < 1.13

@resurtm

This comment has been minimized.

Show comment Hide comment
@resurtm

resurtm Jun 28, 2013

Contributor

@ubfr-franckborel, could you please post your DDL (column types in particular) of your tables? What do $criteria contains, how did you build it? What software do you use (PostgreSQL, PostGIS precise versions, etc.)? This information will help us fixing this issue.

Contributor

resurtm commented Jun 28, 2013

@ubfr-franckborel, could you please post your DDL (column types in particular) of your tables? What do $criteria contains, how did you build it? What software do you use (PostgreSQL, PostGIS precise versions, etc.)? This information will help us fixing this issue.

@resurtm

This comment has been minimized.

Show comment Hide comment
@resurtm

resurtm Jun 29, 2013

Contributor

I think we should postpone this to 1.1.15 or at least to 1.1.14 stable release, but not to 1.1.14-RC. There is lack of information on how to reproduce this bug.

@yiisoft/core-developers, opinions?

Contributor

resurtm commented Jun 29, 2013

I think we should postpone this to 1.1.15 or at least to 1.1.14 stable release, but not to 1.1.14-RC. There is lack of information on how to reproduce this bug.

@yiisoft/core-developers, opinions?

@mdomba

This comment has been minimized.

Show comment Hide comment
@mdomba

mdomba Jun 29, 2013

Member

The point of releasing a RC is to stop introducing new PR and polish eventual tips/bugs introduced in the RC release
so if this does not make it into 1.1.14 RC it will be for 1.1.15.

Member

mdomba commented Jun 29, 2013

The point of releasing a RC is to stop introducing new PR and polish eventual tips/bugs introduced in the RC release
so if this does not make it into 1.1.14 RC it will be for 1.1.15.

@resurtm

This comment has been minimized.

Show comment Hide comment
@resurtm

resurtm Jun 29, 2013

Contributor

Ok, postponed to 1.1.15.

Contributor

resurtm commented Jun 29, 2013

Ok, postponed to 1.1.15.

@unibas-franckborel

This comment has been minimized.

Show comment Hide comment
@unibas-franckborel

unibas-franckborel Jul 8, 2013

Am 28.06.2013 18:32, schrieb resurtm:

@ubfr-franckborel https://github.com/ubfr-franckborel, could you
please post your DDL (column types in particular) of your tables? What
do |$criteria| contains, how did you built it? What software do you use
(PostgreSQL, PostGIS precise versions, etc.)? This information will help
us fixing this issue.

yep, give me a bit of time. It is far far away from my focus now ;). I
will take a look at the scripts and sent you the informations.

-- Franck

Am 28.06.2013 18:32, schrieb resurtm:

@ubfr-franckborel https://github.com/ubfr-franckborel, could you
please post your DDL (column types in particular) of your tables? What
do |$criteria| contains, how did you built it? What software do you use
(PostgreSQL, PostGIS precise versions, etc.)? This information will help
us fixing this issue.

yep, give me a bit of time. It is far far away from my focus now ;). I
will take a look at the scripts and sent you the informations.

-- Franck

@nineinchnick

This comment has been minimized.

Show comment Hide comment
@nineinchnick

nineinchnick Aug 21, 2013

Contributor

@ubfr-franckborel how did you add that expression to the select? If you declared it as aliases nothing inside will be quoted automatically. If that is true, this is not a bug.

Contributor

nineinchnick commented Aug 21, 2013

@ubfr-franckborel how did you add that expression to the select? If you declared it as aliases nothing inside will be quoted automatically. If that is true, this is not a bug.

@cebe

This comment has been minimized.

Show comment Hide comment
@cebe

cebe Jan 28, 2014

Owner

Closed until more feedback given.

Owner

cebe commented Jan 28, 2014

Closed until more feedback given.

@cebe cebe closed this Jan 28, 2014

@eleiva

This comment has been minimized.

Show comment Hide comment
@eleiva

eleiva Apr 29, 2014

Same happens to me on postgres:

public function relations(){
return array(
   'activityUsers' => array(self::HAS_MANY, 'ActivityUser', 'activity_id')
     )
}
public function search($user_id){
  $criteria=new CDbCriteria;
  $criteria->with = array('activityUsers');
  $criteria->condition = 'activityUsers.user_id = '.$user_id;
...
...
}

CdbException

 CDbCommand fail executing SQL: SQLSTATE[42P01]: Undefined table: 7 ERROR: missing FROM-clause entry for table "activityusers"
LINE 1: ... ("activityUsers"."activity_id"="t"."id") WHERE (activityUs...
^. The SQL statement executed was: SELECT COUNT(DISTINCT "t"."id") FROM "activity" "t" LEFT OUTER JOIN "activity_user" "activityUsers" ON ("activityUsers"."activity_id"="t"."id") WHERE (activityUsers.user_id = 145)
/var/www/common/yii/yii-1.1.14.f0fee9/framework/db/CDbCommand.php(543)

Any help will be apreciate.

eleiva commented Apr 29, 2014

Same happens to me on postgres:

public function relations(){
return array(
   'activityUsers' => array(self::HAS_MANY, 'ActivityUser', 'activity_id')
     )
}
public function search($user_id){
  $criteria=new CDbCriteria;
  $criteria->with = array('activityUsers');
  $criteria->condition = 'activityUsers.user_id = '.$user_id;
...
...
}

CdbException

 CDbCommand fail executing SQL: SQLSTATE[42P01]: Undefined table: 7 ERROR: missing FROM-clause entry for table "activityusers"
LINE 1: ... ("activityUsers"."activity_id"="t"."id") WHERE (activityUs...
^. The SQL statement executed was: SELECT COUNT(DISTINCT "t"."id") FROM "activity" "t" LEFT OUTER JOIN "activity_user" "activityUsers" ON ("activityUsers"."activity_id"="t"."id") WHERE (activityUsers.user_id = 145)
/var/www/common/yii/yii-1.1.14.f0fee9/framework/db/CDbCommand.php(543)

Any help will be apreciate.

@nineinchnick

This comment has been minimized.

Show comment Hide comment
@nineinchnick

nineinchnick Apr 29, 2014

Contributor

@DexterEX just add double quotes in the condition around relation alias.

Contributor

nineinchnick commented Apr 29, 2014

@DexterEX just add double quotes in the condition around relation alias.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment