Solved thread

This post is marked as solved. If you think the information contained on this thread must be part of the official documentation, please contribute submitting a pull request to its repository.

Find() and order by field from related model

Hello!

I'm working on a search, order and paginate option for my project and this all works ok. But i have a one question.

Is it possible to order on a field from a related model?

For example: i have an object called Robot, and each robot has one Manufacturer, they are related with IDs, is there a way to sort on the manufacturers name with use of Robos::find() ?

Robots::find(array('order', 'manufacturername DESC'));

I can't find anything in the documentation or on the forum about this. I know i can do this with raw SQL queries and joining the manufacturer table but i would like to avoid that.

Thanks in advance!

I don't think there is with just find(). At least with PHQL, your result set can still be a collection of Robots, so you don't need to instantiate & populate a bunch of objects.



10.6k

Hmm ok, thanks i was already afraid of this.

Would be cool if you could sort on the values of the object inside your collection. Not sure how heavy this is for the server but for example if have a few of these fields like the examle above and also some calculated fields the sorting query is going to be 'complex'.

You can sort on values of the object, but you're wanting to sort on values of a related object.



10.6k
edited Mar '14

yes indeed, that was what I meant.

Isnt it possible to do the following in this case: and than order on this field?

Model:

    class Product {
      public $manufacturername

      public function afterFetch()
      {
          $this->manufacturername = $this->manufacturer->name;
      }
    }

Edit: this does not work with find() because it runs a query and it can't find the manufacturername field (ofcourse). Btw, I see that there is a 'filter' option in a resultset but not an order/sort option (http://docs.phalconphp.com/en/1.2.6/api/Phalcon%5Mvc%5Model%5Resultset.html)

edited Mar '14

You can do anything always ;)
I don't know what your models are, but shape it for your use, should work

class Robots extends \Phalcon\Mvc\Model
{

    public function find_extended($parameters)
    {
        $robots = self::find($parameters);
        foreach($robots as $robot)
        {
            $manufactures = Manufactures::find(array(
                    "robot_manufacture = {$robot->id}",
                    "order" => "manufacture_name"
                ));

            $robot->manufactures = $manufactures;
            $new_collection[] = $robot;
        }

        return $new_collection;
    }    
}

You can use it like this

class IndexController extends \Phalcon\Mvc\Controller 
{
    public function indexAction()
    {
        $robots = Robots::find_extended(array(
            "robot_generation = 'steel'",
            "order" => "robot_name ASC"
        ));

        foreach($robots as $robot)
        {
            print_r($robot->manufactures->toArray()); //your manufactures should be sorted
        }
    }
}
edited Mar '14

or you can try something like this:

class Manufactures extends \Phalcon\Model\Mvc
{
    public function find($parameters)
    {
        $parameters['order'] = "name ASC";
        return parent::find($parameters);
    }
}

Try one of this solution and reply if it works.

edited Mar '14

@kaphert you can join tables using Robots::query() aka Phalcon\Mvc\Model\Criteria or using Phalcon\Mvc\Model\Query\Builder:

$robots = Robots::query()
    ->columns("Robots.id")
    ->leftJoin("Manufacturers") //This should work without a condition if you defined a relation between Robots and Manufacturers
    ->order("Manufacturers.name")
    ->execute()


10.6k
edited Mar '14

@Karol Marcinkiewicz that is indeed a solution. But not sure if this is flexible enough in my case @maxgalbu this is also an idea! And pretty straightforward

My idea is to make it flexible to sort on all possible fields of the model or related models and search in all these fields. Not that I need to sort/search on ALL of the fields but it is nice to have the possibility of doing it, right? I was hoping phalcon was going to help me a little bit but i'm afraid it is going to be a little bit more extra work :-)

I'm going to try both solution tomorrow and see what works best in my case.

Thanks a lot for now !

edited Mar '14

It is hot idea, you can beautify it or extend as you wish. As parameter to find is always array() you can push there option which you use fo what you want.

Using in Controller

class IndexController extends \Phalcon\Mvc\Controller
{
    public function indexAction()
    {
        $robots = Robots::find_extended(array(
                "robot_generation = 'steel'",
                "order" => "robot_name ASC",
                /* below you define $parameters for Manufactures::find($parameters) */
                "manufactures_find" => array(
                    "country = 'USA'",
                    "order" => "name ASC"
                )
            ));

        foreach($robots as $robot)
        {
            print_r($robot->manufactures->toArray()); //your manufactures should be sorted
        }
    }
}

Model method

class Robots extends \Phalcon\Mvc\Model
{
    public function find_extended($parameters)
    {
        $manufactures_find = $parameters['manufactures_find']; // get $parameters for Manufactures::find($parameters);

        /* i don't know if you have to unset below, i didn't test it so maybe find() use only what it knows nothing more */
        unset($parameters['manufactures_find']); //remove above parameters from parameters to Robots::find($parameters);

        $robots = self::find($parameters);
        foreach($robots as $robot)
        {
            $manufactures = Manufactures::find($manufactures_find); // use above parameters to search and sort as you wish

            $robot->manufactures = $manufactures;
            $new_collection[] = $robot;
        }

        return $new_collection;
    }
}


10.6k

@Karol, at first I thought this was going to work i don't think this is going to work. Now you are sorting all manufacturers by name for each robot. But the order of the robots do not change in this case, correct?

I have 1 robot that has 1 manufacturer, I want to sort all robots based on the name of the manufacturer.

I'm going to try one of the other solutions =)



10.6k
Accepted
answer
edited Mar '14

Ok guys, i've made a solution that works for me and is also pretty flexible based on the querybuilder.

In my controller:

    $aFilter = array(
            'conditions' => 'name LIKE :search: OR Models\Manufacturer.name LIKE :search:',
            'bind' => array(
                    'search' => '%search value%'   
                ),
            'order' => 'Models\Manufacturer.name DESC',
        );
    Robot::search($aFilter);

In my model i've made the following:

    public static function search($params=null)
    {
        $query = self::query();

        // check if we need to join a table     
        preg_match_all('/[\S]*\./', $params['conditions'] .' '. $params['order'], $aModelsToJoin);
        if(count($aModelsToJoin) > 0) {
            // remove duplicates
            $aModelsToJoin = array_filter(array_unique($aModelsToJoin));
            foreach ($aModelsToJoin as $model) {
                $query->leftJoin(rtrim($model[0],'.'));
            }
        }

        if(isset($params['conditions'])) {         
            $query->where($params['conditions'],$params['bind']);
        }

        if(isset($params['order'])) {         
            $query->order($params['order']);    
        }            

        return $query->execute();
    }

It searches for table aliases and joins them if needed, works like a charm in my case and I can add this to my base controller so it is available in every model.

Thanks for all your help, it helped me out a lot to figure out a solution!

@kaphert i showed you a way how you can extends models and do what you want. Thay you can do anything yourself, don't wait for framework to do everything for you. I am happy that you tried to find implementation and it works for you. We didn't know your envoirment, schema, target etc, we were trying to help you to push forward your knowledge about framework :)
Good work

Might I suggest that since you're sending an array to search(), that you add a new item join rather than regexing your query? Regex is powerful, but slow, and you're doing extra work in your method to accommodate some shortcomings. Using a join item, you wouldn't have to run preg_match_all() or remove duplicates.