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

Get all results from 3rd level related tables

I'm trying to get the entries of a table that is related to the primary table. I'll be honest that i'm not convinved that I'm going about this the right way and as such I'm getting an error that I'm finding little or no information on what I'm missing.

I have three tables: Customers, Contacts and Communications. The Communications table has the FK of contactId. Contacts has the FK of customerId. I'm making use of the related tables setup in the models with the following code in the Customers model:

$this->hasManyToMany('id', 'CrmContacts', 'crm_customers_id', 'id', 'CrmCommunications', 'crm_contacts_id', array('alias' => 'CrmCommunications'));

This is generally fine and I can get access from the Volt template, but I need to add some limits and change the order of the communications table results.

As such I'm trying to do the following in the controller:

    $profile = Customers::findFirstById($id);
    $communications = $profile->getCrmCommunications(['order' => 'date']);

This however results in the following error message:

Can't obtain model's source from models list: 'CrmCommunications', when preparing: SELECT [CrmCommunications].\* FROM [CrmCommunications] INNER JOIN [CrmContacts] ON [CrmContacts].[id] = [CrmCommunications].[crm\_contacts\_id] WHERE [CrmContacts].[cm\_customers\_id] = ?0 ORDER BY date

Can anyone provide some information on what is missing with the model's source and what i can do to correct the issue? Furtthermore, if you think i'm going about this incorrectly, then please let me know what you would change.

As always, any support is greatly appreciated. Many thanks in advance.

What version are you using?

@Andres: I'm using Phalcon 2.0.3, with PHP 5.6.3 and Apache 2.4.10

Any thoughts by anyone?

As an update, I've updated Phalcon to 2.0.5 and the issue remains :(

I'm beginning to consider doing things in a different way and remove the many-to-many link and have the communications table as more generic. However, it would be nice to know what is going on with the current code :)



13.3k
Accepted
answer
edited Jul '15

Just stumbled across a "solution" to the problem here:.

Apparently I needed to wrap the order by field table name in square braces i.e. $profile->getCrmCommunications(["order" => "[CrmCommunications].date DESC"]); and that works.