FuelPHP Forums

basicdesigns

I am using the ORM and my query comes up as an exception. Looking at it, it is trying to SELECT a table called prefix_to, I am just using normal models and have tried setting the $_table_name and that doesn't work. Any ideas as to the problem?

WanWizard

Not without a lot more information. What query are you trying to execute? How do the models involved look?

basicdesigns

I am managing to get some of the information from my query now. Looking further into it, it was to do with the names my fields were being selected AS and having a where statement in the wrong place. However, I now get a frozen object issue. Having tried all the relationship types except many-many (I'm new to the whole relational database concept, so am learning a lot as I go, I realise that the relationship is actually many-many) I realise that my table design isn't probably as simple as it could be for what I'm trying to achieve. I'm going to do a redesign and see if I still have issues - if so, I'll post back. Cheers WW.

basicdesigns

I'm still having some snags but it's almost there. I have 4 main tables, User, Organisation, Event and Address. A User can create many Organisations. An Organisation therefore belongs to one User, has one Address and many Events. An Event belongs to an Organisation and has one Address. An Address therefore belongs to both an Organisation and an Event. There is also a fifth table, Org_Meta which links to Organisation ID, which stores extra information about the organisation, such as if it has public opening hours etc. users.id has many organisations.id; organisations.id has one addresses.org_id, organisation.id has one org_meta.org_id and organisations.id has many events.org_id. events.id has one addresses.event_id. I need my query to return the Organisation details along with its Address, as well as any Events and their individual Addresses. (This will eventually need to pull out an Organisation, its Meta Data and its Address, but as I've not tried this with ORM yet I don't know if I have any problems.) Currently, I can get the Organisation, its Address and its Events. I am struggling to get the Address for the Events. I've tried all sorts of combinations but can't seem to get it working - at one point it even tried to join Events onto itself even though I thought I was using the relationship 'address'. Below is my current query code, any help would be appreciated.

$orgs = Model_Organisation::find('all', array(
   'related' => array(
    'address' => array(
     'join_type' => 'left'
    ),
    'event' => array(
     'join_type' => 'left',
     'order_by' => array(
      'name' => 'asc'
     ),
    ),
   ),
   'order_by' => array(
    'type' => 'desc', 'name' => 'asc'
   ), 
   'where' => array(
    array('user_id', '=', self::$user_id),
    // any more where clauses can go here in their own arrays
   ),
  ));

basicdesigns

The following MySQL gives me the results I am after, I'm just struggling to convert it to the ORM module.

SELECT o.name, o_address.post_code, e.name, e_address.post_code
FROM cc_organisations AS o
LEFT JOIN cc_addresses AS o_address ON o_address.org_id = o.id
LEFT OUTER JOIN cc_events AS e ON e.org_id = o.id
LEFT JOIN cc_addresses AS e_address ON e_address.org_id = e.id
WHERE o.user_id = 1;

Can anybody assist?

basicdesigns

Problem solved, this topic can be closed (if possible). It took me a few hours to find it but was blatantly obvious if I had thought about the error message a bit harder. The model the relationship was pointing to was incorrect, it was pointing to Model_Event when it should have been Model_Address. Always the silly mistakes that take ages to find!

FuelPHP Forums

Ask your question about FuelPHP in the appropriate forum, or help others by answering their questions.

Howdy, Stranger!

Categories

In this Discussion