Love Fuel?    Donate

FuelPHP Forums

Ask your question about FuelPHP in the appropriate forum, or help others by answering their questions.
Select one many to many per item
  • Hi

    I just discovered fuelphp few weeks ago, and i read the entiere doc but there are some points i didn't find the answer yet.

    The question who brings me to you today is the following : With the ORM package and a many to many relationship (to make it easier, let's call it Model_Post, and Model_Author), assuming this query :

    $posts = query()->limit(10)->get();

    So i get 10 posts, with each post->author who contain every author linked in the table_through

    Now if i want select several posts, but for each post, juste get the first author, or the two firsts, ect ? Or even select all field in the table but not related items ? I don't see well how to manipulate related fields... (My concern come with the fact i have some models with a lot of related items, and in some point of my app i don't  want load each join when it's not necessary) 

    I must add i didn't find this out by searching the web. Thank for helping and please excuse my so bad english.
  • HarroHarro
    Accepted Answer
    That query will not fetch any author information, since you haven't specified it should. However, ORM supports lazy loading, so as soon as you access the relation ( ->author ) on a Post object, the ORM detects it doesn't have them yet, and will fire a query to get them.

    If you enable DB profiling (enable the profiler in your app config, and then enable DB profiling for the DB configuration you want to profile), and you'll see the exact queries generated. You'll see it won't contain any join. If you need relation data, the join is usually faster then fetching individual data later, so use:

    $posts = Model_Post::query()->related('author')->rows_limit(10)->get();

    Note that if include relations, you have to use rows_limit() instead of limit(), because by default ORM only selects complete result sets. If you use limit here, and the first post has 8 authors, and the second 4, your resultset would only contain 8 records, and not 10, since it will refuse to load the second post with only 2 of the 4 authors. rows_limit (and rows_offset) will disable this behaviour.

    Since ORM is an object mapper and not a query builder, you can not use it to run complex queries, group_by's, etc. Those would produce incomplete results, or results that have no relation to any model at all, and can therefore not be stored. Use DB calls instead if you need query building.

    You can add methods to your model to encapsulate the DB queries, so all your database access is still in a single class, and not littered all over your controllers...
  • Thanks for your answer, i have a clearer picture of this ORM. But just 2 things : Create method with specific query isn't a different behavior in comparaison of PHP application without framework. So you're telling me that an ORM is limited for every needs a little specific ? It's not disturbing me, it's just the first time i use a framework, and i wanted to be sure that complexes relationals objects could not be a heavy charge.

    I made more tests and you're totally right, objects related aren't load before being called, great.

    And else, is it possible to get just the first related author on a post, for example, or it require to build a specific query ? Because if i understood correctly, the lazy load will trigger a query for getting all related author if he find a "$post->author" ?  
  • HarroHarro
    Accepted Answer
    An ORM gives you object oriented access to database data.

    This implies that the resultset from a query must be compliant with the model's definition. So you can't just run any query and return whatever result is produced as model objects, the ORM simply wouldn't know what to do with it. For the same reason, the primary key of a record MUST always be part of the resultset.

    An ORM is absolutely not a query builder, which is an object oriented interface to generating SQL. A query builder just runs a query and returns a resultset, as is.

    In general, you use an ORM in interactive operations (like CRUD screens), to present lists, and to access related data in an easy and consistent manner.

    If you want to run complex queries, with multiple joins, group_by's, subqueries, etc, you'll end op with rows in the resultset that don't resemble any table in your database. Since an ORM model always maps to a table (and the columns in that table), the ORM can't deal with the result of such a query.

    You understand it correctly that loading a relation (lazy or not) will fetch all related records, and will create model objects for those records. If you write out the SQL you would need to fetch all posts with only the first (what defines "first"?) author, you'll see how complex that is.

    And perhaps you have to look at the design of your database schema. Clearly, this "first" author has some significance. Otherwise you would not need it. If this author is significant (perhaps it's the owner of the post, and others are collaborators?), then from a design point of view you will have two relations between Post and Author. The owner (which is a one-to-many), and collaborators (which is a many-to-many, and can include the owner).
  • Yeah, i didn't really thinking to the implication of the fact the ORM build object with extracted data. Now i assume perfeclty the limit of a such system. Thanks.

Howdy, Stranger!

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

In this Discussion