Ugly ActiveQueries

Yii 2.0 General Discussions
1

Hi there!

After experimenting with relations, I’m faced with a few opportunities to write code to get data I need.

Mostly ugly solutions.

Regarding the following situation:

  • User

  • User in Group

  • Group

  • Permission in Group

  • Permission

I have to get from User all the way down to Permission with ActiveRecord/Query

I aim for an easy to read and to write solution that works without external methods or changes to the results of the Queries.

Given that I know the User ID, I have to find the groups assigned to that user, and the permissions assigned to these groups.

So something like this is not encouraged:




//first part ok

$groupIds = model\UserInGroup::find()->where(['user_id' => $id])->with('user')->asArray()->all();


// I need the IDs only to perform the next step

$groupIds = array_map(function($ids){return $ids['group_id'];}, $groupIds);


//because where does NOT allow objects or arrays that result from the first query, I have to filter out all IDs first to proceed.

$groups = model\Group::find()->where(['id' => $groupIds])->all();

Now, I can do a join to avoid the step of having IDs all over.

It would look like this:




$groups = model\Group::find()->innerJoinWith('user_in_group')->where(['user_id' => $id])->all();

Nice! I don’t even have to deal with the Ids of the Group to get to the Infos of the Groups. We are one step ahead!

But now how do I get to Permissions without using Ids again? I would have to filter out the Ids in order to get the Permissions.

Is there a better solution than that? Having only queries and no self-written functionality between each query.

Thank you for your help in advance. I really appreciate it.

Cheers,

Martin

2

Can you use multiple innerJoinWith() statements? I confess to not doing much with AR in Yii2 yet…

Or just use DAO. I think at a certain point, with this level of complexity you spend more time doing it the way you are trying to with questionable benefit. For a query with lots of input parameters it makes sense to abstract by using the query builder… but I’d honestly write raw SQL for this simple example.

Also…

I’d strongly consider abstracting this away in a view at the database level. You could collapse all of this down to a single ‘simple’ table and generate a model to read data from that table. Your queries would be simple and concise and it would be efficient on resources.