Relation queries with no intermediate table

Hi there,

I have a simple ‘tbl_category’ table which is used in a ‘tbl_users’ table.

Each user can only have one category, so I’d like to store the category id in the ‘tbl_users’ table rather than have a ‘tbl_users_category’ atable, as this seems a little redundant because there would only be one row for each user in the ‘tbl_users_category’ table.

So my layout is like this:

tbl_ category:

id       category

1        categoryA

2        categoryB

3        categoryC

...etc

tbl_users:

id       user       category_id

1        userA      1

2        userB      1

3        userC      3

etc...

Is there a way to use the relations properties in the users model so that I an pull out the correct category name, eg: UserA is in category ‘categoryA’ in the above example. Or would it be easier just to write a new function in the users model to get the category using the users model data (i.e. for userA, find category with id 1) and output?

Thanks,

Stu

Read here about BELONGS_TO relationships:

http://www.yiiframework.com/doc/guide/1.1/en/database.arr

Ah ok, I thought there had to be a foreign and primary key declared in both models to use BELONGS_TO, and as there’s no foreign key in my tbl_categories table, that wouldn’t have worked.

I’ll look into it ta!

That’s brilliantly clever!

In case anyone else stumbles upon this with the same question, what makes this work is in the User model, I add to the relations method:

'categoryList'=>array(self::BELONGS_TO, 'Categories', 'category_id'),

When I’m viewing a user, I can simply call the category of that user by echoing:

echo $model->categoryList->category;

So simple, and that’s exactly what I was after, thanks.

I have another issue though, each user has a postcode value in the tbl_users table, which is their full postcode, ie. ‘AB12 3CD’.

I also have a tbl_postcodes table which lists the first part of the postcode only and the town, so for example:

id       postcode       town

1        AB12           ATown

2        AB13           BTown

3        AB14           CTown

etc...

Is there any way to use the relations to search the tbl_postcodes table using the first part of the postcode field in the users table?

eg; if ‘User A’ has a postcode value of ‘AB13 4RT’, I need to work out that their town is ‘BTown’ using their postcode of ‘AB13 4RT’, stripping the text after the space to give me just ‘AB13’, which I can then use to query against the tbl_postcodes table and see that (in the above values), their town would be ‘BTown’.

Any suggestions that don’t involve storing the first part of the postcode in another database field?

Thanks!

Stu

AFAIK it’s not possible with Yii’s relational AR implementation. So yes, you’d have to use another column if you want to stick with AR.

Nuts!

I know I can use MySQL to join them together in

SUBSTRING_INDEX( tbl_users.postcode , ' ', 1 )=tbl_postcodes.postcode

That will match AB13 4RT to AB13 in the tbl_postcodes table, but I don’t think I can put join statements in the AR relational method.

A re-think it is!

Thanks,

Stu