ActiveRecord representation of unclean database design

Archives Yii 1.0.x Discussions
1

Hello out there,

I have a very unusual ActiveRecord problem while creating a new application using the Yii framework.

Problem is, that the application has a very unclean database layout like this:

As you can see the database isn't normalized very well. In a clean database, there should be 3 tables (news, flag, flag2news), but there are only 2 tables like this ones.

Now i want to create the following model structure (only reading is necessary, writing to the database is not needed in this case):

  • News

  • Flag

A news has many flags in this case.

To achieve this, the News model should retrieve its values from table 1 and group by newsId. It shouldn't retrieve the "flagId" column. Then the relation should join on the flag table using the "flagId".

I don't know how I can force the News model to do grouping by default. Is there any option to achieve this?

The next question is how to realize the relation if there is no flagid anymore. A join to the the news table and another join to the flag table is an option. I will do it this way if it works.

But the main problem is how to do default grouping in the news model.

Maybe you can help me solve the problem.

Thanks in advance,

Matthias

Changing the database layout is not an option is this case ;)

2

I have not tried, but it could actually work using activeRecord as is (define the model saying that news has many flag and flag belongs to news).

Then try using eager loading and together(), something like this:

myNews = news::model->together()->with('flag')->findAll()

This should create a single query where grouping is not used since the flag data is retrieved as well. Then you should be able to access the news and flag data from the returned news objects (without further queries being generated)

3

I had the same idea, but this would return multiple news objects for every row in the news table. But i only want the distinct news values, join on the same table again and then join over to the flags table. Maybe this is possible using default functionality of the ActiveRecord model, but I don't want to do anything when using the models.

I simply want to call:

Any grouping and relations should be already defined in the model.

4

When you use together() Yii creates a single query. So even if the database was normalized, the result set whould contain the same news data several times. The Yii logic takes care to only create the objects once.

I think it should work, please try.