Solved thread

This post is marked as solved. If you think the information contained on this thread must be part of the official documentation, please contribute submitting a pull request to its repository.

Can't get hasMany relation to work

Hello,

I am struggling in getting my first table relations working in Phalcon.

Studying the model relations in the documentation I've put together my first relations:

Topic model:

    public function initialize()
    {
        $this->hasMany('id', 'Multiple\Forums\Models\Post', 'topic_id');
        $this->belongsTo('category_id', 'Multiple\Forums\Models\Category', 'id', array('foreignKey' => true));
        $this->hasMany('id', 'Multiple\Forums\Models\TopicViews', 'topic_id');
    }

Post model:

    public function initialize()
    {
        $this->belongsTo('topic_id', 'Multiple\Forums\Models\Topic', 'id', array('foreignKey' => true));
        $this->belongsTo('user_id', 'Multiple\Forums\Models\User', 'id', array('foreignKey' => true));
    }

All the models are correctly in the Multiple\Forums\Models namespace.

The relation itself:

  1. A topic can have many posts as well as have many views (view counter)
  2. A topic belongs to a forum category under which the topic is listed
  3. A post belongs to a single topic and a single user

Now I have a function called getParticipants in my Topic model which gets the users that have taken part in the topic conversation (posts):

    public function getParticipants()
    {
        $users = array();

        foreach($this->post as $post){
            $users[$post->user->id] = array(
                'last' => false,
                'image' => $post->user->userDetails->image == null ? 'img/user/defaults/'.rand(1, 20).'.png' : $post->user->userDetails->image,
                'username' => $post->user->username
            );
        }

        $latest = Post::findFirst("topic_id = ".$this->id, array(
            'order' => 'created_timestamp DESC'
        ));

        $users[$latest->user->id]['last'] = true;

        return $users;
    }

I think I'm doign the relations wrong because when I do $this->post, which should get me all the posts that belong to that topic, but instead I get

Notice: Access to undefined property Multiple\Forums\Models\Topic::post

I would really appreciate if someone could explain me how the model/table relation are supposed to properly work.

Thank-you in advance.



32.6k

Hey

In model Topic you call $this->post this mean post is element in Model Topic, you should replace $this->post to

$this->post to new Post::find()
edited Feb '15

Thank-you on the reply.

I gave it a try but unfortunately it is now giving an SQL error now:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'post.?id' in 'field list'

Where is it getting that ? there? I checked my id column in both my tables and the models and there is no ? there.

Just in case a little on the flow how the function is called: I have a volt template of a topic listing page. There it loops through the topics resultset and then calls it like topic.getParticipants() in the template. At this point I assume that when I call the function in the Topic model like so, it should have the current result bound to the Topic model (e.g. if I were to call $this->id it would give me the current Topic ID). Is this correct so far?

The end result of the function would be to return an assoc array of users as ID => data. This would mean walking through a long relation (e.g. $post->user->userDetails->image where Post belongsTo User and User hasOne UserDetails).

Will Post::find() still work in this case when the current Topic from the resultset is bound to the model? Or would a different approach need to be done for this to work?

If this is the right way then how would I achieve the similar thing for user and userDetails in the same function?

Apologies if I didn't clear the flow of it in my first post.

Cheers, Karl

edited Feb '15

I gave a try with using aliases in models to see if that would solve the issue.

$this->hasMany('id', 'Multiple\Forums\Models\Post', 'topic_id', array('alias' => 'posts'));

Now if I was to run $this->posts it results in a mixed parameters SQL error:

SQLSTATE[HY093]: Invalid parameter number: mixed named and positional parameters

And this is the SQL that gets executed (after turning on low-level SQL logging)

SELECT `post`.`?id`, `post`.`text`, `post`.`user_id`, `post`.`topic_id`, `post`.`created_timestamp`, `post`.`updated_timestamp` FROM `post` WHERE `post`.`topic_id` = :0

It seems that the mixed are ?id and :0 but I don't understand why they are being mixed?

Update

I set up an alias for topicViews and tested $this->topicViews which is also a hasMany relation to Topic and that one works correctly:

SELECT `topic_views`.`topic_id`, `topic_views`.`ip_address`, `topic_views`.`changed_timestamp` FROM `topic_views` WHERE `topic_views`.`topic_id` = :0

But why is the Post relation putting post.?id instead of post.id?

First off, let me commend you on your very well written post. It was very easy to follow & understand, though I fear that may not help you get your solution any faster.

post.?id is indeed messed up - I have no idea why that's happening.

I wonder if case is the problem. Perhaps your Topic model has set up the relationship using capital-P "Post" rather than "post"? Try removing the alias and referring to the related model with "Post", and see what happens.

edited Feb '15

Hi and thank-you for your reply on the feedback :)

I hope I understood you correctly and mean replacing $this->posts with $this->Post (after removing the alias ofcourse)?

I gave a try with $this->Post but sadly it results in the same error as in the initial post:

Notice: Access to undefined property Multiple\Forums\Models\Topic::Post

As wierd as it would sound, I even tried renaming my Post model and the relations to a different name like PostSmt and then set the source to the post table but that didn't work too.

Since I have a multi-module app I removed the Post and Topic models from my other module thinking that might cause it but no luck there as well. Then I tried removing the primary key and auto_increment from post.id in the database table but that didn't work as well. I also tried recreating the table in the database too.

I literally can't think of anything else that might cause it.

Try

1) removing the "foreignKey" declaration, from the Post::belongsTo declaration. It may be nothing, but I've seen other inexplicable problems fixed by removing that declaration. That declaration isn't necessary for the cascading delete & update in the database to work anyway.

2) Re-adding your alias to the Topic::hasMany declaration. It may be necessary since your model is namespaced.

If neither of these has an affect, then I'm at a loss too.

Oh, and substituting $this->post for Post::find() is nonsense. Post::find() will return a resultset of all posts - not at all what you're wanting.

edited Feb '15

I removed every foreignKey declaration in Topic, Post and TopicViews and added back my aliases but it didn't nudge at all.

Just in case I tried going through the Topic result inside $this and it doesn't have any reference on ?id. So I'm basically at a standstill as well. I hope someone might know what the issue is.

(I'm starting to think this is a hidden feature :))

Have you done anything with the model meta data?

I'm tagging @Phalcon - maybe he'll have some insight.

edited Feb '15

None that I'm aware of no. I just made the models as usual, set the namespaces, added relations and thats all. As much as I saw when walking through $this, the meta data there seemed normal to me.

Probably unrelated, but you spelled "Forums" wrong in one of the relations in Topic: Multiple\Froums\Models\TopicViews

edited Feb '15

Ah yes apologies about that, I had it fixed in my code but forgot to update it in the first post. Updated now.

Update

I was trying to fetch every post in another location and doing Post::find() doesn't work as well. It gives me an unknown column post.?id. It seems this issue goes even deeper than mere relations?

It seems the issue is indeed in the metada. Apologies for saying otherwise because then I wasn't aware of the function I found in the docs.

I found that in the class Model\MetaData\Memory() function getAttributes() that it has ?id in the meta data.

Full output:

Array
(
    [0] => ?id
    [1] => text
    [2] => user_id
    [3] => topic_id
    [4] => created_timestamp
    [5] => updated_timestamp
)


2.0k
Accepted
answer
edited Feb '15

I have it solved!

The issue was not in the metadata, nor the models or relations. The issue was in the MySQL table.

I started thinking that what if I used something else besides id. For example pid. In MySQL Workbench I modified my table and add a p before id (Note: I didn't clear the whole column, I just prepended p). I did all the changes to the model as well.

Now if I ran Post::find(), it failed again but this time the SQL generated had post.p?id.

So that got me thinking, it has to be a funky i!

Back in my Workbench I fully erased the column and retyped id and saved the changes. In my model I reverted the changes as well and voila, it works. And so does my relation $this->posts.

When I recreated the table (I was trying this in an above post) I copied the create statement to clipboard. Guess it copied the funky i along.

If a normal i has an ASCII code of

105

Then my funky i was

226 129 175 105

Thats what the ? mark was about, it was some wierd character that Phalcon just couldn't interpret. But since the ? was in the SQL statement, it started processing it as a positional parameter.

So note to everyone in the future When you have ? marks in your SQL query columns, rewrite your table column name with a proper keyboard layout that has a proper i (Though I'm still thinking how could have I typed that funky i since I usually use the ENG/US keyboard layout) :)

Success! Glad to hear you got it figured.