basic MySQL query

Archives Yii 1.0.x Discussions
1

Here is my database structure (simplified):

TABLE properties

id (PK, INT, AUTO INC)

prop_vebraid (INT)

TABLE images

id (PK, INT, AUTO INC)

image_url (VARCHAR)

prop_vebraid (INT)

I am using MySQL database with MyISAM format, which is why I don’t have any foreign keys in the database.

I want to retrieve images.image_url where images.prop_vebraid = properties.prop_vebraid

But I’m having difficulty doing this. I’m setting up this relationship in public function relations().

Does anyone have any idea how I can do this and output the image url on each match?

2

ImageController.yourAction()




$criteria = new CDbCriteria;

$criteria->join = 'inner join properties on images.prop_vebraid = properties.prop_vebraid';

$models = Image::model()->findAll($criteria);

Does something like that help?

3

Hi,

Yes that looks like what I’m looking for, but it doesn’t seem to work.

I get the following error:

Syntax error or access violation: 1066 Not unique table/alias: ‘properties’

So I changed the query to ‘inner join properties p on…’

But then I get the error:

Column not found: 1054 Unknown column ‘images.prop_vebraid’ in ‘on clause’

My controller is SearchController with Property::model, this returns the tablename ‘properties’.

4

Bump

5

You should investigate Dynamic Relational Query Options on this page. That will help you. Also, it would be easier to travel from properties to images via the other table that you haven’t mentioned, the one which has verbraid as it’s primary key, even if it’s a reference table.

Then you should be able to do something like this:




Image::model()->with(array(

  'verbraid.properties' => array('condition' => 'verbraid.id = 123'),

))

As long as your relationships between (Image and Verbraid) and (Verbraid and Property) are correctly defined, this should work. I haven’t tested it tho.

6

prop_vebraid is not a primary key in any of the tables. i have an auto increment id field instead.

prop_vebraid is not unique.

7

what does it mean? that will help… :)

8

OK in actual fact all I need to do is the following:


SELECT image_url FROM images i WHERE i.prop_vebraid = $model->prop_vebraid

$model is Property.php, this returns tableName ‘properties’, so I think I need to define a relation in this model. Can anyone advise?

9

hello

what is your mysql version?

im not sure but i think it needs parenthesis on join queries. it would be:


inner join properties p on (images.prop_vebraid = p.prop_vebraid)
10

No I don’t think that’s the solution, can someone refer to my most recent post and provide the correct solution. Thanks.

11

What is the generated SQL statement being executed (check the log)? This should be some simple SQL errors (table name or column name error).

12

Column not found: 1054 Unknown column ‘images.prop_vebraid’ in ‘on clause’

13

I am asking for the complete SQL statement, not the error. You can find this SQL statement in the log (turning on CWebLogRoute for example.)

14

That’s all I want to do - can someone tell me the appropriate code to make this work in Yii?