We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.

Working with JOINed result set? (Mvc\Model)

Hi there,

Regarding working with models;

Say I have a table Message and a table User

I want to display all messages for an user. A Message has 2 references (FK's) to User ('to' and 'from')

I want to display all Messages in a table for the current user, displaying the username of the User that sent it. I want also be able to sort on this 'from' column.

I guess something like this is not easily and/or efficiently done using models? Because when using models, it seems it's only possible to get stuff in multiple steps? (It doesn't actually perform JOINs?)

So when I iterate though all the messages, every time I do $user->UserFrom->username ('UserFrom' being the relationship alias) it will do a new query, unnecessary (in this case) and quite inefficient of course.

Am I missing something here? Or is this something that might be possible in future versions?

I guess in cases like this it is better to not use models, and go for PHQL instead?

(Let alone, it seems to be impossible to sort on a column of a related table using models?)

Thanks!



98.9k

You can use relationships to easily retrieve the related records from existing instances based on their relationships. https://docs.phalcon.io/en/latest/reference/models.html#relationships-between-models



8.1k

Thanks. I've gone through all that, but if I'm not mistaken, it doesn't allow me to do what I'm describing in my post?

So now I solved it by using the Query Builder, left-joining my related table and manually setting columns, with an alias for the column from the related table I want to display and be able to sort on.