Working with JOINed result set? (Mvc\Model)

Question

Working with JOINed result set? (Mvc\Model)

ren Nov '13

Created Nov '13	Last Reply Nov '13	Replies 1	Views 445	Votes 0

ren 8.1k

Nov '13

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!

Phalcon · Answer 1 · 2013-11-08T11:50:25-07:00

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

ren · Answer 2 · 2013-11-11T12:30:13-07:00

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.