Love Fuel?    Donate

FuelPHP Forums

Ask your question about FuelPHP in the appropriate forum, or help others by answering their questions.
How do i order an orm query by a relationship?
  • stefhornerstefhorner
    How do i order an orm query by a relationship?

    take the following example:

    i have a Model_Suggestion, Model_Vote, and Model_User

    Model_Vote belongs to Model_User and Model_Suggestion
    Model_User and Model_Suggestion can have many Model_Vote s

    i need to get the 10 ideas with the most votes.

    My first idea would be something like the following:

    Model_Suggestion::find(
    'all',
    array(
    'related' => array(
    'votes' => array(
    'order_by' => array( 'votes', 'DESC' )
    )
    ),
    'limit' => 10,
    )
    );

    But this obviously spits out the error that the 'votes' table has no column 'votes' to order by - i want to order by the suggestion with the most votes. is there something else i should be putting in the order by part?

    Model_Suggestion::find(
    'all',
    array(
    'related' => array( 'votes' ),
    'order_by' => array( 'votes' or 'related' ... etc. ),
    'limit' => 10,
    )
    );

    won't work, as that tries to order by the 'votes' or 'related' column in the 'suggestions' table.

    I guess what i'm asking is - can i do this (order by number of related values) with the ORM, or am i going to have to craft a DB query?!
    (the latter's a perfectly fine answer - it just seems like this should be possible using an ORM query)
  • HarroHarro
    Accepted Answer
    You need a count query for that, which you can't do with ORM, it's not a query builder.

    What you could try is something like:
    $result = DB::select(DB::expr('*'), DB::expr('COUNT(*) as count'))->from('votes')->group_by('suggestion_id')->order_by('count', 'desc')->limit(10)->as_object('Model_Votes')->execute();
    if ($result)
    {
        foreach ($result as $vote)
        {
            // access the suggestion from the result vote record
            echo $vote->suggestion->name;
        }
    }

    (disclaimer: from the top of my head, not tested!)
  • stefhornerstefhorner
    off the top of your head?!
    that works perfectly!
    thanks, harro.
  • HarroHarro
    Cool! You're welcome.
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