Love Fuel?    Donate

FuelPHP Forums

Ask your question about FuelPHP in the appropriate forum, or help others by answering their questions.
Querying a query. How does it work?
  • zechdczechdc
    Does ORM models work like this:
    Each call to a Model returns an SQL result set, and each subsequent call to the same model will be run against the previous result set? An example might be helpful, see below for more details. 

    $data = Model_Course::query() 
       ->where('course_id', 5) 
       ->get();

    Model_Course::query()
       ->related('students')
       ->where('students.grade', 'A')
       ->get();

    Here are some assumptions I have made about how the above code works behind the scenes. Please correct me. 
    1) The first call to Model_Course creates an SQL statement that returns all courses with the course_id of 5.
    2) The second call to Model_Course creates an SQL statement that gets run against the returned results of the first Model_Course call.

    So in this example:
    - If no students receive an 'A', then the $data object will contain the details of a single course
    - If any students receive an 'A', then the $data object will contain the details of a single course plus the details of every student students that received an A. 

    With this logic, I could continue to make calls to Model_Course to further refine my result set. For example, once I have a list of students that received a 'A', I can make another call to limit it to students with the name 'Mike'. 
    Question:
    Is this how it really works? Each call to a Model returns an SQL result set, and each subsequent call to the same model will be run against the previous result set?
  • HarroHarro
    No, the two examples are non-related queries. If you want to relate then, you have to split them up:

    $query = Model_Course::query() 
       ->where('course_id', 5);

    $data = $query
        ->get();

    $students = $query
       ->related('students')
       ->where('students.grade', 'A')
       ->get();
  • zechdczechdc
    I'm confused by the query results. When I run the example above, I get all 'Courses' and all related 'Students' for each course. More importantly, I get all 'Courses' even if no students are related to it. 

    However, when I run the following code, I only get 'Courses' with 'Students' related. It seems like these two queries are in fact affecting each other. 

    $data = Model_Course::query()
       ->related('students')
       ->where('students.grade', 'A')
       ->get();
  • HarroHarro
    This query has a WHERE clause on students, so obviously it will never return courses without students, as there WHERE clause will be false?

    If you don't put the WHERE in, you say "give me all courses, and all students who registered for them". In which it is logical that you also get courses without students.
  • zechdczechdc
    That makes sense. What is confusing me, is the original codes gives me the expected results (described in the first post). When i try to combine the two statements it no longer works.

    $data = Model_Course::query()
    ->related('students')
    ->where('course_id', 5)
    ->where('students.grade', 'A')
    ->get();

    It doesn't work in the above because if no students are associated with a course, then the course will not be returned, I need the course returned no matter what. And I need only students with grade 'A'. What might be confusing in this example is 'course_id', a more accurate name would be 'course_description' or something similar. In the above example, multiple courses have a course_id of 5. It should be returning multiple courses, each with all the students that have 'A's, and still return the course even if no students are associated with it or have 'A's.
  • HarroHarro
    If it makes sense, why is it confusion you? ;-)

    You say "give me all courses with all their students, which have a course_id of 5, and students with grade A". Courses without students don't have students with grade "A", and are therefore not included.

    You want "give me all courses with all their students, which have a course_id of 5, and students with grade A, or with no students at all". Which is a different question.

    The ORM does a LEFT OUTER JOIN by default, so without the WHERE clause on students.grade, you would get all courses, even those without students. It's the WHERE that filters the result to those courses with students with grade "A".

    I don't have a quick answer for you, ideally you would include a subquery to replace the WHERE, which you can try, but support for that is limited. For me it always helps to code the SQL I want, then try to convert that to ORM method calls.
  • zechdczechdc
    Horro Verton:
    You say "give me all courses with all their students, which have a course_id of 5, and students with grade A". Courses without students don't have students with grade "A", and are therefore not included.
    My response:
    Yes, this logic makes sense. However, by using the code in the first post you can actually get these results "give me all courses with all their students, which have a course_id of 5, and students with grade A, or with no students at all", which is what confuses me... because you say that is not possible? 



    Horro Verton:
    You want "give me all courses with all their students, which have a course_id of 5, and students with grade A, or with no students at all". Which is a different question.
    My response:
    Yes, this is exactly my question. Sorry I haven't made that very clear. Again, what confuses me is that the code in my first post does exactly this. Why?



    Horro Verton:
    The ORM does a LEFT OUTER JOIN by default, so without the WHERE clause on students.grade, you would get all courses, even those without students. It's the WHERE that filters the result to those courses with students with grade "A".
    My response:
    Ok. So how do I implement the WHERE statement to get all courses and limit the results based on students.grade?



    Horro Verton:
    I don't have a quick answer for you, ideally you would include a subquery to replace the WHERE, which you can try, but support for that is limited. For me it always helps to code the SQL I want, then try to convert that to ORM method calls.
    My response:
    Ok, I will give this a try and post the results. Still confused about why my original code produces the desired results. 


  • HarroHarro
    Now you're confusing me. If it works, what exactly is your question?
  • zechdczechdc
    I believe my question is pretty clear in the first post. I have two queries and they work, but I don't understand why. I was hoping to understand what I'm using so I'm less likely to make mistakes and can actually take advantage its potential. I have read the ORM documentation multiple times, but it doesn't seem to mention this situation. 
  • HarroHarro
    Must be my mistake then. I tought you said one worked and one not, and wanted to know why.

    If you want to see what happens behind the scenes, use DB::last_query() to dump the query constructed by the ORM, so you can see the SQL that was generated.

    Alternatively you can enable the profiler in your app config, and DB profiling in your database configuration, to see all queries.

  • sagikazarmarksagikazarmark
    Accepted Answer
    Well, I think I understand the question (as I have ran into something similar before), and I hope I can give you some answers with some examples.

    First of all let's see these queries:


    $data = Model_Course::query() 
       ->where('course_id', 'in', array(5,6,7)) 
       ->get();

    $data2 = Model_Course::query()
       ->related('students')
       ->where('course_id', 5)
       ->where('students.grade', 'A')
       ->get();

    $data3 = Model_Course::query()
       ->related('students')
       ->where('course_id', 5)
       ->where('students.name', 'Mike')
       ->get();

    When you look at $data, you will find it will contain all registered students who recieved A !OR! is called Mike. As you can see the big OR, this is not a filtering of the previous result. The answer is: Orm (and maybe CRUD as well, I don't know) caches the results as objects and actually this is the answer, because the cached objects are stored by references, so it appears in your previous result set.

    Orm does not store the same model twice, so when you query some data, and later you want to query the same, you will get the earlier cached data. This also means that relations are also set on the cached data, this is why you can see new data in the in the "old" one. This behaviour can be turned of by calling from_cache(false) on the query object or immediately save your model result as an array. But as you can see, your resultset of the queried model is the same, it
    never changes, you only get the related models as an "extra". This is
    why you shouldn't query the same model with relations multiple times.
    (BTW: I am not sure whether it is the expected behaviour or not, but it
    works like this).

    Also, your case (give me all courses with all their students, which have a course_id of 5, and students with grade A, or with no students at all) can be solved in a better way: pass your "students with grade A" condition to the related method, which will move the condition from where close to the join close. This way you get all the courses with id 5, and the joined data will only contain students with grade A.


    I hope this helps.

  • HarroHarro
    But caching is per individual object, on primary key.

    The ORM retrieved from cache after the query has run, it should not produce incorrect results.

    Re-reading the initial post again, this is where it goes wrong:

    2) The second call to Model_Course creates an SQL statement that gets run against the returned results of the first Model_Course call.

    No, the second call is a NEW query, it has no relation at all with the previous one.

  • zechdczechdc
    @sagikazarmark
    Thanks. So caching is why my query performs the way it does. That makes more sense than my initial assumptions about querying a query. 

    Your answer makes sense, but it took awhile to implement. The documentation doesn't make it obvious. 

    I will post my solution in another post because this forum won't let me post more than X number of characters :( 


  • zechdczechdc

    Solution

    Original ORM query:
    $data = Model_Course::query() 
       ->where('course_id', 5) 
       ->get();

    Model_Course::query()
       ->related('students')
       ->where('students.grade', 'A')
       ->get();

    SQL Produced by Original ORM Query:
    SELECT * 
    FROM courses
    LEFT JOIN students ON courses.course_id = students.course_id 
    WHERE user_id = 39
    AND students.grade = 'A'

    Revised Working ORM Query
    $data = Model_Course::query()
        ->related('students', array(
            'join_type' => 'left',
            'join_on' => array(array('grade', '=', DB::expr('A')))
            )
         )
        ->where('course_id', 5)
        ->get();

    SQL Produced by Revised ORM Query
    SELECT * 
    FROM courses
    LEFT JOIN students ON courses.course_id = students.course_id AND grade = 'A'
    WHERE course_id = 5

    I will explain how I came up with this solution in my next post, because this forum limits the size of a single post :( 
  • zechdczechdc
    Solution Continued
    Continuation of Solution post above (Nov 19, 2013 at ). 

    Unfortunately, I found very little documentation on how to create such a query. I will try to explain what documentation I did find, and how it was pieced together. 

    If you look at the "Join Types" section of ORM > Relating Models > Introduction  you will see a query that looks similar to the above, except it uses ::find instead of the query() chaining. 

    In order to make it work with the query() chaining, I found some code under the "All Selection methods when using find" section on the ORM > CRUD page. If you take a look at the "related" method, you should see an example of a WHERE statement being placed on the relation, like so:

    // include conditions on the relation (only supported when chaining) 
    Model_Article::query() 
         ->related('author', array('where' => array(array('active', '=',1))));

    However, this does not produce the desired results. It instead produces the same SQL that my original ORM query produced. The above statement is equivalent of using the following code (not sure why there are two ways to do it):

    Model_Article::query() ->related('author')
    ->where('author.active', '=', 1);

    To go from this to my final query, I just guessed until it worked. If there is documentation for this, I was unable to find it. I have read and re-read the ORM documentation a few times now and it is hard to follow. I hope this helps someone else. 

  • HarroHarro
    The ORM documentation is unfortunately very sparse, in part caused because the ORM designer is no longer with the team, and his code is *very* complicated.

    If you have suggestions on how to improve the documentation, please let us know. Or even better, fork the fuel/docs repository, improve the docs, send a pull request, and go into history as a FuelPHP contributor! ;-)
  • zechdczechdc
    Thanks Harro. I might just do that. :) 
  • sagikazarmarksagikazarmark
    I agree with both of you, ORM doc is very light. But there are lots of things in it, you just have to know where to find. This is also a problem.

    Despite query() is the recommended way, most of documentation is about find(). I will also contribute, if I have some time.
Add a Comment

Howdy, Stranger!

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

Sign In Apply for Membership

Categories

In this Discussion