Love Fuel?    Donate

FuelPHP Forums

Ask your question about FuelPHP in the appropriate forum, or help others by answering their questions.
ORM relationship conditions
  • I'm having an issue fetching relationships in a model. It seems to be a typing issue. I'll try to explain as best as I can.

    I have a site model that contains a list of sites. Each site can have one image and one video. I setup these relationships in the $_has_one property. They work great in code if I call $site->image or $site-video. The site model returns the proper relationship objects. The image and video models both have their own table, but they use a composite key comprised of the entity (varchar), entity_id (int), language (varchar) and the delta (int).

    The issue occurs if I try to do a site query and add the relationships in the query. For example, if I use the following code: $site = \Model_Site::query()->related('video')->where('id', '=', 1)->get_one(). This query will throw an error. I looked at the query and noticed that Fuel is trying to fetch the related data from the delta column as a string and not an integer as specified above. This is what's throwing the error as the delta column does not contain any string values. Again, if I take out the related part from that query and then call the relationship object, it works fine as it runs as a separate query and uses the proper type casting. I should also note that my models are using the typing observer.

    Another thing to note is that if I try to use a NULL condition in my relationship in the model, Fuel doesn't run IS NULL in the related query above either. If I take out the related method from the query and then call the related object separately, Fuel properly checks for IS NULL.

    So this leads me to believe that there's a type casting issue when using the related method on query calls. Fuel tries to fetch everything as string values even though the model columns are specifically setup as INT and the typing observer is enabled on the models.

    If anyone can provide any insight on this, it would be greatly appreciated. Maybe there's a setting or something that I'm overlooking. I'd really prefer to have the related items in the query to drop the overhead on the amount of database calls for my project.

    Thanks in advance.
  • String vs Int is not an issue, a lot of the PHP drivers (notably MySQL) convert to string. If it is really a type issue, you would see a RDBMS error to that effect (PostgreSQL for example does this).

    You have defined your relationship properly: sites has_one videos, videos belongs_to sites? And not has_one on both sides of the relation?

    You can't use NULL in a relationship condition, as NULL is not considered a valid value, NULL is the indication of the absense of a relation.
  • Hi Harro,

    Thanks for the super quick response as usual. :)

    Regarding the has_one and belongs_to, I only have the has_one property setup in the site model. I cannot use the belongs_to property in the video model as it belongs to different entities, not just sites.

    I figured out how to resolve my issue. For my relationships to work properly, I have to use DB:expr() on my condition values in order for Fuel to recognize my values as integers instead of strings. The problem with this is that you cannot use functions/methods directly on the condition properties in the model, so I had to use an _init() method and set these conditions manually using DB:expr() there.

    I am also able to use my NULL condition as well. I had to use this code to make it work though: static::$_has_one['video']['conditions']['where'][] = ['descriptor', NULL, \DB::expr('IS NULL')];. After adding this to my _init() method, my related queries work like a charm now.

    Again, I have to note that this only affects ORM queries when using the related() method. If I query and get from the site model first and then call the video relationship from the site object ($site->video), the model conditions work just fine without needing the _init() method. This issue only occurs when using the related() method in the model query.

    Long story short, there's something happening in ORM's related() method that's not type casting the conditions correctly. I can post the setup of my models if you'd like to try this out.

    Again, thank you for your feedback.
  • Harro VertonHarro Verton
    Accepted Answer
    You MUST always define a relation from both sides, if you don't, you get all sorts of weird errors, based on the type of query you run. And one side (the side that has the foreign key) of the relation MUST always be a belongs_to (except for a many_many relation).

    As a relation is defined strictly between two models, I don't see why you can't define the belongs_to. We we models with dozens of relation definitions, you can even define a relation from a model to itself (to create parent->child tree's).

    So you really need to address this.

    As for your other issues, I really need to see code (models, database migrations for the related tables and an example controller that shows the problem) to determine if you have encountered a framework issue that needs adressing. At the moment it is not clear to me what you are exactly doing.

    If you can setup an app with the required code (only what is needed), zip it's fuel/app, and email to me ( wanwizard<at>fuelphp.com ), I can have a look...
  • I usually define a belongs_to property in the opposing model. However, in this project, I have tables that handle videos and images for multiple entities. For example, sites have images, users have images, etc. These are all stored in the image table which has an entity column in which I specify if it is a site or user. Therefore I cannot have a belongs_to relation in this table as it can belong to several different entities (other models). But I can assure you that this isn't an issue on my end. I never call relationships from the video or image models. They are always called from other models.

    I'll see if I can whip up a quick version of this and will send it over to you to check out. I really think something is going on in the ORM code where it's not converting the database conditions from the model properly. I will keep you posted.

    Thanks again for your feedback. It's greatly appreciated.
  • Then you have a database design problem. 

    A key always needs to be unique within the relation. If you have multiple relations to the same information, use a compound key to make the key unique, and use the condition in the relation to place a filter on the relation to make sure you only see the correct records.

    ModelA { PK:id }
    ModelB { PK:id }
    Videos { PK:id, type, FK:type_id }

    ModelA => Videos, relation on id => type_id, condition type = "A"
    ModelB => Videos, relation on id => type_id, condition type = "B"

    This way you have a correctly normalized database design, a re-use of the Video table, a separation of Video's of different types, and relation conditions to make sure you only fetch the correct related records.

    The requirement of a correct relationship definition is not related to the way you create the query. Based on the operation performed internally the relation object uses both the Model_from and Model_to data. If one is missing, it will try to use defaults (which are also used when you don't define relation properties) but those defaults will not work for non-standard relations like yours.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

In this Discussion